Query of data from three tables gives incorrect summary data

[Recordsetclown]

I'm new to Access and with limited VB and no SQL experience trying to convert some Paradox databases.

I have a master table(Requests)linked to each of two others (Responses and Payments), one to many, by a common indexed field called Request_Number. I'm looking for a query to summarize information contained in all of these tables upon which I can base a report.

The master table contains fields DateReceived and Category. I want to select records by [DateReceived] - probably quarterly - and count them by [Category]. For these same selected records and within these category groupings, I want to total the PaymentRequested field from the Responses table, and also the PaymentReceived and CopiesProvided fields from the Payments table.

Everything I've tried from the query design window seem to get only records with a corresponding Request_Number in each table (some requests may be in process or were denied). There are also doubled totals wherever more than one Request_Number is present (e.g., Payments contains one line with a value in [PaymentReceived] and another line with a value in [CopiesProvided].

Can anyone point me to a way to accomplish this? Thanks in advance for any assistance you can provide.

Jeff

 

[chaufschild]

I think the best thing to do in your case would be to set up queries for each type of totalling that you need to do all with the same key field (reqest number). After this, make a base query that uses the totaling queries as inputs along with the tables...the totals will appear more than once but when used outside the detail section of a report they should work fine.

This is fairly hard to explain and if you would like more information or have any questions feel free to ask.

 

[Recordsetclown]

Thanks a million. I tried this on a sample database at home and it looks like it gets just what I wanted.

Jeff