Crosstab Query Auto-Create????

[leicklda]

I have 2 tables: "Jobs" table contains all the job numbers. "Options" table contains all possible options that are available with various jobs.

I need to do two things.

1. Match up every option with every job. I had luck with this step by creating a select Query with GroupBy totals on both the JobNumber and OptionDesc. This matched up every job with every option. But I had nowhere to set the value to yes or no (see next step).

2. Set a yes or no value for each option-jobnumber matchup. The crosstab query looks like exactly what I want, but where do I store the yes/no value? I don't really have a field for it in any table, because pairing of the two fields from the different tables is something that is done automatically.

I hope this makes sense. Any help would be greatly appreciated.

Darleen

 

[r937]

you'll need to create a third table, JobOptions

primary key is JobID plus OptionID, with each of those as a separate foreign key referencing the appropriate table

third column is YesNo, a one character or small integer or whatever you want datatype

then step 1 --

insert into JobOptions
select JobID, Options, 0
from Jobs, Options

the select is a "cross join" and will create one row for every possible combination

now step 2 --

go into JobOptions and set YesNo to 1 for every combination that needs it

when you create your crosstab query, join JobOptions into the mix, and make YesNo determine whether that combination is included in the crosstab

post your crosstab query if this is not clear

rudy

http://rudy.ca/

 

[leicklda]

Thanks for responding

I made the third table - JobOptions with three fields. JobID looks up a value from Jobs table and OptionID looks up a value from Options table. Then there is a third field for Value.

You said "insert into JobOptions - select JobID, Options, ) - from Jobs, Options"

Where do I do this? Is this a new query I'm making? Like an append query to put the combinations into the JobOptions table? I'm a little lost here

Thanks

 

[leicklda]

I think the select statement is SQL that should be put somewhere that you wrote. Does it go into a query? I've tried lots of different ways to do this, still not working.

Thanks

 

[r937]

yes, just open a new query, sql view (not design view), and paste in

insert into JobOptions
select JobID, Options, 0
from Jobs, Options

i guess access calls that an append query

it will insert a zero into your Value column