Overview of data

[Guest]

I am trying to display my data in an overview per day, but cannot come up
with a method that seems to work. First I tried with a cross tab query and
got the design to work, but the data seems not to be what I want. My problem
is that I need text data in the columns, not numbers that the cross tab is
doing.

My goal is a day schematic with time and customer's name on the rows and the
staff names as column headings. 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

Is there another way to get this than with cross tab queries?

I have been thinking of how to populate an array, but did not grasp how to
present the array in a form.

Or is there a better way?

PS
Please forgive me for a kind of crossposting, as I have asked in the query
department first and now no longer think that the cross tab can present the
data as text, only numbers. So I am prone to give up that idea.

/ Rolf

 

[David F Cox]

This is a query problem, the multiple posting does not help the functioning
of these groups.

A Crosstab query can give you the result you want. I just cannot tell you
what it is because I do not have enough information about your problem.
This little test query of mine works beautifully.

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT Table1.pets
FROM Table1
GROUP BY Table1.pets
PIVOT "shift" & [month1];

 

[Guest]

Thanks, it works just beautiful as you say. Now also with my tables! There is
now just one more wish. The Time field shows the clock time for the bookings.
And I have every hour of the day as records in a seperate time-table.

Now, in the query only those rows that have a record of customer's name, and
of course a number reference to the time table, show up. I wanted all the
hours for every day to be shown as a row, even if there is no customer
specified for all the hours.

Is that also possible to do?
/ Rolf

This is a query problem, the multiple posting does not help the functioning
of these groups.

A Crosstab query can give you the result you want. I just cannot tell you
what it is because I do not have enough information about your problem.
This little test query of mine works beautifully.

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT Table1.pets
FROM Table1
GROUP BY Table1.pets
PIVOT "shift" & [month1];

I am trying to display my data in an overview per day, but cannot come up
with a method that seems to work. First I tried with a cross tab query and
got the design to work, but the data seems not to be what I want. My
problem
is that I need text data in the columns, not numbers that the cross tab is
doing.

My goal is a day schematic with time and customer's name on the rows and
the
staff names as column headings. 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

Is there another way to get this than with cross tab queries?

I have been thinking of how to populate an array, but did not grasp how to
present the array in a form.

Or is there a better way?

PS
Please forgive me for a kind of crossposting, as I have asked in the query
department first and now no longer think that the cross tab can present
the
data as text, only numbers. So I am prone to give up that idea.

/ Rolf

 

[David F Cox]

I see half-hours, and this is still not the queries group ->)
I would think this is possible, using a query as input with a join to a
little table containing all of the times you want to see.

Thanks, it works just beautiful as you say. Now also with my tables! There
is
now just one more wish. The Time field shows the clock time for the
bookings.
And I have every hour of the day as records in a seperate time-table.

Now, in the query only those rows that have a record of customer's name,
and
of course a number reference to the time table, show up. I wanted all the
hours for every day to be shown as a row, even if there is no customer
specified for all the hours.

Is that also possible to do?
/ Rolf

This is a query problem, the multiple posting does not help the
functioning
of these groups.

A Crosstab query can give you the result you want. I just cannot tell you
what it is because I do not have enough information about your problem.
This little test query of mine works beautifully.

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT Table1.pets
FROM Table1
GROUP BY Table1.pets
PIVOT "shift" & [month1];

I am trying to display my data in an overview per day, but cannot come
up
with a method that seems to work. First I tried with a cross tab query
and
got the design to work, but the data seems not to be what I want. My
problem
is that I need text data in the columns, not numbers that the cross tab
is
doing.

My goal is a day schematic with time and customer's name on the rows
and
the
staff names as column headings. 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

Is there another way to get this than with cross tab queries?

I have been thinking of how to populate an array, but did not grasp how
to
present the array in a form.

Or is there a better way?

PS
Please forgive me for a kind of crossposting, as I have asked in the
query
department first and now no longer think that the cross tab can present
the
data as text, only numbers. So I am prone to give up that idea.

/ Rolf

 

[Guest]

That is right, I have both full hours and half hours.
I created a dummy table, with all the times and a query that showed all of
them when run separately. After that I put this new query into my cross tab
query, but I still got only the times where there are corresponding
customers. I also played with all the 3 types of connection between the
tables. Couln't get it work.

