General discussion

Locked

Occupancy Query In Access

By Okestra007 ·
I am having a big problem writing a query that will give the right answer to this problem.

Here's my scenario, let say you have a house, and people are allow to go in and out of the house at anytime all year round, the only requirement is that their entry and exit date/time must be noted, and each person is given a unique ID on entry, but if a person leaves then come back he/she must still use the initial ID assigned.

Now, here's my question; how do you use MS Access to query how many people occupy the house say in the whole month of January, April, June ..., basically what's the occupancy of the house within a given month, date range, time....

Thanks yall.

This conversation is currently closed to new comments.

13 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

This is a safe house for illegals

by santeewelding In reply to Occupancy Query In Access

Isn't it?

Either that, or homework.

Collapse -

Not A Safe House For Illegals

by Okestra007 In reply to This is a safe house for ...

Sorry, but this is a serious work, I only use "House" as scenario. I will appreciate any help.

Collapse -

I see

by santeewelding In reply to Not A Safe House For Ille ...

You conduct your serious work on behalf of the National Security Agency, Central Intelligence Agency, and Department of Defense combined. Tell us any more and you would have to kill somebody.

Collapse -

One day

by santeewelding In reply to Not A Safe House For Ille ...

Someone will show up at my door, in view of my efforts to win friends and influence people, with a different perspective.

Will that be you, *** hole?

You blew it with, "serious".

I skipped right on past "serious" with the notion that anyone in their right mind would see it as completely subjective, foisted as objective.

You do that, and you earn my abiding ridicule.

Don't care what you are up to. You blew it.

Collapse -

Ah a classic time recording scenario

by Tony Hopkinson In reply to Occupancy Query In Access

There's a trick to this and it's

Create a table called occupancyMonths
say
Year,Month,StartOfMonth,EndOfMonth and populate it with the ranges you are interested in reporting on.

The if you have an Occupancy table like
OccupancyID, ResidentID, DateEntered, Dateleft

Then given you want to include those who were there for part of the month, that you have some who were there for more than one month and coping with those who had a period of occupancy that spanned a year Dec 2010 to Jan 2010 say...

Select DistinctRow PersonID,[Year],[Month] From OccupancyMonths m
join Occupancy o on
(m.Year = DatePart(year,o.DateEntered
and
m.Month = DatePart(month,o.DateEntered)
or
(
(m.Year = DatePart(year,o.DateLeft
and
m.Month = DatePart(month,o.DateLeft)
)
Where m.year = 2010

will give you the people who were there in any part of the month for that year

In access you'd make this one query Q_PeopleInMonth, in a proper sql compliant DBMS it would be an inner query

either way

Select [Month],Count(*) From Q_PeopleInmonth

should do the job

Haven't tested the syntax, bit the method is sound, I've used something like it several times.

In this case you could do away with OccupancyMonths but the sql would be horrendous and I fail to see the point.

If you can't figure out how to get this in the graphical query builder in access, switch to sql view, type in the sql, prove it and then flip back, I couldn't tell you anyway, never use them, come to think of it I don't use access except at gun point.

Donlt worry about not seeing this trick, took me well over a day to come up with it and I've been doing this 92.
It's one of those obvious after you've been told maneouvres.

Comes in handy for all sorts of things like seeing how many days each employee was working, or what rooms you have free in a hotel and such.

Collapse -

Thanks Tony

by Okestra007 In reply to Ah a classic time recordi ...

Thanks Tony, I see the logic in your thinking, but what if I don't have the privilege to create a table, I am limited to read and query access? How do you suggest I do this? All I have is a table with every transactions which include:

ClientID (Unique but can repeat)
FirstName
LastName
EntryDate
ExitDate
....

Collapse -

Okay, NOW it smells like homework. No text.

by CharlieSpencer In reply to Thanks Tony
Collapse -

No I'm not having that

by Tony Hopkinson In reply to Thanks Tony

You claimed to be a DBA, if a DBA can't create a table who can?

So what's the primary key on that shite.
You have got a key haven't you?

ClientID,EntryDate,ExitDate ?

WTF happened to normalisation?

FirstName and LastName shouldn't be in there!

The solution to your difficulty with not being able to use a table is obvious you use a query don't you?

Oh well seeing as I've already done the hard bit for you.

Select 2010 as [Year], 1 as Month, #2010-01-01# as MonthStarted, #2010-01-31# asMonthEnded
Union

...

Union
Select 2010,12,#2010-12-01#, #2010-12-31#

By the way thanks for reinforcing my opinion that access is not a tool you teach databases with.


Download SQL Server Express, learn SQL , access will only cause you problems with it's limitations, and you will be fit for the real world. At the moment I wouldn't pay you to clean tables never mind create them.

Collapse -

Thanks Yall

by Okestra007 In reply to No I'm not having that

Thanks everyone, but I found a much easier way to get this done, it only cost me 3 layers of queries, and I will have to change the date range in query 2 for each month.

Thanks a lot guy, most especially Tony. All my college days, I used SQL and very very little Access, but when I got a job, I was limited and required to use Access, and we both know SQL query is not 100% compatible with Access SQL query.

By the way Tony, how much you willing to pay me again?

Collapse -

I can't believe no one had the EZ answer to this

by WhiteKnight_ In reply to Occupancy Query In Access

SELECT DISTINCT PERSONID FROM XXXXXX WHERE DATE IN ...

If you just want the count then you do a Select Distinct Count query. Doesn't anyone know SQL anymore?

Back to Web Development Forum
13 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums