SQL v Crosstab

[ZOR]

I am presently using a listbox with a crosstab query to display appointments log of companies. The listbox looks like the following:

CompanyName ! Wk1 ! Wk2 ! Wk3 ! etc - Heading
Microsoft Bill Gates Frank Yo etc - Rows

My problem with the crosstab/listbox is as appointments extend into further week number columns the listbox will not
grow. I have to keep replacing the listbox and using the wizard, as for some reason if I select the query as the recordource for the list it leaves out records. If I run the listbox query on its own all records are there. The listbox seems to not like the crosstab query.

Is there an SQL way to create the above. I know the listbox will cough out when too many columns are generated, but I can filter so many weeks before and after to reduce the number of columns.

I am presently using two queries, the first being to supply the crosstab. It looks like this.

SELECT Companies.Company, Contacts.Name, Appts.WeekNo, Appts.Visited, Appts.ID3
FROM (Companies INNER JOIN Contacts ON Companies.ID1 = Contacts.ID1) INNER JOIN Appts ON Contacts.ID2 = Appts.ID2
ORDER BY Appts.WeekNo;

The crosstab querie is like this:

TRANSFORM Last(Master.Name) AS [The Value]
SELECT Master.Company
FROM Master
GROUP BY Master.Company, Master.ID3
PIVOT Master.WeekNo;

If it was for a report then it would look okay, but I want it in a listbox.

Hope I have explained it properley.

Many thanks

 

[dhookom]

What kind of values do you have in WeekNo?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Many thanks, its text in the tabls. Format$(Me.CAL1.Value, "ww") ' Week number, where CAL1 is a calendar control. Regards

 

[dhookom]

Consider creating your list box using info from faq703-5466. Change the month stuff to weeks.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

I guess you have to dynamically set the ColumnCount property of the ListBox.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZOR]

Thanks for the link. However I cannot seem to make sense of whats there, maybe it's me?.

What does this line mean? "Set the with menuing: Query|Parameter".

I will look at it again later to see if I can get a glimmer of
how it works etc. Best regards

 

[dhookom]

"Set the with menuing: Query|Parameters
simply means to find the menu in query design view and select "Query|Parameters"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]