Pull all three tables into a query. Make sure all three tables are linked by CustID. Pull CustID from Table 1, Order from Table 2, and Payment from Table 3. Make your query a totals query (sigma button), group on CustID and Count Order and Payment. Use the appropriate date fields for your criteria and use Where in the totals row.
I get the right results when i join two table but as soon as i add 3rd table in query i do not get right result...I'm posting my query
SELECT tblMothers.MotherID, Count(tblScreening.ScreeningDate) AS CountOfScreeningDate, Count(tblInfants.IPTID) AS CountOfIPTID
FROM (tblMothers INNER JOIN tblScreening ON tblMothers.MotherID = tblScreening.MotherID) INNER JOIN tblInfants ON tblMothers.MotherID = tblInfants.MotherID
GROUP BY tblMothers.MotherID;
I have come across this in some of the queries I have built. What you may need to do is create a nested query.
Create query 1. pull in tables 1 and 2 join on Customer ID and group and sum the values.
You will now have a table with the same number of rows as the first (assuming all of the customers had orders)
then create query 2, pull in the query you just created, then add table three
group and sum and viola numbers should be good to go.
Why this happens is that if there different number of values in the secondary tables. For instance
Customer 1 has two orders and for each of those orders they had two payments. So this would result in four rows in your queury
custID orderID PaymentID
1 O1 P1
1 O1 P2
1 O1 P1
1 O2 P2
When the computer groups on customer id and counts orders and payments your order count is doubled.
Well for my other reports i have created the form where user will enter StartDate and EndDate and then the report will preview data for that time range. For this one report i would like to see the time frame for example:
TimeFrame | Order
28-Apr-04 | 100
29-Apr-04 | 200
Again, "TimeFrame" is not anyfield from table. Is it possible to do this ?