Setting Up A Query Showing Count Of Fields

[Ange Kappas]

Hi,
I have a Query which has the following Fields.
COMPANY
ARRIVAL
DEPARTURE

In this Query the results displayed are as follows:

COMPANY ARRIVAL DEPARTURE
TCT 1-5-08 8-5-08
TCT 1-5-08 15-5-08
TCA 2-5-08 8-5-08
PRV 1-5-08 7-5-08
TCA 4-5-08 12-5-08
PRV 5-5-08 10-5-08
and so forth.

I want to setup a query where on the top it has the dates as per day and to
count as per each company per day.

In other words something like this

1-5-08 2-5-08 3-5-08 4-5-08 5-5-08 6-5-08

TCT 2 2 2 2 2
2
PRV 1 1 1 1 2
1
TCA 0 1 1 2 2
2

I know it has something to do with a Crosstab Query but I am having
difficulties with the dates and the ranges.

Can anyone help!

Thanks
Ange

 

[Michel Walsh]

One way to do it is, first, to normalize the data:


--------------in SQL view---------------
SELECT company, arrival AS theDate FROM tableName
UNION ALL
SELECT company, departure FROM tableName
----------------------------------------------

save it as, say, qu.


Then, use a crosstab:



--------------in SQL view-----------------
TRANSFORM COUNT(*)
SELECT company
FROM qu
GROUP BY company
PIVOT theDate

 

[Ange Kappas]

Thanks Michel but unfortunatly it does not return the results as I wanted
them. It returns the results like this:


1-5-08 2-5-08 4-5-08 4-5-08 5-5-08 7-5-08 8-5-08

TCT 2 0 0 2 0
0 2
PRV 1 0 0 1 1
1 0
TCA 0 1 1 2 0
0 0

instead of like this which is the desired result:

1-5-08 2-5-08 3-5-08 4-5-08 5-5-08 6-5-08
TCT 2 2 2 2 2
2
PRV 1 1 1 1 2
1
TCA 0 1 1 2 2
2

I think it only looks at the arrival and departure if there is a value and
it adds it without having a continuous date set.

Any Ideas?!?

Thanks
Ange

 

[John Spencer]

Step 1: create another table with all the dates in the range you are going
to examine.

Step 2: using that calendar table build a query to return one record for
each date in the range

SELECT T.Compamy, X.TheDate
FROM TheDateTable as X INNER JOIN YourQuery as T
ON X.TheDate >= T.Arrival and X.TheDate <= T.Departure

Step 3: use that saved query as the source for a crosstab query

Transform Count(Q.Company) as TheNumber
SELECT Q.Company
FROM TheAboveQuery as Q
GROUP BY Q.Company
PIVOT Q.TheDate



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

I failed to see that the dates were a range of dates rather than the point
in time I thought. John's solution should work, or a variation, using a
standard driver table (a table with one field, with values from 0 to 999, or
whatever limit is appropriate), calling that driver table Iotas and its
field, iota:

------------------------
Step 2:

SELECT T.Company, T.Arrival+ X.Iota
FROM Iotas as X INNER JOIN YourQuery as T
ON X.Iota>= T.Departure-T.Arrival
-------------------------




Vanderghast, Access MVP

 

[Ange Kappas]

HI John,
I used your directions as indicated with the following SQL but
it gives me an error message as: Syntax error in FROM clause pointing on the
word AS after the words COMPANY OCC which is the name of the Query as
indicated below as YourQuery.

SELECT RESERVATIONS.COMPANY, DAYS08.DAYS FROM DAYS08 AS RESERVATIONS INNER
JOIN COMPANY OCC AS RESERVATIONS ON DAYS08.DAYS >= RESERVATIONS.ARRIVAL and
DAYS08.DAYS <= RESERVATIONS.DEPARTURE

Don't know what I'm doing wrong?!?

Ange

 

[John Spencer]

Your table name has spaces in it. Field names and table names that hvae
spaces must be surrrounded with brackets [].
Also you cannot use the same name for two different objects. You used
Reservations as an "alias" for both Days08 and Company OCC

You might try the following.

SELECT RESERVATIONS.COMPANY
, DAYS08.DAYS
FROM DAYS08 INNER
JOIN [COMPANY OCC] AS RESERVATIONS
ON DAYS08.DAYS >= RESERVATIONS.ARRIVAL and
DAYS08.DAYS <= RESERVATIONS.DEPARTURE

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..