List all clients in a specific date from 3 date fields

[batman1056]

Ok let me explain
I have a list of 50 people who may have accessed events A,B,C at different times but what I want to do is to put a date in my form, run the quaery which will list all people who have Attended A,B or C

When I do my normal query, I put the fields in, but if I put a date filter in Event A, it will not show up any person who may have attended event B but not Event A.

I thought about running an append query but this would mean running the data several times with possible duplication of client names.

Ideally I would like to have my report to show
Date: 31/07/2006
Name A B C
K.Brown Y Y
J Blogg Y Y
K Smith Y
K Ellum Y Y

then I can just use a count at the bottom

The methods I have tired will filter event date A, but will not show J Blogg
If I create another append for Event B, and append that data to a table, then I will have duplicaion of K Brown.. Any Help Appreciated.

Last thing - when I run the append queriy and get my duplications, the fields where I had a Y/N option - if it was YES then I get a -1 instead of 1 ??? Why? and how do I make sure that when appending that the Y answer is a 1 not a -1

 

[batman1056]

*Note on the above** my append query (have have created 4) will list all those in each event, but will also duplicate those people who have attended more than 1 -
How do I now Merge these duplications?

 

[ajetrumpet]

can you please post the SQL you have written for what you are doing??

 

[batman1056]

Event A

INSERT INTO tbl_claims ( [First Name], Surname, District, [Application From], [App from Date], [Post Start App], [Post Start Date], [Startup Declaration], [Start-up Date], Disabled, [Disadvantaged Area], BME, Woman, [Priority Area], HMR, [Non-Prority Area], [Social Enterprise], [Client ID], [Claim Date] )
SELECT [tbl_client details].[First Name], [tbl_client details].Surname, [tbl_client details].District, [tbl_claim data].[Applicaiton Form], [tbl_claim data].[AF Date], [tbl_claim data].[Application Form (post)], [tbl_claim data].AFP, [tbl_claim data].[Start-up Declaration], [tbl_claim data].[SUD Date], [tbl_claim data].Disabled, [tbl_claim data].Disadvantaged, [tbl_claim data].BME, [tbl_claim data].Female, [tbl_claim data].[Priority Area], [tbl_claim data].HMR, [tbl_claim data].[Non-Priority Area], [tbl_claim data].[Social Enterprise], [tbl_client details].[Clinet No], [tbl_claim data].[AF Date]
FROM [tbl_claim data] INNER JOIN [tbl_client details] ON [tbl_claim data].[Client Ref] = [tbl_client details].[Clinet No]
WHERE ((([tbl_claim data].[AF Date])=[Forms]![frm_claim_date]![cdate]));

Event B

INSERT INTO tbl_claims ( [First Name], Surname, District, [Application From], [App from Date], [Post Start App], [Post Start Date], [Startup Declaration], [Start-up Date], Disabled, [Disadvantaged Area], BME, Woman, [Priority Area], HMR, [Non-Prority Area], [Social Enterprise], [Client ID], [Event Date], [Event Attended], [Claim Date] )
SELECT [tbl_client details].[First Name], [tbl_client details].Surname, [tbl_client details].District, [tbl_claim data].[Applicaiton Form], [tbl_claim data].[AF Date], [tbl_claim data].[Application Form (post)], [tbl_claim data].AFP, [tbl_claim data].[Start-up Declaration], [tbl_claim data].[SUD Date], [tbl_claim data].Disabled, [tbl_claim data].Disadvantaged, [tbl_claim data].BME, [tbl_claim data].Female, [tbl_claim data].[Priority Area], [tbl_claim data].HMR, [tbl_claim data].[Non-Priority Area], [tbl_claim data].[Social Enterprise], [tbl_client details].[Clinet No], [tbl_claim data].[AR Date], [tbl_claim data].[Attendance Register], [tbl_claim data].[AR Date]
FROM [tbl_claim data] INNER JOIN [tbl_client details] ON [tbl_claim data].[Client Ref] = [tbl_client details].[Clinet No]
WHERE ((([tbl_claim data].[AR Date])=[Forms]![frm_claim_date]![cdate]));

As you can see I am using the same core data, but the different filters are needed.

The main problems is that my clients may attened different events ( I am using the term events as its easir to understand) so Client
Joe Brown on 31/07/07 may go to event B
Fre Smith on 31/07/07 may go to event A and B
Dee Jones on 31/07/07 may go to event C
Fre Smith on 30/08/07 may then go to event D

