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?

 

[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.

 

[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!

 

[JRPMD]

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

 

[arnelgp]

also made a db, without the need of another table that you enter those extra long dates.

 

[arnelgp]

to take care of bloat issue, I added another Boolean field to table tblDummy. Records will no longer be deleted, but instead set this field to True. added criteria to the query to include only records where this flag field set.

 

[arnelgp]

another revision, the query now will not count if the person is discharged on same date as [end date] parameter.