Value in Cross tab query

[Guest]

I have a cross tab query to show date, time, customer and staff as a
schedule. It should look like this:
DATE TIME STAFF1 STAFF2 STAFF3
10/19 08:30 Smith Jones
10/19 09:00 Anderson
10/19 10:00
10/19 10:30 Davidson

But I cannot get the customer's names in the table when I set them to
'Value' - then I have to specify sum or number of records to get the query
running. Isn't there a way to retrieve the names and put them in the result?

/ Rolf

 

[Guest]

Try this --
TRANSFORM First(Rolf.Name) AS FirstOfName
SELECT Rolf.StaffDate
FROM Rolf
GROUP BY Rolf.StaffDate
PIVOT "Shift " & [Staff];

 

[Guest]

I got a syntax error in TRANSFORM-expression when I wrote:
TRANSFORM First(Kunder.Efternamn) AS FirstOfEfternamn

'Kunder' is my customer's table, 'Efternamn' is the customer's name
Did I do anything wrong by that?

/ Rolf

Try this --
TRANSFORM First(Rolf.Name) AS FirstOfName
SELECT Rolf.StaffDate
FROM Rolf
GROUP BY Rolf.StaffDate
PIVOT "Shift " & [Staff];

I have a cross tab query to show date, time, customer and staff as a
schedule. It should look like this:
DATE TIME STAFF1 STAFF2 STAFF3
10/19 08:30 Smith Jones
10/19 09:00 Anderson
10/19 10:00
10/19 10:30 Davidson

But I cannot get the customer's names in the table when I set them to
'Value' - then I have to specify sum or number of records to get the query
running. Isn't there a way to retrieve the names and put them in the result?

/ Rolf

 

[Guest]

Yep.
It needs to be --
TRANSFORM First([Kunder].[TheFieldName]) AS First_Name

I got a syntax error in TRANSFORM-expression when I wrote:
TRANSFORM First(Kunder.Efternamn) AS FirstOfEfternamn

'Kunder' is my customer's table, 'Efternamn' is the customer's name
Did I do anything wrong by that?

/ Rolf

Try this --
TRANSFORM First(Rolf.Name) AS FirstOfName
SELECT Rolf.StaffDate
FROM Rolf
GROUP BY Rolf.StaffDate
PIVOT "Shift " & [Staff];

I have a cross tab query to show date, time, customer and staff as a
schedule. It should look like this:
DATE TIME STAFF1 STAFF2 STAFF3
10/19 08:30 Smith Jones
10/19 09:00 Anderson
10/19 10:00
10/19 10:30 Davidson

But I cannot get the customer's names in the table when I set them to
'Value' - then I have to specify sum or number of records to get the query
running. Isn't there a way to retrieve the names and put them in the result?

/ Rolf

 

[Guest]

Now I wrote: TRANSFORM First([Kunder].[Efternamn]) AS First_Name

but still got the syntax error. I think the syntax is a very well kept
secret, as I cannot find any information at all about TRANSFORM. Neither in
the Help nor in any of the 8 books I have about Access, Visual Basic, SQL and
MySQL. I thought that I would find it in at least one or two of them....
Neither of them is any thin pocket book.

Are you sure the line should look like that?

/ Rolf

Yep.
It needs to be --
TRANSFORM First([Kunder].[TheFieldName]) AS First_Name

I got a syntax error in TRANSFORM-expression when I wrote:
TRANSFORM First(Kunder.Efternamn) AS FirstOfEfternamn

'Kunder' is my customer's table, 'Efternamn' is the customer's name
Did I do anything wrong by that?

/ Rolf

Try this --
TRANSFORM First(Rolf.Name) AS FirstOfName
SELECT Rolf.StaffDate
FROM Rolf
GROUP BY Rolf.StaffDate
PIVOT "Shift " & [Staff];


:

