Solved Alert Counter on Form (1 Viewer)

[Jomat]

Hi. I hope someone can help.
I have a main form that has a list of occupancies. In and Out. I'm trying to get a counter alert on the main form to display Maximun if the manually set maximum is reached based on the total of entries minus outs in one day. I'm not sure where to do this, on the form or in a query.

I've attached a sample db.
Thank you ahead of time.

 

[arnelgp]

see your form in design view.

 

[Jomat]

I've made a mistake. The count is continuous.
Example, 100 IN at 9am then another 100 IN at 10am, then another 100 IN at 11am.. then 100 OUT at 12pm .... such as the sum would be 200 minus the 100 amount of outs.

 

[arnelgp]

see the form again.

 

[arnelgp]

... and another

 

[Jomat]

It works great.
Now that I look at it, one change. LOW MEDIUM AND HIGH.
I've added some calculation fields to the form. I needed to see the actual numbers.
Instead of just Maximum and Good, how can I add the MEDIUM where the Count is Less then 5 before the Max Amount?

 

[Jomat]

... and another

I did something similar to your sample. Your's looks much more organized.

 

[Jomat]

I don't know the syntax but I understand the logic. I'm trying to use the below.

=IIf(DLookUp("GMAXSET","GUEST_MAX_SET")<=Nz(DSum("GCOUNT","GUEST_T","GDATE=DATE() AND INOUT='IN'"),0)-Nz(DSum("GCOUNT","GUEST_T","GDATE=DATE() AND INOUT='OUT'"),0)-Dat()-5,"MID",IIf(DLookUp("GMAXSET","GUEST_MAX_SET")<=Nz(DSum("GCOUNT","GUEST_T","GDATE=DATE() AND INOUT='IN'"),0)-Nz(DSum("GCOUNT","GUEST_T","GDATE=DATE() AND INOUT='OUT'"),0),"MAX","LOW"))

 

[arnelgp]

i made a query QRY_TOTAL_TODAY.
see your form now.

 

[Jomat]

i made a query QRY_TOTAL_TODAY.
see your form now.

I uploaded another one. If you look at the totals in red. Those are the totals. Each time a count is done, new customers are entering. The entries is in addition to how ever many customers that are existing in the restaurant. so if the entry is 10 at 8am and 10 at 9am then there should be 20 total.
the do.cmd to open the Guest set max didn't work for me so I placed a macro in it's place. I understand what you did for the evaluation part but I don't know how to complete the syntax and formula to tally the combined totals where it's only for IN or OUT.

 

[ebs17]

I think one could solve this with a single query as the RecordSource for the form:

SELECT
   G.GDate,
   G.GTime,
   G.INOUT,
   G.GCount,
   DSum("GCount * IIF(INOUT = 'OUT', -1, 1)", "GUEST_T", "GDate = Date() AND GTime <= " & Format(G.GTime, "\#hh:nn:ss\#")) AS CurrentTotal,
   DLookup("GMAXSET", "GUEST_MAX_SET") - CurrentTotal AS FreeSpace
FROM
   GUEST_T AS G
WHERE
   G.GDate = Date()
ORDER BY
   G.GTime

If necessary, you could derive your alerts from concrete values from FreeSpace or work with colors using conditional formatting in the form.

 

[Jomat]

That's a good idea. I will try that.

 

[Jomat]

Thank you all.
I've decided to use unbound fields for the calculation and the conditioning formating. Works now.
Thank you all again.