Trouble Joining Queries

[Perissos]

I have a table that contains

SCode
FYear
FMonth
Qty
Cost

I need to create a report which will calculate the Year to date information and the Prior year Year to date information and show it along with the current month and the prior year for the current month.

I created individual queries to gather the information and each appears to run correctly on its own, but when I join the queries together using an inner join, instead of getting the 3500 records I was expecting, I got over 2 million.

Do you have any suggestions on how to correct this, or an easier way of doing it?

I am sure this is probably something simple that I am trying to make complicated.

 

[boblarson]

Did you remember to join on both

SCode AND on FMonth?

 

[vbaInet]

Welcome to AWF!

If you sort based on SCode do you get lots of duplicate records?

 

[Perissos]

Thank you for the welcome.

I originally had the queries joined by the scode, and the month, but I got two different types of errors. One was an overflow and the other was something like you can't do this because it uses more than the allowed 2gig of temporary memory.

And yes I get tons of duplicate records.. oh wait. grrr.. yes I get tons and tons of duplicate records.

 

[vbaInet]

There must be a join from the other queries that's causing the overflow.

To get rid of the duplicates, you need the DISTINCT predicate and you apply this in the SQL view of the query. Have a look at this:

http://www.techonthenet.com/sql/distinct.php

Once you've applied Distinct, try joining based on both fields again.

 

[Perissos]

Ok. I looked that over, it helped.

I got the record count down to 27400 now. Lots better than millions, lol. I am going to go back and look at the other queries and see if I need to do the same thing. I have never combined so many queries to make 1 single report.

You guys are great.. thank you. Hopefully I will be able to help you also. I am pretty good with Access, but I start making mistakes when I look at stuff too long.

 

[vbaInet]

There may just be some joins you've created in the other queries which is pulling in more than necessary. Maybe an outter join. Look into what's causing the extra records in the other queries first before resorting to distinct.

Glad we could help.

 

[Perissos]

Good news, lol. The structure of the tables is changing and that includes data changes also.

 

[vbaInet]

Yeepee!! Let us know how you get on