So my report for 31/07/07 will show
a list of clients who have attened the events for that date, it will not show event D for Fre Smith and it will list all the events for each client

If I put a date filter on Event A, I will not get events B,C,D,E as as the clinet did not go to that event, but on 31/07 they did go to other events.

Hope this makes sence

 

[ajetrumpet]

Here is the key statement, and this is where the problem lies (I believe)...

If I put a date filter on Event A, I will not get events B,C,D,E as as the clinet did not go to that event, but on 31/07 they did go to other events.

One more question; I assume your table structure looks like this...??

TABLE
Client
Event A Attend Date
Event B Attend Date
Event C Attend Date
Event D Attend Date
(any other fields..)

 

[batman1056]

Hi the structure is as follows:-
TABLE: tbl_clinet details
which has the unique Client ID, name, address etc
TABLE: tbl_claim data
which has the fields show above - including
(A)Application Form and AF date
(B)Start-up declareation and SUD date
(C)Applicaiton Form (Post) and AFP date
this table has other data which is generic to the clinet and is linked to
Eligibility Form - once I get this form I tick the fields like HMR, Female, BME etc.

I have called these "events" as it was easir than trying to explain the above.
In theory what happens is a client accesses support - then will complete an eligibility form - I will get data like HRM,Femal,BME (which are all tick boxes)
At some stage I might get one of the above forms A,B or C
When I run my monthly activity I would like to see which clients have compelted form A,B or C
At some stage the clinet might complete all of these forms (or some of them) in different time periods.

So in July is client ID 33,34,45 might have done A; and 37 A and B and 43 A and C
in August Clinet ID 22,37,45 might have done B

So when I run my report for July I will see 33,34,45,37,43
and in Aug I will see 22,37,45

Now - what I did was create a macro that seached the above two tables - I had to create 3 seperate queries that appended to a new table - as you can guess when a client has compelted more than one docucment I get duplications of data!.

I am probably making this more complicated than what is needed - not sure if report filter would do what I want -- any help really appreciated.

 

[ajetrumpet]

I am probably making this more complicated than what is needed

Truthfully, I do think this is the case.

However, I am having a difficult time relating your talk to the problem that you are having. I cannot create a visual in my mind of your tables and queries. Can you post your file or an example DB that illustrates what you are doing??

Also, I am pretty sure that WITH THIS....

TABLE: tbl_clinet details
which has the unique Client ID, name, address etc
TABLE: tbl_claim data
which has the fields show above - including
(A)Application Form and AF date
(B)Start-up declareation and SUD date
(C)Applicaiton Form (Post) and AFP date

I can say that the text in red indicates redundant data. From the way I read the post, those field that are already in Tbl_Client do NOT need to be repeated in Tbl_Claim. Tbl_Claim only needs one identifying field (Client ID) from the Client field (which is necessary in order to join the two tables, as they are officially related). - They should be joined via a one-to-many.

 

[batman1056]

I have attached the database - with 4 clients
as you can see from the Queries - you can see what the 3/4 key areas I need to see in my final report.

Ideally

The monthly claim will show me which customers have has pre-start, start-up application from and event (see queiry)

 

[batman1056]

Also, I am pretty sure that WITH THIS....I can say that the text in red indicates redundant data. From the way I read the post, those field that are already in Tbl_Client do NOT need to be repeated in Tbl_Claim. Tbl_Claim only needs one identifying field (Client ID) from the Client field (which is necessary in order to join the two tables, as they are officially related). - They should be joined via a one-to-many.

Yes. that is correct - BUT if a client goes to event B and no other event then I need to capture this data
If they only wen to A then I need to capture it - so yes its redundent but No also as I need to be able to record it too

 

[ajetrumpet]

I'm looking at your file batman....I will get back to you on it pretty quick...

 

[ajetrumpet]

Batman,

The append queries in the file that you posted do not append any records, so I can't get a handle on what you're talking about about.

Any data left out??

 

[batman1056]

Ok.. I try this version
In the menu - choose claims
then in the date box choose
31/07/2007

this should show u want I mean
also 30/08/2007 and 31/09/2007 are 2 other claims

Thanks

 

[batman1056]

I have tried various methods - still having issues with it.
Another Question - I have created a Query that counts the number of times "Yes" appears in a list - the answer for example is -72
I then show this data in a menu as a top line achivement figure - this is not a problem, but when I reference the query in a table the value is -72, I can get it to be a +72 in a report but unsure how to make it +72 in a query or form.

 

