Left join query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Left join query

Brent Davis

2 years ago

All, I am trying to compare year over year revenue per customer. I have a customerlistT and a ShippertotalsT. I have created a query for just the companyname and accountnumber, called customerlistQ. Then I made another query with the customerlistQ and the shippertotalsT with the accountnumber linking the two together. I made this a left join query. I want all the customers listed whether they have data or not. I then made this an aggregate query to group by accountnumber and sum by revenue. It works great until I apply the criteria(I want totals by year. When I do the totals by year the join does not show all customers only the ones with data. I want to show all customers even if they have zero revenue. Why is this happening? Any assistance is appreciated.

	Kevin Yip @Reply 2 years ago
When there is no revenue, there is no year. When there is no year, you can't group by year. And that's why nothing shows. For them to show, somewhere in your process you have to "artificially" enter a year, or years, in which a customer didn't have revenue, which would be hard to do.

	Scott Axton @Reply 2 years ago
The easiest method, probably, might be to create a temporary table with the data you need before summing it up. Go watch the Missing Months (and the per-requisite videos) to see how to get the zero value if there is no data.

	Brent Davis OP @Reply 2 years ago
Guys thanks for your input. I was able to figure out a solution due to your responses. I tried to make it much more difficult than it had to be. I had to make a few queries but it works perfectly. It was a great learning experience. Now on to the next opportunity! Thanks again.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View