I have 4 tables which I am combining in this query which will be used to make a form. The tables are registrant, event, participant and day_care_participants. Both the registrant and event tables have primary keys of "id" and the other two tables have dual primary keys - one from event and one from participant. The events are recreational activities and each registrant then becomes a participant when they enroll in a particular event. So that I can eventually create a summary form for each registrant and the total cost of all events that they signed up for, I have created the following query:
SELECT DISTINCT REGISTRANT.ID, EVENT.Description, EVENT.[Start Date], EVENT.[End Date], EVENT.Total, EVENT.[Event Type]
FROM REGISTRANT, EVENT, [DAY CARE Participants], Participants
WHERE (((REGISTRANT.ID)=PARTICIPANTS.Registrant) And ((EVENT.ID)=PARTICIPANTS.EVENT)) Or (((REGISTRANT.ID)=[DAY CARE Participants].Registrant) And ((EVENT.ID)=[DAY CARE Participants].EVENT));
The [day care participants] table has fields where the user may select the days of the week where their child will be attending - and they may not attend every day. There is a separate "total" field in that table which calculates the daily cost based on the number of days multiplied by EVENT.Total. This is where the EVENT.[Event Type] field is used. Day care is event type "2".
Here is my problem. For each event type "2" - I need to capture the "total" which comes from [day care participants].Total. If I just insert this field on the "select" line - I return too many records and the values are all messed up. How can my query return the correct information? EVENT.total when EVENT.Type <>2, and [day care participants].Total when EVENT.Type =2?
It's hard to visualize completely without a download, but I think the following will work, if I understand you correctly.
Ceate another query with two fields forn the EVENT table: EVENT.Type and EVENT.Total. The Type field should be limited by "HAVING EVENT.Type = '2'", or by simply grouping on the Type field. The Total field should be a SUM, so it totals all that Type in one line. Save the query, perhaps qryEventSummary. In your main query (above), add the new query into the design grid, and JOIN to the EVENT table on the Type fields. Bring the query's field SumOfTotal into your QBE grid, and you should have the correct numbers.
Of course, you are not limited to only Event = 2 and a HAVING clause, you can make it a universal query with a GROUP ON clause insted, and in your main query you can have a WHERE or HAVING clause to limit Type to '2'.
First try the previous solution. If it doesn't work, it's always easier with a full compact, zip, and upload. I don't need to see live data, though. False data is fine, as long as the same data is in all the tables.