Help with query

[reknox]

Taking advice from another post, have created 4 different queries, named Qtr1, Qtr2, Qtr3 and Qtr4. Need help with getting correct results. Want Qtr1 to be the sum of ConAmt where dates > =1/1/2009 and <=3/31/2009. If I can get help with Qtr1, I can do the others. Fields included in all queries are as follows except the date which is changed to represent the specific qtr. Although I'm not too familiar with SQL , have tried to use information found online. Thanks.

Field: MemID
Table: T_Contributions
Total: Group By
Sort: Ascending
Show: Yes
Criteria:


Field: Qtr1: Sum([T_Contribution]![ConAmt])
Table:
Total: Expression
Sort:
Show: Yes
Criteria:

Field: ConDte
Table:T-Contribution
Total: Where
Sort: No
Show: No
Criteria: Between #1/1/2009# And #3/31/2009#

Field: ConDte
Table:T-Contribution
Total: Group By
Sort: No
Show: Yes
Criteria:

I added the second ConDte field after an error message from Access which said to do it this way in order to see the date field.

Unique values set to yes

 

[wilpeter]

So for each MemID, you want a Sum of the ConAmt for each different ConDte--are you not getting this with a simple Design View query?

 

[reknox]

No. It doesn't group by name; each name is duplicated or even appears several times. (I forgot earlier to say that the name is linked to MemID in the query). Some names and dates appear in the query that shouldn't because it's not in the where clause for the particular query. I wonder if I've messed with it so much, Access doesn't know what to do.

 

[vbaInet]

Are T-Contribution and T_Contributions two different tables? Can you paste the sql here as well?

 

[wilpeter]

Does your query look like this at all?

 

[reknox]

Hold up; you both got me thinking and I believe I have resolved it. I'll finish testing tomorrow as it's late where I am. Thanks for your help.

 

[reknox]

Back again. Got the queries working with the correct data under the appropriate column; however, there are duplicate lines according to the number of records per name (field). Does this have to do with the join. This is using a left join. If not, what. Thanks.

 

[wilpeter]

Did you decide to Group By both MemID AND Name? Normally, only the name or only the ID is the Grouping. If the names are repeated but the same, using the Name to group by should combine records for that name. Can you display your Query as a snapshot or type the SQL?
When you say Left Join, it would be helpful to know which table is the Left (first) table here. A simple join is best if the table being used as a lookup table is joined to a transaction (multiple record) table. In my snapshot earlier I used the example of a Member table that has just one record for the MemName. The only problem I can envision is that you have different codes or names for the same customer.

 

[reknox]

SELECT T_Contribution.MemID, T_Members.Address1, T_Members.Address2, T_Members.MemCity, T_Members.MemSt, T_Contribution.ConDte, T_Members.MemZip, [Qtr1 Query].Qtr1, [Qtr2 Query].Qtr2, [Qtr3 Query].Qtr3, [Qtr4 Query].Qtr4
FROM ((((T_Members INNER JOIN T_Contribution ON T_Members.AutoMemID = T_Contribution.MemID) LEFT JOIN [Qtr1 Query] ON T_Contribution.MemID = [Qtr1 Query].MemID) LEFT JOIN [Qtr2 Query] ON T_Contribution.MemID = [Qtr2 Query].MemID) LEFT JOIN [Qtr3 Query] ON T_Contribution.MemID = [Qtr3 Query].MemID) LEFT JOIN [Qtr4 Query] ON T_Contribution.MemID = [Qtr4 Query].MemID
ORDER BY T_Contribution.MemID, T_Members.MemCity, [Qtr1 Query].Qtr1, [Qtr2 Query].Qtr2, [Qtr3 Query].Qtr3, [Qtr4 Query].Qtr4;

 

[wilpeter]

That is one huge step from the attempt to build the Qtr1 Query about which the thread was started. I’m assuming, hopefully, that you got the Qtr1 Query working and have now tried using the results of all four quarters in a combined report. Once you combine Inner and Left Joins, you have travelled beyond my pay grade and I must ask that some fresh eyes intercede here.

 

[vbaInet]

Could you zip, upload and post your db and I'll amend your query to remove duplicates. Also just let me know which query I should look for and what duplicated records you want to get rid of.

 

[Brianwarnock]

If you are still grouping by a date field in your queries you will end up with lots of records , infact I'm surprised there is anything to sum.

Brian

 

[reknox]

I hope I sent the correct file as I have several copies with which I have been experimenting. The query in question is AllQueriesJoined. You can see the problem immediately when opening. To re-state, there are two tables and the goal is to end up with a report which lists contributions made each quarter by name with address, etc. Feel free to change anything. Just let me know what so I can try to understand. Thanks again.

 

[vbaInet]

I have attached it. If you look carefully (prior to the attached) you will find that the records aren't duplicates, because of the DATE field (i.e.ConDte). Each record is unique because, although the Names are repeated, the Date is unique per member, per record. Your criteria was to find records BETWEEN dates and that's exactly what it did. Open the attached and have a look at the design view of the query and you will find that ConDte is unticked. This simply means you can still query your database using this field but the value wouldn't be shown on the query result (i.e. datasheet view). If the date field isn't shown, then you definitely are looking at duplicate values. To get rid of that you then put a keyword called DISTINCT just right after the SELECT keyword. This is needed because you're pulling information from several tables. Look at the sql of your query.

I will leave you to play around with summing your query, but if you get stuck just post back.

 

[reknox]

Thanks a lot. I know about summing so hopefully won't run into many more hurdles. This has truly been a great learning experience.

 

[vbaInet]

Excellent! Good luck with the rest of your db.

 

[Brianwarnock]

I see that you did not bother with my post before posting your DB

I believe that your duplicate records are to do with you having a lookup in your table design, I think the experts on here view that as a no no.You do not need the table T_Distribution in your final query, nor do you need to have date criteria again since the records have already been selected on date criteria, and you now don't need the Distinct keyword.

Brian

SELECT T_Members.MemFirstName, T_Members.MemLastName, T_Members.Address1, T_Members.Address2, T_Members.MemCity, T_Members.MemSt, T_Members.MemZip, [Qtr1 Query].Qtr1, [Qtr2 Query].Qtr2, [Qtr3 Query].Qtr3, [Qtr4 Query].Qtr4
FROM (((T_Members LEFT JOIN [Qtr4 Query] ON T_Members.AutoMemID = [Qtr4 Query].MemID) LEFT JOIN [Qtr3 Query] ON T_Members.AutoMemID = [Qtr3 Query].MemID) LEFT JOIN [Qtr2 Query] ON T_Members.AutoMemID = [Qtr2 Query].MemID) LEFT JOIN [Qtr1 Query] ON T_Members.AutoMemID = [Qtr1 Query].MemID
ORDER BY T_Members.AutoMemID, [Qtr1 Query].Qtr1, [Qtr2 Query].Qtr2, [Qtr3 Query].Qtr3, [Qtr4 Query].Qtr4;

 

[vbaInet]

I see that you did not bother with my post before posting your DB

That was naughty of the OP.

 

[Brianwarnock]

He would have still got duplicates without the date in but it would have narrowed down your search, and when there are too many errors going on it is easier to miss things.

Brian

 

[vbaInet]

Ah yes, that's right Brian. Less work for the engine and faster computation.

I hope the OP sees this.