[ajetrumpet]

When I run my monthly activity I would like to see which clients have compelted form A,B or C
At some stage the clinet might complete all of these forms (or some of them) in different time periods.

So in July is client ID 33,34,45 might have done A; and 37 A and B and 43 A and C
in August Clinet ID 22,37,45 might have done B

So when I run my report for July I will see 33,34,45,37,43
and in Aug I will see 22,37,45

That's exactly right...that's what you will see with criteria like this....

WHERE DateField Between [firstofmonth] AND [endofmonth]

Now - what I did was create a macro that seached the above two tables - I had to create 3 seperate queries that appended to a new table - as you can guess when a client has compelted more than one docucment I get duplications of data!.

I don't quite understand why you are using 3 different queries. I have looked at all 4 of them, and they are the same, except for the WHERE clause line.

As I said before, I am not going to question how you have this set up because I now know where your append queries are getting their information. I think the best thing to do is give you a couple of scenarios of which I believe might help you (because I'm not 100% on the relational structure of your DB, compared to what you might view it as). So, here is what I have...

** If your tbl_Claim Data - 1) holds individual records of clients AND
2) Has the different DATE fields (AR, SUD, etc...) in it to represent the different EVENTS that the clients have gone to (on that date),

Then correcting the problem you originally posted about should be fairly simple to do....

Query All Clients and the events they went to in July

SELECT [AllYourFields]

FROM table INNER JOIN on YourOtherTable

WHERE (event1field BETWEEN #7/1/2007# AND #7/31/2007) OR 
(event2field BETWEEN #7/1/2007# AND #7/31/2007) OR 
(event3field BETWEEN #7/1/2007# AND #7/31/2007) OR
(event4field BETWEEN #7/1/2007# AND #7/31/2007)

Stop the annoying "-1" numbers that represent "YES" in checkbox fields

Substitute...

SELECT [FieldWith"Yes/No"Field]

With...

SELECT IIF (["Yes/No"Field] = -1, "YES", "NO") AS ["Yes/No"FieldName]

 

[batman1056]

what is "FROM table INNER JOIN on YourOtherTable"

also the dates - I could use a reference to a form - i.e I could choose a date in from then have this query look for that date - is this correct i.e.

WHERE (event1field BETWEEN FORM![datecheck]![datefield] AND FORM![datecheck]![datefield]) OR

 

[ajetrumpet]

what is "FROM table INNER JOIN on YourOtherTable"

It is a short reference (my "shortcut" reference to the corresponding SQL that you had already created (which was correct, anyway))...

INSERT INTO tbl_claims ( [First Name], Surname, District, [Application From], 
  [App from Date], [Post Start App], [Post Start Date], [Startup Declaration], 
    [Start-up Date], Disabled, [Disadvantaged Area], BME, Woman, [Priority Area], 
      HMR, [Non-Prority Area], [Social Enterprise], [Client ID], [Claim Date] )

SELECT [tbl_client details].[First Name], [tbl_client details].Surname, 
  [tbl_client details].District, [tbl_claim data].[Applicaiton Form], 
    [tbl_claim data].[AF Date], [tbl_claim data].[Application Form (post)], 
      [tbl_claim data].AFP, [tbl_claim data].[Start-up Declaration],
        [tbl_claim data].[SUD Date], [tbl_claim data].Disabled, 
      [tbl_claim data].Disadvantaged, [tbl_claim data].BME, 
    [tbl_claim data].Female, [tbl_claim data].[Priority Area], [tbl_claim data].HMR,
  [tbl_claim data].[Non-Priority Area], [tbl_claim data].[Social Enterprise], 
[tbl_client details].[Clinet No], [tbl_claim data].[AF Date]

[COLOR="Red"]FROM [tbl_claim data] INNER JOIN [/color][tbl_client details][color=red] ON 
  [tbl_claim data].[Client Ref] = [tbl_client details].[Clinet No][/COLOR]

    WHERE ((([tbl_claim data].[AF Date])=[Forms]![frm_claim_date]![cdate]));

also the dates - I could use a reference to a form - i.e I could choose a date in from then have this query look for that date - is this correct i.e.

WHERE (event1field BETWEEN FORM![datecheck]![datefield] AND FORM![datecheck]![datefield]) OR

The correct syntax is...

BETWEEN [size=4]Form[/size][color=red]S[/color]!FormName!ControlName AND, etc...

So, if [datecheck] is the Form Name, and [datefield] is the Control Name, then you have it right.