Crosstab Query using 2 date ranges

[Andeva]

Access 2003 - I created a database to track apartments, and I can pull a
crosstab query to see how many are occupied sorted by size of apartment.
This pulls the current census, but I'm wondering if I could pull a past
census based on the date an apartment was moved into and moved out of. Does
anyone have any suggestions for this? I was hoping that I could create a
query that would prompt the user the enter the date of the census they want,
and it would then provide the crosstab query for that date.

 

[KARL DEWEY]

Post the SQL of your current crosstab.
For new crosstab post example of your date spread and expected results.

 

[Andeva]

TRANSFORM Count(Inventory.[Apartment #]) AS [CountOfApartment #]
SELECT Inventory.[Apt Size], Count(Inventory.[Apartment #]) AS [Total Of
Apartment #]
FROM Inventory
WHERE (((Inventory.Location)="Bridgegate"))
GROUP BY Inventory.[Apt Size], Inventory.Location
ORDER BY Inventory.[Apt Size]
PIVOT Inventory.[Apt Status];


The database is based on apartment inventory, and I have a table linked to
it listing residents and their move-in dates and move-out dates. I would
like a crosstab query that looks the same, but it would pull the number of
apartments that were occupied on a past date based on how many residents were
in an apartment at that time. Please let me know if you need more
information. Thanks!

 

[KARL DEWEY]

it would pull the number of apartments that were occupied on a past date
based on how many residents were in an apartment at that time.
Post example of how the output would look --
xxxx yy1 yy2 yy3
sss 1 5 0
ttt 7 1 4
Replace the xxx, yy1, etc with fields.

--
Build a little, test a little.

TRANSFORM Count(Inventory.[Apartment #]) AS [CountOfApartment #]
SELECT Inventory.[Apt Size], Count(Inventory.[Apartment #]) AS [Total Of
Apartment #]
FROM Inventory
WHERE (((Inventory.Location)="Bridgegate"))
GROUP BY Inventory.[Apt Size], Inventory.Location
ORDER BY Inventory.[Apt Size]
PIVOT Inventory.[Apt Status];


The database is based on apartment inventory, and I have a table linked to
it listing residents and their move-in dates and move-out dates. I would
like a crosstab query that looks the same, but it would pull the number of
apartments that were occupied on a past date based on how many residents were
in an apartment at that time. Please let me know if you need more
information. Thanks!

Post the SQL of your current crosstab.
For new crosstab post example of your date spread and expected results.

 

[KARL DEWEY]

You should have two tables. Appartment and Occupancy as separate data.
Apartment --
AptID - autonumber - primary key
Apartment #
Apt_Size
Location
Apt Status - maybe here if you are using Occupied & Vacant. Better would be
to compute from Occupancy data.

Occupancy --
OccupID - Autonumber - primary key
AptID - number - long integer - foreign key
Move-in - DateTime
Move-out - DateTime
Occupants - number
... etc

--
Build a little, test a little.

TRANSFORM Count(Inventory.[Apartment #]) AS [CountOfApartment #]
SELECT Inventory.[Apt Size], Count(Inventory.[Apartment #]) AS [Total Of
Apartment #]
FROM Inventory
WHERE (((Inventory.Location)="Bridgegate"))
GROUP BY Inventory.[Apt Size], Inventory.Location
ORDER BY Inventory.[Apt Size]
PIVOT Inventory.[Apt Status];


The database is based on apartment inventory, and I have a table linked to
it listing residents and their move-in dates and move-out dates. I would
like a crosstab query that looks the same, but it would pull the number of
apartments that were occupied on a past date based on how many residents were
in an apartment at that time. Please let me know if you need more
information. Thanks!

Post the SQL of your current crosstab.
For new crosstab post example of your date spread and expected results.

 

[KenSheridan via AccessMonster.com]

Try something along these lines:

PARAMETERS [Enter census date:] DATETIME;
TRANSFORM COUNT(*)
SELECT[Apt Size], COUNT(*) AS [Total Occupied]
FROM [Inventory ], [Residents]
WHERE [Inventory].[Apartment #] = [Residents].[Apartment #]
AND [Location] = "Bridgegate" AND
[Enter census date:] BETWEEN [MoveInDate]
AND [MoveOutDate]
GROUP BY [Apt Size]
PIVOT[Apt Status];

Ken Sheridan
Stafford, England

TRANSFORM Count(Inventory.[Apartment #]) AS [CountOfApartment #]
SELECT Inventory.[Apt Size], Count(Inventory.[Apartment #]) AS [Total Of
Apartment #]
FROM Inventory
WHERE (((Inventory.Location)="Bridgegate"))
GROUP BY Inventory.[Apt Size], Inventory.Location
ORDER BY Inventory.[Apt Size]
PIVOT Inventory.[Apt Status];

The database is based on apartment inventory, and I have a table linked to
it listing residents and their move-in dates and move-out dates. I would
like a crosstab query that looks the same, but it would pull the number of
apartments that were occupied on a past date based on how many residents were
in an apartment at that time. Please let me know if you need more
information. Thanks!

Post the SQL of your current crosstab.
For new crosstab post example of your date spread and expected results.

[quoted text clipped - 6 lines]