Can't run the query I need, anybody help?

[kmprice710]

I have 5 tables of sales data. Let's call them car sales, boat sales, bike sales, trailer sales, jetski sales.
I have some common ID fields to all of them: year, customerid, companyid (all of my customers are corporate).
The problem is that a customer may not have bought a car and a boat or a bike and a trailer. Some people have multiple car purchases and nothing else.
But I'd like to see a sheet that has all customers and everything they have bought across categories..
I put together the query and joined them by customerid but the query did not pop up everybody. Then I tried year and Access is telling me that I don't have enough memory. (The car sales sheet has 135,000 records alone. Maybe that's the problem?)

How do I put together this query or do I need to try a different database program?

 

[JonXL]

The design is all wrong. But that aside, it's impossible to say what might be wrong with your query without actually seeing it.

Can you post the SQL of the query? (Open your query in Design view then from the Design ribbon select View>SQL.)

 

[kmprice710]

The design is all wrong. But that aside, it's impossible to say what might be wrong with your query without actually seeing it.

Can you post the SQL of the query? (Open your query in Design view then from the Design ribbon select View>SQL.)

The tables were separate Excel spreadsheets and we wanted each customer to have 1 line for each year with all purchases for that year together in one row. We thought the best way to do that was to put them in Access and run a query that would link the tables together.

SELECT cars.companyid, motorcycles.companyid, boats.companyid, trailers.companyid, jetski.companyid, jetski.incpost, jetski.year1, jetski.company, jetski.customerid, jetski.customerreference, jetski.Posd, jetski.segd, jetski.dint, jetski.dnetprofit, jetski.itdwe, jetski.tsdwe, jetski.tadwe, jetski.ttdwe, jetski.tfldwe, jetski.skdwe, jetski.pddwe, jetski.frecwe, jetski.ffwe, trailers.incpost, trailers.year1, trailers.company, trailers.customerid, trailers.customerreference, trailers.posipu, trailers.segpu, trailers.pupatm, trailers.pupata, trailers.fpugm, trailers.fpuga, trailers.pupt, trailers.ptpuyds, boats.incpost, boats.year1, boats.company, boats.customerreference, boats.customerid, boats.posk, boats.segk, boats.qtyk, boats.knetprofit, boats.krtdte, boats.prwe, boats.prsatyds, boats.prtdopps, motorcycles.incpost, motorcycles.year1, motorcycles.company, motorcycles.customerid, motorcycles.customerreference, motorcycles.posp, motorcycles.segp, motorcycles.pqty, motorcycles.pcmp, motorcycles.pnetprofit, motorcycles.ptdte, motorcycles.pntwe, cars.incpost, cars.year1, cars.company, cars.customerid, cars.customerreference, cars.posr, cars.segr, cars.qtyr, cars.rnetprofit, cars.rtdte, cars.recwe, cars.recsatyds, cars.rectdopps
FROM (((motorcycles INNER JOIN cars ON motorcycles.[year1] = cars.[year1]) INNER JOIN boats ON motorcycles.[year1] = boats.[year1]) INNER JOIN trailers ON boats.year1 = trailers.[year1]) INNER JOIN jetski ON trailers.[year1] = jetski.year1;

 

[JonXL]

Do all the tables have the same fields?

 

[kmprice710]

No, but they have some common fields like year1, customer id, company id, company name, customer name. I tried to join them on each one of those but the query only returned rows that had a customer on each table. Some customers only buy cars or only buy boats or buy boats and cars but not trailers.

 

[kmprice710]

Do all the tables have the same fields?

Here are the tables in CSV

Filebin | 9hi71vlbnhdif2vm

Convenient file sharing. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net

 

[Fluff]

Also posted as an Excel problem. Tried to do this in Access and it didn't work out. Maybe Excel can?

 

[kmprice710]

The design is all wrong. But that aside, it's impossible to say what might be wrong with your query without actually seeing it.

Can you post the SQL of the query? (Open your query in Design view then from the Design ribbon select View>SQL.)

Somebody on the Excel board solved it by using Powerr Query. Does that also work for Access?

 

[kmprice710]

Do all the tables have the same fields?

This problem was solved on the Excel board with dummy data that I have provided here, but I have not been able to duplicate the solution on the real data. I have 100,000 rows to transform and I'm really late in delivering this to my boss. If anybody can please walk me through how to do this, I would be appreciate it so much!

 

[Micron]

If a customer bought a car but no trailer, there is no "equality" when you specify equal joins on everything. I'd say that you'd need to LEFT join trailers on customers, left join cars on customers and so on. That would require that the customer id is in every sales table. If it is not, then I don't know. As noted, it seems like your tables structure is and will continue to be an obstacle. Perhaps you'll have to group data with a UNION query first so that you have something "normal" to work with.