Problem with displaying query as a 2D grid

  • Thread starter Thread starter Bauhaus
  • Start date Start date
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 ?
 
I tried a crosstab query on your familydatabase by changing the Count()
function with Concatenate() in the SQL statement, but I get an error that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem WHERE FamID ="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;
 
Did you add the function to a module in your MDB?

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
I tried a crosstab query on your familydatabase by changing the Count()
function with Concatenate() in the SQL statement, but I get an error that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem WHERE FamID ="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;
Click to expand...
 
Yes, there's a module basConcatenate.


Duane Hookom said:
Did you add the function to a module in your MDB?

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
I tried a crosstab query on your familydatabase by changing the Count()
function with Concatenate() in the SQL statement, but I get an error that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS
CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and
CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem WHERE FamID
="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;


Duane Hookom said:
You can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=68af51b8779778122zba342z4288b834
as the value in a crosstab query.

If that doesn't work, you would need to create a ranking field in your
query
that identifies the first, second, third,... NameID by date and shop.
--
Duane Hookom
Microsoft Access MVP


:

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 ?
Click to expand...
Click to expand...
Click to expand...
 
I think you need to select a Total of "First" for the contcatenate value. If
you can't figure this out, can you provide the significant table/query and
field names?

Creating a crosstab in my sample MDB would require creating a "dummy" column
for the Row Heading:

TRANSFORM First(Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID = "
& [FamID],Chr(13) & Chr(10))) AS Expr1
SELECT "Record" AS Row
FROM tblFamily
GROUP BY "Record"
PIVOT tblFamily.famLastName;

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
Yes, there's a module basConcatenate.


Duane Hookom said:
Did you add the function to a module in your MDB?

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
I tried a crosstab query on your familydatabase by changing the Count()
function with Concatenate() in the SQL statement, but I get an error that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS
CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and
CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem WHERE FamID
="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
You can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=68af51b8779778122zba342z4288b834
as the value in a crosstab query.

If that doesn't work, you would need to create a ranking field in your
query
that identifies the first, second, third,... NameID by date and shop.
--
Duane Hookom
Microsoft Access MVP


:

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 ?
Click to expand...
Click to expand...
Click to expand...
 
I tried First() with Concatenate() but then I only get the first name of the
Concatenate() function.
(which is odd, the function returns a string... does Access use the
delimiter "," to separate the valus in the string ?)
Not sure what you mean with the dummy column, how should I use it with the
crosstab I mentioned below ?

Duane Hookom said:
I think you need to select a Total of "First" for the contcatenate value.
If
you can't figure this out, can you provide the significant table/query and
field names?

Creating a crosstab in my sample MDB would require creating a "dummy"
column
for the Row Heading:

TRANSFORM First(Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =
"
& [FamID],Chr(13) & Chr(10))) AS Expr1
SELECT "Record" AS Row
FROM tblFamily
GROUP BY "Record"
PIVOT tblFamily.famLastName;

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
Yes, there's a module basConcatenate.


Duane Hookom said:
Did you add the function to a module in your MDB?

--
Duane Hookom
Microsoft Access MVP


:

I tried a crosstab query on your familydatabase by changing the
Count()
function with Concatenate() in the SQL statement, but I get an error
that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS
CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and
CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem WHERE
FamID
="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
You can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=68af51b8779778122zba342z4288b834
as the value in a crosstab query.

If that doesn't work, you would need to create a ranking field in
your
query
that identifies the first, second, third,... NameID by date and
shop.
--
Duane Hookom
Microsoft Access MVP


:

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 ?
Click to expand...
Click to expand...
Click to expand...
 
Reply back with your full sql view so we can help. I expect your might just
see one name if your row height only reveals one value. What do you see if
you enlarge the row height?

I had to use a dummy column because the data was 1D while yours is 2D.

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
I tried First() with Concatenate() but then I only get the first name of the
Concatenate() function.
(which is odd, the function returns a string... does Access use the
delimiter "," to separate the valus in the string ?)
Not sure what you mean with the dummy column, how should I use it with the
crosstab I mentioned below ?

