2nd column in cross table query

[khartoum]

Is there a way to add a second column to a cross table query so it can show for example a name and a type in the table;

i.e. I have staffnames across columns, times down rows and client name in table but need a second column to display next to client name what their appt is for


regards kh

[June7]

Don't know enough about data structure but possibly could have both the client name and appt fields as row headers.

[khartoum]

Hi June here is what I have: qrytoday;
TRANSFORM First(qrysubtoday.clientname) AS FirstOfclientname
SELECT tblTimes.Times
FROM tblTimes LEFT JOIN qrysubtoday ON tblTimes.Times = qrysubtoday.Time
GROUP BY tblTimes.Times
PIVOT qrysubtoday.stylist;

qrysubtoday:
SELECT tblappts.Time, tblappts.ClientName, tblappts.HavingDone, tblappts.Stylist, tblappts.ApptDate
FROM tblappts
WHERE (((tblappts.ApptDate)=Date()));

so just want to add a second column beside each stylist for appt type
hope this helps

[June7]

Crosstab allows only one field to be transposed to columns.

By having the clientname and appttype as row headers, will get a record for each clientname/type pair. To get the type as column will need to do two crosstab queries then join them.

[khartoum]

Hi June7, am struggling with this; if i have my first query (qrysubtoday) for todays appts:
SELECT tblappts.Time, tblappts.ClientName, tblappts.HavingDone, tblappts.Stylist, tblappts.ApptDate
FROM tblappts
WHERE (((tblappts.ApptDate)=Date()));

then I add my times table:
TRANSFORM First(qrysubtoday.clientname) AS FirstOfclientname
SELECT tblTimes.Times
FROM tblTimes LEFT JOIN qrysubtoday ON tblTimes.Times = qrysubtoday.Time
GROUP BY tblTimes.Times
PIVOT qrysubtoday.stylist;

how do i go about getting the query to show the two columns by producing a new query using two crosstable queries

I want it to appear like this:
Stylist1 HavingDone Stylist2 HavingDone Stylist3 HavingDone
Time 08:00 Betty Style1 Mary Style2 Joe Style3
08:15
08:30 Mary Style6 etc...
Can i do this?

[June7]

Do two crosstabs. One will pivot Stylists and one will pivot the HavingDone. The appointment date/time will be the RowHeader on each. Join the two crosstabs on the date/time fields of each. However, this won't indicate which HavingDone goes with which Stylist.

[khartoum]

Sorry June, could not grasp how i do this, and actually whether it would display as i want it to;
would it appear as below and if so, could you give me an example of the query structure as when i
tried to add two crosstab queries to the grid, it did not display all the fields
StylistName1 HavingDone StylistName2 HavingDone
Time
08:00 Client1 StyleZ Client2 StyleB
08:30 Client3 StyleX

or am i confused - sorry for my misunderstanding

[June7]

Always helps to work with data. What I was picturing before is not quite what I could actually do. Revises my statement about knowing which style went with which stylist.

Assume a table/query Appointments like:

ID	Appt	Stylist	Client	Style
1	7/4/2012 10:15:00 AM	A	R	V
2	7/4/2012 11:00:00 AM	A	W	M
3	7/4/2012 11:00:00 AM	B	Y	J

query StylistClient
TRANSFORM First(Appointments.Client) AS FirstOfClient
SELECT Appointments.Appt
FROM Appointments
GROUP BY Appointments.Appt
PIVOT Appointments.Stylist;

query StylistStyle
TRANSFORM First(Appointments.Style) AS FirstOfStyle
SELECT Appointments.Appt
FROM Appointments
GROUP BY Appointments.Appt
PIVOT Appointments.Stylist;

query join the crosstabs
SELECT StylistClient.Appt, StylistClient.A, StylistClient.B, StylistStyle.A, StylistStyle.B
FROM StylistStyle INNER JOIN StylistClient ON StylistStyle.Appt = StylistClient.Appt;

The resulting output is:

Appt	StylistClient.A	StylistClient.B	StylistStyle.A	StylistStyle.B
7/4/2012 10:15:00 AM	R		V
7/4/2012 11:00:00 AM	W	Y	M	J