One to Many - Count Last Record Only (1 Viewer)

[Valery]

I have two tables - one contains customer names, the other customer appointments. So one customer - many appointments.

Each appointment is booked at a set interval (every 3 weeks, 4 weeks, 5 weeks...) which can vary from one appointment to another.

I want to do a count, in a query, to show in a report.

I need to count:

Total Cus_ID by interval - so how many customers are booked every 2 weeks, every 3 weeks, etc.


I need the count to be based on the customer's LAST appointment only.

I have tried, select query (group), crosstab (!)... querying a query... Total line using Max... then Last...

Nothing I tried works. The sum of appointments by interval should equal the total number of clients in the databse... It gives me 4 times that... so it is counting every appointment, not just the last appoinment entered.

PS: I also will be including two other fields: activecustomer = yes and source = Eve - need to know criteria to set for those within the solution you will provide.

Thank you for your support - much appreciated.

 

[Mihail]

Hi Valery !

Let me understand how you manually do this for a certain customer.
First you find the last appointment.
Then you read from a table the type of appointment.
Based on this type you read the interval (1w, 2w, 3w ... etc) that this type of appointment is booked.

Now, for this customer, you start to count... what ?
How many appointments have exactly the same type or how many appointments has the same interval for booked ?

If this is the logic seems to be fairly simple to accomplish the task.
Anyway, upload the DB (I think that no one can help you without to see the exact structure of your DB).
If my logic is not OK then show me, in very short and by using very usual words, how should be.

Cheers

 

[Valery]

Hi Mihail, thank you for helping. Attached is a reduced sample - 36 clients (patients in fact) with their appointments.

Let me understand how you manually do this for a certain customer.
First you find the last appointment.
Then you read from a table the type not type but frequeny, that is the number of weeks between this appt and the next which can vary from appt to appt (patients begin seeing the therapist every 8 weeks and later maybe every 6 weeks or 0 weeks if they only want to call when they want an appt of appointment.
Based on this type you read the interval (1w, 2w, 3w ... etc) that this type Current Frequency at which appts are booked of appointment is booked.

Now, for this customer, you start to count... what ? I want to know - currently - how many active patients are at 0 or 1 or 2... frequency
How many appointments have exactly the same FREQUENCY or how many appointments have the same interval for booked ? YES

If this is the logic seems to be fairly simple to accomplish the task.
For you maybe! LOL!

Anyway, upload the DB (I think that no one can help you without to see the exact structure of your DB).
If my logic is not OK then show me, in very short and by using very usual words, how should be.

TY

 

[Mihail]

Save the DB in the previous version of Access (2007 or 2003).
I'm not able to open the DB.

 

[Valery]

It will not let me save as mdb "...because it uses features required by the current format". But this is not true as I have reduced it to two tables with about 6 fields each - plain text fields - quite ordinary. Have no relationships... took out everything and left you the minimum.

Suggestions?

 

[Mihail]

Create a new MDB then import (embed, not link) the tables.
Take a look to the relationships to ensure that are OK.

 

[Valery]

Works! Thanks

 

[Mihail]

Create a new MDB then import (embed, not link) the tables.
Take a look to the relationships to ensure that are OK.

If you forget to do this in .mdb, no problem. I establish the relationship between Pat_ID(s) fields (1 - many).
But if you haven't this relationship in your DB is a big problem.

 

[Mihail]

Run query Q_PatientsAtFrequency
Is this returning what you expected ?

 

[Valery]

I have 7 tables and yes, all relationships are established. Just gave you a quick sample and deleted everything except what I thought you needed for this report. Thanks for the advice though.

 

[Valery]

Yes, this is great - did not know how to do this (what you put in the first query). I added Where Deactivate_Ind = N and Source = Evelyne and we have a perfect 30 records!

THANK YOU!

One quick question (something else - quick - if you want me to post it, I will):

The following code, in a command button, opens a report in print preview, prints it PDF format and sends it by email - works great.

Would like to add the following "in it" - but don't know how or where!!

Just want to have the following yes/no check box field: ConsentFormSent checked after I open that report.

Here is the procedure in which I want to include this:

[SIZE=3][FONT=Calibri]Private Sub ConsentFormFre_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.OpenReport "R_Form_ConsentFre", acViewPreview, , "[Pat_ID]=" & Me![Pat_ID][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.SelectObject acReport, "R_Form_ConsentFre"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.OutputTo acOutputReport, "R_Form_ConsentFre", acFormatPDF, "C:\SOS\ConsentForms\Consent" & "_" & Me.Last & "_" & Me.First & ".pdf"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.SendObject acSendReport, "R_Form_ConsentFRE", acFormatPDF, "bergeronevelyne@hotmail.com", , , "Consent Form_" & Me.Last & "_" & Me.First, , False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]

Thank you again!

 

[Mihail]

As I've said more times over the forum, my first language is Romanian, the second is Romanian and the 3rd is also Romanian
After this I handle a little bit of French and English.

So, if you wish to make me able to help you, try to explain the problem from left to right and from top to bottom.

What is your new requirement ?
To have a check box in the report ?
Should be this check box always "checked" ?
Do you wish to keep tracks of reports created ?
Other thing ?

Sorry, but I don't understand.

Maybe an Update Query is enough, maybe not...

 

[Valery]

Very sorry.

So, if you wish to make me able to help you, try to explain the problem from left to right and from top to bottom.

What is your new requirement ? to have some coding that will put a check in a checkbox, added to an existing procedure located in a command button

To have a check box in the report ? No, sorry. The command button and the checkbox are both on a form. The checkbox is a field on the form.


Should be this check box always "checked" ? It should be always checked after I have opened the report (clicked on the command button) a first time


Do you wish to keep tracks of reports created ? No but thank you!

Other thing ? No

 

[Mihail]

Me.CheckBoxName = True

 

[Valery]

Where do I place it in the coding i gave you?

 

[Mihail]

Just after the code that open the report.