Date interval (1 Viewer)

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
Hello, I have table in a database with two date fields , admission and discharge date to a clinic . I can count the days between this two dates in a query .
Now I want to know how many people is present at a single day (x day).
I think it is possible making an expresion like :
x day : >=[Fecha de ingreso] and <= [Fecha de egreso].
It can retrieve the records that have that day in between the interval.
Can I make it a query or a code to solve this?
Thanks in advance for your help.
 

Attachments

  • UCI5 copia1.accdb
    1.7 MB · Views: 56

plog

Banishment Pending
Local time
Today, 14:49
Joined
May 11, 2011
Messages
11,611
Can I make it a query or a code to solve this?

Yes.

Now which one do you prefer and why not try it?
 

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
I prefer a code.
I don't know wich is the appropriate syntax ; I'm not to skilled in programming codes , so don't tryied it and ask for some help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:49
Joined
Oct 29, 2018
Messages
21,358
Hi. I could be wrong but it seems like you should be able to use the DCount() function for this. Have you tried it?
 

plog

Banishment Pending
Local time
Today, 14:49
Joined
May 11, 2011
Messages
11,611
Try and then post back here your code when/if it doesn't work and explain the manner in which it doesn't work (error code, unexpected results, etc.)
 

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
Hi , I have not a code ; I'trying to make a query with an expression : between [admission date] and [discharge date].
 

JHB

Have been here a while
Local time
Today, 20:49
Joined
Jun 17, 2012
Messages
7,732
Could you show what result you wanted with the data you posted in the database?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:49
Joined
Feb 19, 2002
Messages
42,973
To produce a day by day count requires an additional table. Queries cannot generate records that don't exist so this day by day count is a little more difficult. At a high level the techinque is to create a table of dates in the range that you need. Then you create a query that joins to that table using a non-equi-join. So start with an equi-join (equal) and then switch to SQL view and change the join to

From tblA inner join tblB on tblA.AdmitDate >= tblB.EachDate AND tblA.DischargeDate <= tblB.EachDate

Once you change the join from = to <= and >=, you will not be able to switch back to QBE view since the QBE can only represent equi-joins

If the person is in the facility for 5 days, you will end up with five records, one for each day.

Then take this query and sum it by day to count the occupants.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:49
Joined
May 7, 2009
Messages
19,169
i imported your table and create new query (query1).
there is also a Module (module1).

it can be done through just a simple query, but I prefer to use VBA.
 

Attachments

  • Database4.zip
    28.5 KB · Views: 54

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
Could you show what result you wanted with the data you posted in the database?

Yes , the people that were present at a single day , i.e 6/6/19 are :
Id 1,3 ,5 and 6.
 

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
To produce a day by day count requires an additional table. Queries cannot generate records that don't exist so this day by day count is a little more difficult. At a high level the techinque is to create a table of dates in the range that you need. Then you create a query that joins to that table using a non-equi-join. So start with an equi-join (equal) and then switch to SQL view and change the join to

From tblA inner join tblB on tblA.AdmitDate >= tblB.EachDate AND tblA.DischargeDate <= tblB.EachDate

Once you change the join from = to <= and >=, you will not be able to switch back to QBE view since the QBE can only represent equi-joins

If the person is in the facility for 5 days, you will end up with five records, one for each day.

Then take this query and sum it by day to count the occupants.

Thank you very much, I will try.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:49
Joined
May 7, 2009
Messages
19,169
on your sample db that you posted, only Di Tomaso is admitted on 6-jun-2019.
 

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
on your sample db that you posted, only Di Tomaso is admitted on 6-jun-2019.

Thank you Arnelgp for your excellent query and module , I think is useful to know how many admission there are in time period.
But also I want to know how many people were present not in period but in one selected day ( in order to calculate resources spent in that day, personnel required etc).
 

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
Hi. I could be wrong but it seems like you should be able to use the DCount() function for this. Have you tried it?

Yes , I can use DCount , but before count I need to retrieve the records in wich the admission-discharge interval comprises the selected day.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:49
Joined
May 7, 2009
Messages
19,169
you can get 1 day by inputting same date on [admission date] and [discharge date].
you should try it out and compare to the data in your table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:49
Joined
Feb 19, 2002
Messages
42,973
I made a database to show you how this works. There are two queries.

qGetDays uses a cross join to generate a record per day per person
qCountPerDay counts the number of persons per day for the date range on the form.

The form takes a date range and when you press the run button, it populates the subform with the results. There is a comment on the form that tells you the range of the data in tblAdmissions.

To make this work going forward, you will need to add day records to tblDays going into the future. I put in records for 2019. If you are always looking back, then you can prompt yourself in November to add the records for the next year. I generated the dates using excel since it is easy to put in three rows and just drag to extend. You can then copy and paste into Access or append if you prefer.
 

Attachments

  • CountUnitsPerDay.accdb
    1 MB · Views: 58
Last edited:

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
Thank you very much for your excellent database and querys .
That's exactly what I need.
I think this matter is solved.
Thanks again!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:49
Joined
Feb 19, 2002
Messages
42,973
You are very welcome. The technique is very useful for other situations also. I use it with a table of numbers rather than dates to print out the correct number of labels for an order. For example, if the order contains three boxes, I need to print three labels. So the query joins to the numbers table using a cross join (no join lines) with a where clause of
Where tblNumbers.NumVal <= tblOrderHeader.PackingBoxCount
 

JRPMD

Registered User.
Local time
Today, 12:49
Joined
Nov 24, 2012
Messages
52
Alright! I learned a lot ; and also about the interesting nz function!
 

Users who are viewing this thread

Top Bottom