Duane Hookom said:
I think you need to select a Total of "First" for the contcatenate value.
If
you can't figure this out, can you provide the significant table/query and
field names?

Creating a crosstab in my sample MDB would require creating a "dummy"
column
for the Row Heading:

TRANSFORM First(Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =
"
& [FamID],Chr(13) & Chr(10))) AS Expr1
SELECT "Record" AS Row
FROM tblFamily
GROUP BY "Record"
PIVOT tblFamily.famLastName;

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
Yes, there's a module basConcatenate.


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
Did you add the function to a module in your MDB?

--
Duane Hookom
Microsoft Access MVP


:

I tried a crosstab query on your familydatabase by changing the
Count()
function with Concatenate() in the SQL statement, but I get an error
that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS
CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and
CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem WHERE
FamID
="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
You can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=68af51b8779778122zba342z4288b834
as the value in a crosstab query.

If that doesn't work, you would need to create a ranking field in
your
query
that identifies the first, second, third,... NameID by date and
shop.
--
Duane Hookom
Microsoft Access MVP


:

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 ?
Click to expand...
Click to expand...
Click to expand...
 
You're correct, I had to enlarge the row height.
I managed to use the Concatenate() with my own database and it works fine:

TRANSFORM First(Concatenate("SELECT Name & Chr(9) FROM [Person Query] WHERE
ShopName = " & "'" & [Person Query].ShopName & "'" & " and [Person
Query].Date= " & "#" & [Person Query].Date & "#", Chr(13) & Chr(10))) AS
FirstOfNaamID
SELECT [Person Query].Date
FROM [Person Query]
GROUP BY [Person Query].Date
PIVOT [Person Query].ShopName;

But there's one problem: the columns are now ordered alphabetically by
ShopName (from the Person Query), but I want them to be ordered by ShopID
(from the ShopsPerson table).
I tried to incorporate it in the Concatenate() function:

TRANSFORM First(Concatenate("SELECT Name & Chr(9) FROM [Person Query],
ShopsPerson WHERE ShopName = " & "'" & [Person Query].ShopName & "'" & " and
[Person Query].Date= " & "#" & [Person Query].Date & "#" & " ORDER BY " &
ShopsPerson.ShopID, Chr(13) & Chr(10))) AS FirstOfNaamID
SELECT [Person Query].Date
FROM [Person Query]
GROUP BY [Person Query].Date
PIVOT [Person Query].ShopName;

But that didn't work.


Duane Hookom said:
Reply back with your full sql view so we can help. I expect your might
just
see one name if your row height only reveals one value. What do you see if
you enlarge the row height?

I had to use a dummy column because the data was 1D while yours is 2D.

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
I tried First() with Concatenate() but then I only get the first name of
the
Concatenate() function.
(which is odd, the function returns a string... does Access use the
delimiter "," to separate the valus in the string ?)
Not sure what you mean with the dummy column, how should I use it with
the
crosstab I mentioned below ?

Duane Hookom said:
I think you need to select a Total of "First" for the contcatenate
value.
If
you can't figure this out, can you provide the significant table/query
and
field names?

Creating a crosstab in my sample MDB would require creating a "dummy"
column
for the Row Heading:

TRANSFORM First(Concatenate("SELECT FirstName FROM tblFamMem WHERE
FamID =
"
& [FamID],Chr(13) & Chr(10))) AS Expr1
SELECT "Record" AS Row
FROM tblFamily
GROUP BY "Record"
PIVOT tblFamily.famLastName;

--
Duane Hookom
Microsoft Access MVP


:

Yes, there's a module basConcatenate.


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
Did you add the function to a module in your MDB?

--
Duane Hookom
Microsoft Access MVP


:

I tried a crosstab query on your familydatabase by changing the
Count()
function with Concatenate() in the SQL statement, but I get an
error
that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM
tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS
CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and
CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem WHERE
FamID
="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and
CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
You can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=68af51b8779778122zba342z4288b834
as the value in a crosstab query.

If that doesn't work, you would need to create a ranking field in
your
query
that identifies the first, second, third,... NameID by date and
shop.
--
Duane Hookom
Microsoft Access MVP


:

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 ?
Click to expand...
Click to expand...
Click to expand...
 
If you want the columns to appear in a different order, you can enter the
ShopName values into the Column Headings property of the crosstab query.

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
You're correct, I had to enlarge the row height.
I managed to use the Concatenate() with my own database and it works fine:

TRANSFORM First(Concatenate("SELECT Name & Chr(9) FROM [Person Query] WHERE
ShopName = " & "'" & [Person Query].ShopName & "'" & " and [Person
Query].Date= " & "#" & [Person Query].Date & "#", Chr(13) & Chr(10))) AS
FirstOfNaamID
SELECT [Person Query].Date
FROM [Person Query]
GROUP BY [Person Query].Date
PIVOT [Person Query].ShopName;

But there's one problem: the columns are now ordered alphabetically by
ShopName (from the Person Query), but I want them to be ordered by ShopID
(from the ShopsPerson table).
I tried to incorporate it in the Concatenate() function:

TRANSFORM First(Concatenate("SELECT Name & Chr(9) FROM [Person Query],
ShopsPerson WHERE ShopName = " & "'" & [Person Query].ShopName & "'" & " and
[Person Query].Date= " & "#" & [Person Query].Date & "#" & " ORDER BY " &
ShopsPerson.ShopID, Chr(13) & Chr(10))) AS FirstOfNaamID
SELECT [Person Query].Date
FROM [Person Query]
GROUP BY [Person Query].Date
PIVOT [Person Query].ShopName;

But that didn't work.


Duane Hookom said:
Reply back with your full sql view so we can help. I expect your might
just
see one name if your row height only reveals one value. What do you see if
you enlarge the row height?

I had to use a dummy column because the data was 1D while yours is 2D.

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
I tried First() with Concatenate() but then I only get the first name of
the
Concatenate() function.
(which is odd, the function returns a string... does Access use the
delimiter "," to separate the valus in the string ?)
Not sure what you mean with the dummy column, how should I use it with
the
crosstab I mentioned below ?

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
I think you need to select a Total of "First" for the contcatenate
value.
If
you can't figure this out, can you provide the significant table/query
and
field names?

Creating a crosstab in my sample MDB would require creating a "dummy"
column
for the Row Heading:

TRANSFORM First(Concatenate("SELECT FirstName FROM tblFamMem WHERE
FamID =
"
& [FamID],Chr(13) & Chr(10))) AS Expr1
SELECT "Record" AS Row
FROM tblFamily
GROUP BY "Record"
PIVOT tblFamily.famLastName;

--
Duane Hookom
Microsoft Access MVP


:

Yes, there's a module basConcatenate.


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
Did you add the function to a module in your MDB?

--
Duane Hookom
Microsoft Access MVP


:

I tried a crosstab query on your familydatabase by changing the
Count()
function with Concatenate() in the SQL statement, but I get an
error
that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM
tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS
CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and
CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem WHERE
FamID
="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and
CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
You can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=68af51b8779778122zba342z4288b834
as the value in a crosstab query.

If that doesn't work, you would need to create a ranking field in
your
query
that identifies the first, second, third,... NameID by date and
shop.
--
Duane Hookom
Microsoft Access MVP


:

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 ?
Click to expand...
Click to expand...
Click to expand...
 
Yep, works fine.
Thanks for the help Duane, much appreciated.


Duane Hookom said:
If you want the columns to appear in a different order, you can enter the
ShopName values into the Column Headings property of the crosstab query.

--
Duane Hookom
Microsoft Access MVP


Bauhaus said:
You're correct, I had to enlarge the row height.
I managed to use the Concatenate() with my own database and it works
fine:

TRANSFORM First(Concatenate("SELECT Name & Chr(9) FROM [Person Query]
WHERE
ShopName = " & "'" & [Person Query].ShopName & "'" & " and [Person
Query].Date= " & "#" & [Person Query].Date & "#", Chr(13) & Chr(10))) AS
FirstOfNaamID
SELECT [Person Query].Date
FROM [Person Query]
GROUP BY [Person Query].Date
PIVOT [Person Query].ShopName;

But there's one problem: the columns are now ordered alphabetically by
ShopName (from the Person Query), but I want them to be ordered by ShopID
(from the ShopsPerson table).
I tried to incorporate it in the Concatenate() function:

TRANSFORM First(Concatenate("SELECT Name & Chr(9) FROM [Person Query],
ShopsPerson WHERE ShopName = " & "'" & [Person Query].ShopName & "'" & "
and
[Person Query].Date= " & "#" & [Person Query].Date & "#" & " ORDER BY " &
ShopsPerson.ShopID, Chr(13) & Chr(10))) AS FirstOfNaamID
SELECT [Person Query].Date
FROM [Person Query]
GROUP BY [Person Query].Date
PIVOT [Person Query].ShopName;

But that didn't work.


Duane Hookom said:
Reply back with your full sql view so we can help. I expect your might
just
see one name if your row height only reveals one value. What do you see
if
you enlarge the row height?

I had to use a dummy column because the data was 1D while yours is 2D.

--
Duane Hookom
Microsoft Access MVP


:

I tried First() with Concatenate() but then I only get the first name
of
the
Concatenate() function.
(which is odd, the function returns a string... does Access use the
delimiter "," to separate the valus in the string ?)
Not sure what you mean with the dummy column, how should I use it with
the
crosstab I mentioned below ?

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
I think you need to select a Total of "First" for the contcatenate
value.
If
you can't figure this out, can you provide the significant
table/query
and
field names?

Creating a crosstab in my sample MDB would require creating a
"dummy"
column
for the Row Heading:

TRANSFORM First(Concatenate("SELECT FirstName FROM tblFamMem WHERE
FamID =
"
& [FamID],Chr(13) & Chr(10))) AS Expr1
SELECT "Record" AS Row
FROM tblFamily
GROUP BY "Record"
PIVOT tblFamily.famLastName;

--
Duane Hookom
Microsoft Access MVP


:

Yes, there's a module basConcatenate.


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in bericht
Did you add the function to a module in your MDB?

--
Duane Hookom
Microsoft Access MVP


:

I tried a crosstab query on your familydatabase by changing the
Count()
function with Concatenate() in the SQL statement, but I get an
error
that
Concatenate() isnt part of a static function.

Here's the code:

TRANSFORM Concatenate("SELECT FirstName & Chr(9) & Role FROM
tblFamMem
WHERE FamID =" & [tblFamily.FamID],Chr(13) & Chr(10)) AS
CountOfFirstNames
SELECT [Example of First Names and Roles in Family with Tab and
CrLf].FamID,
Concatenate("SELECT FirstName & Chr(9) & Role FROM tblFamMem
WHERE
FamID
="
& [tblFamily.FamID],Chr(13) & Chr(10)) AS [Total Of FirstNames]
FROM [Example of First Names and Roles in Family with Tab and
CrLf],
tblFamily
GROUP BY [Example of First Names and Roles in Family with Tab
and
CrLf].FamID
PIVOT [Example of First Names and Roles in Family with Tab and
CrLf].famLastName;


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> schreef in
bericht
You can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=68af51b8779778122zba342z4288b834
as the value in a crosstab query.

If that doesn't work, you would need to create a ranking field
in
your
query
that identifies the first, second, third,... NameID by date
and
shop.
--
Duane Hookom
Microsoft Access MVP


:

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 ?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top