Combobox to crosstab style query

[Mr T]

Hi,
I'm trying to create a query which will return records
based on 3 comboboxes.

I have 3 tables, one for a persons details, one for
certificates, and one which combines them to record the
certificate a person has.

I had solved my problem (with guidance) using a crosstab
query based on the certificate list, but only on sample
data - then the problem arose that there can be many many
more certificate names than columns which are allowed. IE
3000+

Is it possible to run a crosstab style query based on the
values selected in comboboxes?

Such as:
combo1 = MCAD
combo2 = MCDBA
combo3 = MCSE

returning something like:
Name MCAD MCDBA MCSE
BOB MCAD MCSE
JULIE MCSE
FRANK MCDBA

Any help or advice to point me in the right direction
would be appreciated.

Thanks in advance
T

 

[Duane Hookom]

I think you would need to modify the SQL of the query and place the values
of the combo boxes in the "In (....)" clause. If you always make three
selections, you can set the criteria under the certificate column to "Where"
and three criteria lines like:
Forms!frmA!combo1
Forms!frmA!combo2
Forms!frmA!combo3

 

[Mr T]

Thanks for the reply, I think I was trying to make it more
complicated than it needs to be, your clarity of my intent
was refreshing!!

I haven't used crosstabs before and am now getting a
reference error - jet database does not recognise
[Forms]![Main]![Cert] as a valid field name or expression.

I think I saw some things about this posted before - will
look and then should be all up and running!

Again thanks for your input Duane.

T

 

[Duane Hookom]

Sorry, I should have added that any reference to a control on a form or a
parameter prompt must be entered into the query parameters. Select
Query|Parameters and enter:
[Forms]![Main]![Cert] Text (or number)

--
Duane Hookom
MS Access MVP

Thanks for the reply, I think I was trying to make it more
complicated than it needs to be, your clarity of my intent
was refreshing!!

I haven't used crosstabs before and am now getting a
reference error - jet database does not recognise
[Forms]![Main]![Cert] as a valid field name or expression.

I think I saw some things about this posted before - will
look and then should be all up and running!

Again thanks for your input Duane.

T

-----Original Message-----
I think you would need to modify the SQL of the query and place the values
of the combo boxes in the "In (....)" clause. If you always make three
selections, you can set the criteria under the certificate column to "Where"
and three criteria lines like:
Forms!frmA!combo1
Forms!frmA!combo2
Forms!frmA!combo3

--
Duane Hookom
Microsoft Access MVP





.

 

[Guest]

Ahaaa!
That's got it!
Let me thank you again Duane, your help has saved my
scalp! (What's left of it..)

Works a treat!

-----Original Message-----
Sorry, I should have added that any reference to a control on a form or a
parameter prompt must be entered into the query parameters. Select
Query|Parameters and enter:
[Forms]![Main]![Cert] Text (or number)

--
Duane Hookom
MS Access MVP

Thanks for the reply, I think I was trying to make it more
complicated than it needs to be, your clarity of my intent
was refreshing!!

I haven't used crosstabs before and am now getting a
reference error - jet database does not recognise
[Forms]![Main]![Cert] as a valid field name or expression.

I think I saw some things about this posted before - will
look and then should be all up and running!

Again thanks for your input Duane.

T

-----Original Message-----
I think you would need to modify the SQL of the query

and
place the values

of the combo boxes in the "In (....)" clause. If you always make three
selections, you can set the criteria under the certificate column to "Where"
and three criteria lines like:
Forms!frmA!combo1
Forms!frmA!combo2
Forms!frmA!combo3

--
Duane Hookom
Microsoft Access MVP


Hi,
I'm trying to create a query which will return records
based on 3 comboboxes.

I have 3 tables, one for a persons details, one for
certificates, and one which combines them to record the
certificate a person has.

I had solved my problem (with guidance) using a crosstab
query based on the certificate list, but only on sample
data - then the problem arose that there can be many many
more certificate names than columns which are

allowed.
IE

3000+

Is it possible to run a crosstab style query based on the
values selected in comboboxes?

Such as:
combo1 = MCAD
combo2 = MCDBA
combo3 = MCSE

returning something like:
Name MCAD MCDBA MCSE
BOB MCAD MCSE
JULIE MCSE
FRANK MCDBA

Any help or advice to point me in the right direction
would be appreciated.

Thanks in advance
T


.

.