I have a cross tab query to show date, time, customer and staff as a
schedule. It should look like this:
DATE TIME STAFF1 STAFF2 STAFF3
10/19 08:30 Smith Jones
10/19 09:00 Anderson
10/19 10:00
10/19 10:30 Davidson

But I cannot get the customer's names in the table when I set them to
'Value' - then I have to specify sum or number of records to get the query
running. Isn't there a way to retrieve the names and put them in the result?

/ Rolf

 

[Guest]

You said that "Efternamn" was the persons name. You can not use it with the
table name. You must use the table name and the name of the field that
contains the persons name.

Now I wrote: TRANSFORM First([Kunder].[Efternamn]) AS First_Name

but still got the syntax error. I think the syntax is a very well kept
secret, as I cannot find any information at all about TRANSFORM. Neither in
the Help nor in any of the 8 books I have about Access, Visual Basic, SQL and
MySQL. I thought that I would find it in at least one or two of them....
Neither of them is any thin pocket book.

Are you sure the line should look like that?

/ Rolf

Yep.
It needs to be --
TRANSFORM First([Kunder].[TheFieldName]) AS First_Name

I got a syntax error in TRANSFORM-expression when I wrote:
TRANSFORM First(Kunder.Efternamn) AS FirstOfEfternamn

'Kunder' is my customer's table, 'Efternamn' is the customer's name
Did I do anything wrong by that?

/ Rolf



:

Try this --
TRANSFORM First(Rolf.Name) AS FirstOfName
SELECT Rolf.StaffDate
FROM Rolf
GROUP BY Rolf.StaffDate
PIVOT "Shift " & [Staff];


:

I have a cross tab query to show date, time, customer and staff as a
schedule. It should look like this:
DATE TIME STAFF1 STAFF2 STAFF3
10/19 08:30 Smith Jones
10/19 09:00 Anderson
10/19 10:00
10/19 10:30 Davidson

But I cannot get the customer's names in the table when I set them to
'Value' - then I have to specify sum or number of records to get the query
running. Isn't there a way to retrieve the names and put them in the result?

/ Rolf

 

[David F Cox]

My guess was [Efternamn] was the equivalent of "AfterName" and was a field
name. If it was an unrecognised field he would have been asked for its
value. He has a syntax error, and I do not think we can help with that
unless he shows us the whole query or code.

You said that "Efternamn" was the persons name. You can not use it with
the
table name. You must use the table name and the name of the field that
contains the persons name.

Now I wrote: TRANSFORM First([Kunder].[Efternamn]) AS First_Name

but still got the syntax error. I think the syntax is a very well kept
secret, as I cannot find any information at all about TRANSFORM. Neither
in
the Help nor in any of the 8 books I have about Access, Visual Basic, SQL
and
MySQL. I thought that I would find it in at least one or two of them....
Neither of them is any thin pocket book.

Are you sure the line should look like that?

/ Rolf

Yep.
It needs to be --
TRANSFORM First([Kunder].[TheFieldName]) AS First_Name

:

I got a syntax error in TRANSFORM-expression when I wrote:
TRANSFORM First(Kunder.Efternamn) AS FirstOfEfternamn

'Kunder' is my customer's table, 'Efternamn' is the customer's name
Did I do anything wrong by that?

/ Rolf



:

Try this --
TRANSFORM First(Rolf.Name) AS FirstOfName
SELECT Rolf.StaffDate
FROM Rolf
GROUP BY Rolf.StaffDate
PIVOT "Shift " & [Staff];


:

I have a cross tab query to show date, time, customer and staff
as a
schedule. It should look like this:
DATE TIME STAFF1 STAFF2 STAFF3
10/19 08:30 Smith Jones
10/19 09:00 Anderson
10/19 10:00
10/19 10:30 Davidson

But I cannot get the customer's names in the table when I set
them to
'Value' - then I have to specify sum or number of records to get
the query
running. Isn't there a way to retrieve the names and put them in
the result?

/ Rolf