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?

 

[kmprice710]

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.

Maybe like a customer table that lists all customers with their IDs? Then I can link all of the tables to the index?

 

[Micron]

Your question surprised me and I had to look back at your posts. I guess I imagined you already had a customer table because it's so basic/generic. If the case is that there is a guarantee that every customer would be in at least one table (e.g. everyone bought a car) then you might get away with left joining the other tables to that particular table. Otherwise you could create tblCustomers by running append queries using the sales tables. However, without seeing those sales tables I cannot tell you for certain how to structure that. Generally speaking, you should look at how customers are identified in those tables and decide what information makes them unique from any other customer. Surely that is not last name alone; perhaps it is 2 or even 3 pieces of data. If that is the case, you'd create a unique index using those fields so that John Smith can only be added once, but Paul Smith would be allowed. Hopefully you get the drift of that but research unique indexes rather than ask me how to create one. Perhaps the unique identifier is a customer id that magically just happens to be the same in every table they have a purchase record in and it belongs to no one but them. That would make life simpler.

So the goal of append query for each sales table would be to put the customer details as a record for every customer, but just once. If you simply open these queries you'll get a warning that n records could not be appended because of index violations. You can ignore that because the unique index will prevent duplicates of customers if you do it correctly. Then you should be able to left join whatever is common between your sales tables and the customer table - obviously there has to be some commonality. Again, since I can't see them I have no idea what that might be.

You should fix the design very soon if not now, in which case you'd disregard all of the above. Research db normalization first.
EDIT - I should mention that you must play around with a copy of your db. Don't try such things on your production version.

 

[kmprice710]

Your question surprised me and I had to look back at your posts. I guess I imagined you already had a customer table because it's so basic/generic. If the case is that there is a guarantee that every customer would be in at least one table (e.g. everyone bought a car) then you might get away with left joining the other tables to that particular table. Otherwise you could create tblCustomers by running append queries using the sales tables. However, without seeing those sales tables I cannot tell you for certain how to structure that. Generally speaking, you should look at how customers are identified in those tables and decide what information makes them unique from any other customer. Surely that is not last name alone; perhaps it is 2 or even 3 pieces of data. If that is the case, you'd create a unique index using those fields so that John Smith can only be added once, but Paul Smith would be allowed. Hopefully you get the drift of that but research unique indexes rather than ask me how to create one. Perhaps the unique identifier is a customer id that magically just happens to be the same in every table they have a purchase record in and it belongs to no one but them. That would make life simpler.

So the goal of append query for each sales table would be to put the customer details as a record for every customer, but just once. If you simply open these queries you'll get a warning that n records could not be appended because of index violations. You can ignore that because the unique index will prevent duplicates of customers if you do it correctly. Then you should be able to left join whatever is common between your sales tables and the customer table - obviously there has to be some commonality. Again, since I can't see them I have no idea what that might be.

You should fix the design very soon if not now, in which case you'd disregard all of the above. Research db normalization first.
EDIT - I should mention that you must play around with a copy of your db. Don't try such things on your production version.

I'll give that a try. Each customer does have a unique customer ID. But, some customers only bought cars or only bought trailers and boats. Customers can have at most one row PER year per sheet, though if you bought cars in 2013, 2014 and 2015, you would have three rows in cars.