B
Bauhaus
I have an Excell grid with shop names as columnheadings, dates as
rowheadings and people's names as cell values:
http://bauhaus.dommel.be/rooster.jpg
Meaning: in every shop at any date we have a number of people working.
I wanted to put this in a database so I made a database which has a table
ShopsPerson with 3 columns (Date, NameID and ShopID)
Now I want a query whose output should look like the Excell grid, i.e.
something like this:
ShopID1 ShopID2 ShopID3 ...
Date1 NameID NameID NameID
Date1 NameID NameID NameID
Date1 NameID NameID Null
Date2 NameID NameID NameID
Date2 NameID NameID NameID
Date2 NameID Null NameID
Date2 Null Null NameID
Date3 NameID NameID NameID
Date3 NameID NameID NameID
Date3 NameID NameID Null
....
First I thought of a crosstab query, since there are rowheadings and
columnheadings. Unfortunately only one value for a specific date and shop is
possible, while there are several nameID's.
Next I tried a simple query with aliases:
SELECT a1.Date, a1.NameID, a2.NameID
FROM ShopsPerson AS a1, ShopsPerson AS a2
WHERE a1.Date=a2.Date and a1.ShopID<a2.ShopID;
Output:
Date a1.NameID a2.NameID
2009-02-02 3 6
2009-02-02 39 6
2009-02-02 7 6
2009-02-02 2 6
2009-02-02 3 7
2009-02-02 39 7
2009-02-02 7 7
2009-02-02 2 7
....
As you see, for every nameID of shop2, all rows of shop1 are repeated...
While it should be like this:
Date a1.NameID a2.NameID
2009-02-02 3 6
2009-02-02 39 7
2009-02-02 7 Null
2009-02-02 2 Null
Another problem here is to put Nulls in the query output, since only 2
people work at shop2 while 4 people work on shop1 (for date 2009-02-02).
Any suggestions how to solve this ?
rowheadings and people's names as cell values:
http://bauhaus.dommel.be/rooster.jpg
Meaning: in every shop at any date we have a number of people working.
I wanted to put this in a database so I made a database which has a table
ShopsPerson with 3 columns (Date, NameID and ShopID)
Now I want a query whose output should look like the Excell grid, i.e.
something like this:
ShopID1 ShopID2 ShopID3 ...
Date1 NameID NameID NameID
Date1 NameID NameID NameID
Date1 NameID NameID Null
Date2 NameID NameID NameID
Date2 NameID NameID NameID
Date2 NameID Null NameID
Date2 Null Null NameID
Date3 NameID NameID NameID
Date3 NameID NameID NameID
Date3 NameID NameID Null
....
First I thought of a crosstab query, since there are rowheadings and
columnheadings. Unfortunately only one value for a specific date and shop is
possible, while there are several nameID's.
Next I tried a simple query with aliases:
SELECT a1.Date, a1.NameID, a2.NameID
FROM ShopsPerson AS a1, ShopsPerson AS a2
WHERE a1.Date=a2.Date and a1.ShopID<a2.ShopID;
Output:
Date a1.NameID a2.NameID
2009-02-02 3 6
2009-02-02 39 6
2009-02-02 7 6
2009-02-02 2 6
2009-02-02 3 7
2009-02-02 39 7
2009-02-02 7 7
2009-02-02 2 7
....
As you see, for every nameID of shop2, all rows of shop1 are repeated...
While it should be like this:
Date a1.NameID a2.NameID
2009-02-02 3 6
2009-02-02 39 7
2009-02-02 7 Null
2009-02-02 2 Null
Another problem here is to put Nulls in the query output, since only 2
people work at shop2 while 4 people work on shop1 (for date 2009-02-02).
Any suggestions how to solve this ?