Date interval

[JRPMD]

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.

 

[JRPMD]

The tables is Ingreso.

 

[plog]

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]

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]

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]

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]

Hi , I have not a code ; I'trying to make a query with an expression : between [admission date] and [discharge date].

 

[JHB]

Could you show what result you wanted with the data you posted in the database?

 

[Pat Hartman]

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]

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.

 

[JRPMD]

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]

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]

on your sample db that you posted, only Di Tomaso is admitted on 6-jun-2019.

 

[JRPMD]

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]

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]

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]

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.

 

[JRPMD]

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]

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]

Alright! I learned a lot ; and also about the interesting nz function!