Query1: (Fråga1 in swedish)
SELECT dummy.dummyTime, Time.Time
FROM [Time] INNER JOIN dummy ON Time.TimeID = dummy.dummyTime;

Query2:
TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Time.Time
FROM Fråga1, [Time] INNER JOIN Booking ON Time.TimeID = Booking.TimeID
WHERE (((Booking.Day)=#10/19/2006#))
GROUP BY Booking.Day, Time.Time
ORDER BY Booking.Staff
PIVOT Booking.Staff;

Can you see what I have been missing, to get all the time rows into the
result?
/ Rolf

I see half-hours, and this is still not the queries group ->)
I would think this is possible, using a query as input with a join to a
little table containing all of the times you want to see.

Thanks, it works just beautiful as you say. Now also with my tables! There
is
now just one more wish. The Time field shows the clock time for the
bookings.
And I have every hour of the day as records in a seperate time-table.

Now, in the query only those rows that have a record of customer's name,
and
of course a number reference to the time table, show up. I wanted all the
hours for every day to be shown as a row, even if there is no customer
specified for all the hours.

Is that also possible to do?
/ Rolf

This is a query problem, the multiple posting does not help the
functioning
of these groups.

A Crosstab query can give you the result you want. I just cannot tell you
what it is because I do not have enough information about your problem.
This little test query of mine works beautifully.

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT Table1.pets
FROM Table1
GROUP BY Table1.pets
PIVOT "shift" & [month1];


message I am trying to display my data in an overview per day, but cannot come
up
with a method that seems to work. First I tried with a cross tab query
and
got the design to work, but the data seems not to be what I want. My
problem
is that I need text data in the columns, not numbers that the cross tab
is
doing.

My goal is a day schematic with time and customer's name on the rows
and
the
staff names as column headings. 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

Is there another way to get this than with cross tab queries?

I have been thinking of how to populate an array, but did not grasp how
to
present the array in a form.

Or is there a better way?

PS
Please forgive me for a kind of crossposting, as I have asked in the
query
department first and now no longer think that the cross tab can present
the
data as text, only numbers. So I am prone to give up that idea.

/ Rolf

 

[David F Cox]

You need a left join. My guess is that the field (desired times) in the
lookup table should be TEXT and that the time in the other table should be
formatted as "hh:mm".

FWIW this works:

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT [T_allpets].Field1, Table1.pets
FROM T_allpets LEFT JOIN Table1 ON [T_allpets].Field1=Table1.pets
GROUP BY [T_allpets].Field1, Table1.pets
PIVOT "shift" & [month1];

That is right, I have both full hours and half hours.
I created a dummy table, with all the times and a query that showed all of
them when run separately. After that I put this new query into my cross
tab
query, but I still got only the times where there are corresponding
customers. I also played with all the 3 types of connection between the
tables. Couln't get it work.

Query1: (Fråga1 in swedish)
SELECT dummy.dummyTime, Time.Time
FROM [Time] INNER JOIN dummy ON Time.TimeID = dummy.dummyTime;

Query2:
TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Time.Time
FROM Fråga1, [Time] INNER JOIN Booking ON Time.TimeID = Booking.TimeID
WHERE (((Booking.Day)=#10/19/2006#))
GROUP BY Booking.Day, Time.Time
ORDER BY Booking.Staff
PIVOT Booking.Staff;

Can you see what I have been missing, to get all the time rows into the
result?
/ Rolf

I see half-hours, and this is still not the queries group ->)
I would think this is possible, using a query as input with a join to a
little table containing all of the times you want to see.

Thanks, it works just beautiful as you say. Now also with my tables!
There
is
now just one more wish. The Time field shows the clock time for the
bookings.
And I have every hour of the day as records in a seperate time-table.

Now, in the query only those rows that have a record of customer's
name,
and
of course a number reference to the time table, show up. I wanted all
the
hours for every day to be shown as a row, even if there is no customer
specified for all the hours.

Is that also possible to do?
/ Rolf



:

This is a query problem, the multiple posting does not help the
functioning
of these groups.

A Crosstab query can give you the result you want. I just cannot tell
you
what it is because I do not have enough information about your
problem.
This little test query of mine works beautifully.

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT Table1.pets
FROM Table1
GROUP BY Table1.pets
PIVOT "shift" & [month1];


message I am trying to display my data in an overview per day, but cannot
come
up
with a method that seems to work. First I tried with a cross tab
query
and
got the design to work, but the data seems not to be what I want. My
problem
is that I need text data in the columns, not numbers that the cross
tab
is
doing.

My goal is a day schematic with time and customer's name on the rows
and
the
staff names as column headings. 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

Is there another way to get this than with cross tab queries?

I have been thinking of how to populate an array, but did not grasp
how
to
present the array in a form.

Or is there a better way?

PS
Please forgive me for a kind of crossposting, as I have asked in the
query
department first and now no longer think that the cross tab can
present
the
data as text, only numbers. So I am prone to give up that idea.

/ Rolf

 

[Guest]

Yes, you are quite right about the LEFT JOIN. Now I have got it working, but
there was also another thing that stopped here. And that was the WHERE with
the specified date. Without date all the times came as I wanted, but with the
date, only those records with a customer names were shown.

After that I changed the WHERE to Null OR the wanted date. Then it worked!

It looks like this:
TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Time.Time
FROM ([Time] LEFT JOIN Booking ON Time.TimeID = Booking.TimeID) RIGHT JOIN
dummy ON Time.TimeID = dummy.dummyTime
WHERE (((Booking.Day) Is Null Or (Booking.Day)=#10/19/2006#))
GROUP BY Booking.Day, Time.Time, dummy.dummyTime
ORDER BY Time.Time, Booking.Staff
PIVOT Booking.Staff;

Dummy is the dummy table with one record for each desired time.
Many, many thanks for your patience and good ideas!
/ Rolf

You need a left join. My guess is that the field (desired times) in the
lookup table should be TEXT and that the time in the other table should be
formatted as "hh:mm".

FWIW this works:

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT [T_allpets].Field1, Table1.pets
FROM T_allpets LEFT JOIN Table1 ON [T_allpets].Field1=Table1.pets
GROUP BY [T_allpets].Field1, Table1.pets
PIVOT "shift" & [month1];

That is right, I have both full hours and half hours.
I created a dummy table, with all the times and a query that showed all of
them when run separately. After that I put this new query into my cross
tab
query, but I still got only the times where there are corresponding
customers. I also played with all the 3 types of connection between the
tables. Couln't get it work.

Query1: (Fråga1 in swedish)
SELECT dummy.dummyTime, Time.Time
FROM [Time] INNER JOIN dummy ON Time.TimeID = dummy.dummyTime;

Query2:
TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Time.Time
FROM Fråga1, [Time] INNER JOIN Booking ON Time.TimeID = Booking.TimeID
WHERE (((Booking.Day)=#10/19/2006#))
GROUP BY Booking.Day, Time.Time
ORDER BY Booking.Staff
PIVOT Booking.Staff;

Can you see what I have been missing, to get all the time rows into the
result?
/ Rolf

I see half-hours, and this is still not the queries group ->)
I would think this is possible, using a query as input with a join to a
little table containing all of the times you want to see.

message Thanks, it works just beautiful as you say. Now also with my tables!
There
is
now just one more wish. The Time field shows the clock time for the
bookings.
And I have every hour of the day as records in a seperate time-table.

Now, in the query only those rows that have a record of customer's
name,
and
of course a number reference to the time table, show up. I wanted all
the
hours for every day to be shown as a row, even if there is no customer
specified for all the hours.

Is that also possible to do?
/ Rolf



:

This is a query problem, the multiple posting does not help the
functioning
of these groups.

A Crosstab query can give you the result you want. I just cannot tell
you
what it is because I do not have enough information about your
problem.
This little test query of mine works beautifully.

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT Table1.pets
FROM Table1
GROUP BY Table1.pets
PIVOT "shift" & [month1];


message I am trying to display my data in an overview per day, but cannot
come
up
with a method that seems to work. First I tried with a cross tab
query
and
got the design to work, but the data seems not to be what I want. My
problem
is that I need text data in the columns, not numbers that the cross
tab
is
doing.

My goal is a day schematic with time and customer's name on the rows
and
the
staff names as column headings. 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

Is there another way to get this than with cross tab queries?

I have been thinking of how to populate an array, but did not grasp
how
to
present the array in a form.

Or is there a better way?

PS
Please forgive me for a kind of crossposting, as I have asked in the
query
department first and now no longer think that the cross tab can
present
the
data as text, only numbers. So I am prone to give up that idea.

/ Rolf

 

[David F Cox]

Well done.

Yes, you are quite right about the LEFT JOIN. Now I have got it working,
but
there was also another thing that stopped here. And that was the WHERE
with
the specified date. Without date all the times came as I wanted, but with
the
date, only those records with a customer names were shown.

After that I changed the WHERE to Null OR the wanted date. Then it worked!

It looks like this:
TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Time.Time
FROM ([Time] LEFT JOIN Booking ON Time.TimeID = Booking.TimeID) RIGHT JOIN
dummy ON Time.TimeID = dummy.dummyTime
WHERE (((Booking.Day) Is Null Or (Booking.Day)=#10/19/2006#))
GROUP BY Booking.Day, Time.Time, dummy.dummyTime
ORDER BY Time.Time, Booking.Staff
PIVOT Booking.Staff;

Dummy is the dummy table with one record for each desired time.
Many, many thanks for your patience and good ideas!
/ Rolf

You need a left join. My guess is that the field (desired times) in the
lookup table should be TEXT and that the time in the other table should
be
formatted as "hh:mm".

FWIW this works:

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT [T_allpets].Field1, Table1.pets
FROM T_allpets LEFT JOIN Table1 ON [T_allpets].Field1=Table1.pets
GROUP BY [T_allpets].Field1, Table1.pets
PIVOT "shift" & [month1];

That is right, I have both full hours and half hours.
I created a dummy table, with all the times and a query that showed all
of
them when run separately. After that I put this new query into my cross
tab
query, but I still got only the times where there are corresponding
customers. I also played with all the 3 types of connection between the
tables. Couln't get it work.

Query1: (Fråga1 in swedish)
SELECT dummy.dummyTime, Time.Time
FROM [Time] INNER JOIN dummy ON Time.TimeID = dummy.dummyTime;

Query2:
TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Time.Time
FROM Fråga1, [Time] INNER JOIN Booking ON Time.TimeID = Booking.TimeID
WHERE (((Booking.Day)=#10/19/2006#))
GROUP BY Booking.Day, Time.Time
ORDER BY Booking.Staff
PIVOT Booking.Staff;

Can you see what I have been missing, to get all the time rows into the
result?
/ Rolf



:

I see half-hours, and this is still not the queries group ->)
I would think this is possible, using a query as input with a join to
a
little table containing all of the times you want to see.

message Thanks, it works just beautiful as you say. Now also with my tables!
There
is
now just one more wish. The Time field shows the clock time for the
bookings.
And I have every hour of the day as records in a seperate
time-table.

Now, in the query only those rows that have a record of customer's
name,
and
of course a number reference to the time table, show up. I wanted
all
the
hours for every day to be shown as a row, even if there is no
customer
specified for all the hours.

Is that also possible to do?
/ Rolf



:

This is a query problem, the multiple posting does not help the
functioning
of these groups.

A Crosstab query can give you the result you want. I just cannot
tell
you
what it is because I do not have enough information about your
problem.
This little test query of mine works beautifully.

TRANSFORM First(Table1.name1) AS FirstOfname1
SELECT Table1.pets
FROM Table1
GROUP BY Table1.pets
PIVOT "shift" & [month1];


in
message I am trying to display my data in an overview per day, but cannot
come
up
with a method that seems to work. First I tried with a cross tab
query
and
got the design to work, but the data seems not to be what I want.
My
problem
is that I need text data in the columns, not numbers that the
cross
tab
is
doing.

My goal is a day schematic with time and customer's name on the
rows
and
the
staff names as column headings. 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

Is there another way to get this than with cross tab queries?

I have been thinking of how to populate an array, but did not
grasp
how
to
present the array in a form.

Or is there a better way?

PS
Please forgive me for a kind of crossposting, as I have asked in
the
query
department first and now no longer think that the cross tab can
present
the
data as text, only numbers. So I am prone to give up that idea.

/ Rolf