X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Calculate Multiple Fields If Today Within Date Ranges, Access 2016    
post Sep 18 2019, 11:55 AM

Posts: 3
Joined: 18-September 19

Hope I am asking this question correctly:
I am new to ACCESS and am working on Access2016.

I have a database (EmployerInfo) with a subform (HOUSING) in a tab.

The HOUSING subform has the following fields for housing capacities:

Max_Capacity: This is the maximum number of occupants at any given time
Current_Total: I want this to be the total number of workers housed there TODAY or any given day that the record is being viewed.
StartDON: Date workers will arrive
EndDON: Date workers leave

The Current_Total should consider the StartDON and EndDON and the #of workers for the “current employer” as well as the StartDON and EndDON and the #of workers for the additional employers using the same housing.

I am not sure which direction to go. Calculation field? VBA Code? Query? Or how to do it.

Eventually I’d like to do some form of conditional formatting on the Max Capacity field so that it is flagged when the max is reached or exceeded.

Any help would greatly be appreciated.

Attached File  SampleDBEntryPic.JPG ( 193.59K )Number of downloads: 11

This post has been edited by rican8198: Sep 18 2019, 11:56 AM
Go to the top of the page
post Sep 18 2019, 12:02 PM

UA Admin
Posts: 35,882
Joined: 20-June 02
From: Newcastle, WA

Welcome to UA.

Given the information here, I would say that a Totals query should give you the values you need. However, it's hard to tell from just screenshots how things are set up. The most IMPORTANT consideration is always tables. Access is a tool for creating Relational Database Applications. That means tables are the heart of it.

Based on the information shown, I would guess there is a table in which you record each worker in the housing he or she is assigned to on each day. If that's not the way you did it, we'll need to see that alternative.

Again, I'm pretty sure we'll end up with a Totals query, but getting there involves more details.

Best of all would be a sample copy of the accdb, with only dummy data, for us to review.


My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Sep 18 2019, 01:28 PM

Posts: 3
Joined: 18-September 19

File is too big even with only 5 records. How do I Upload?
This post has been edited by rican8198: Sep 18 2019, 01:32 PM
Go to the top of the page
post Sep 18 2019, 05:20 PM

Utterly Eccentric and Moderator
Posts: 4,080
Joined: 4-March 00
From: Bristol / Ipswich / Spain

See my demo HERE you could adapt it: note 'today' counts as 1 whole day- from 'today to (today + 1day). This is not actually a trivial problem but the bones are there in the example.

BTW Compact & repair your db, then zip and post.

All the best,


I would like to remind members and visitors that UtterAccess is a NON SMOKING website. In that respect, it is the worlds first Thank you for not smoking.
Go to the top of the page
post Sep 18 2019, 09:51 PM

Posts: 3,331
Joined: 27-February 09

No need.. I think I got it.

First things first... I created a Calendar table that just contains a range of dates (no gaps/missing dates).

Then I created this query to return one date for each date for a visitor between the ArriveDate and LeaveDate.

SELECT Visits.EmployeeID, Visits.StartDON, Visits.EndDON, Calendar.[The Date]
FROM Visits, Calendar
WHERE (((Calendar.[The Date]) Between [Visits].[StartDON] And [Visits].[EndDON]));

Then I created another query based on that:
SELECT ExplodedDates.[The Date], Count(ExplodedDates.EmployeeID) AS CountOfEmployeeID
FROM ExplodedDates
GROUP BY ExplodedDates.[The Date];

What I end up with is a count of visitors per date. The only time this would mess up is if you can have overlapping visits by a single visitor, … doesn't make sense as it's impossible, but quite easy error to make when querying.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th October 2019 - 09:43 AM