I want to make a query on three tables, with dates and activities but one table is duplicating

[KNap]

Hi I have three tables that hold similar information, for example Client ID, Date.

One of the tables shows client information, another shows how many times a client comes into the establishment and uses certain services, the last one shows whether a client stayed successfully overnight.

I want to build a report which counts each service a client has had during the day using date parameters and how many times during that parameter they stayed overnight.



I have built a query, which counts the services by date parameter, but it duplicates the number of times the client has stayed overnight (e.g., client came in on 1/7/18 and 2/7/18 but only stayed overnight on 2/7/18 - query shows two of the latter records).

Can someone please tell me what I'm doing wrong?

Kelly

[Perceptus]

Are you joining the 3 tables? Do you have a database you can link or screenshots?

[KNap]

So - you can see there's the other date from the last table.... this is the one that duplicates

[KNap]

Sorry - duplicated the screen shots....

[Perceptus]

Hard to see, but im thinking you dont need the join for the data between activity log sheet and client.

[Perceptus]

Can you right click the designer and copy the SQL for here?

[KNap]

Sorry - I'm a novice - how do I do that for you?

[Perceptus]

on the top left of your screen where it says "view" and "run". Under that view is a arrow, click that and choose SQL

[KNap]

SELECT Client.[Client Reference], [Activity Log Sheet].[Enter Date], [Activity Log Sheet].[Advice and Advocacy], [Activity Log Sheet].[Meal Ticket B], [Activity Log Sheet].[Meal Ticket L], [Activity Log Sheet].[Mental Health], [Activity Log Sheet].[Phone Use], [Activity Log Sheet].Post, [Activity Log Sheet].Internet, [Activity Log Sheet].Shower, [Activity Log Sheet].[Washing Machine], [Daily Log Sheet].[Enter Date], [Daily Log Sheet].[Offered a Place for that night], [Daily Log Sheet].[Accepted Place for that Night], [Daily Log Sheet].[Arrived at 5/6pm], [Daily Log Sheet].[Granted Access that Evening], [Daily Log Sheet].[Asked to Leave Once In], [Daily Log Sheet].[Client Walked Out]
FROM ([Daily Log Sheet] INNER JOIN Client ON [Daily Log Sheet].[Client ID] = Client.[Client Reference]) INNER JOIN [Activity Log Sheet] ON [Daily Log Sheet].[Client ID] = [Activity Log Sheet].[Client ID]
WHERE (((Client.[Client Reference])=[Please enter Client Reference]) AND (([Activity Log Sheet].[Enter Date]) Between [Please enter first date] And [Please enter last date]));

[Perceptus]

When you press F4 while in the designer, it should bring up the properties dialog. A Kludge may be to set the unique records, value to true.


separate solution

I can't tell which join is referencing the data on the one to many from here. Looks like you need to click the line that connects the activity log and the client. and press delete.then run again.