Executing a make-table query using multiple primary keys

[NOTdbRanger]

Access 2003 Help files tell me this regarding a make-table query:
"Note The data in the new table you create does not inherit the field
properties or the primary key setting from the original table."

However, in Access 2007 I created a make-table query that picks up the
primary keys from two different tables and upon execution I get an error
message that says (paraphrased) "Cannot have multiple primary keys in the new
table".

Did '07 Access change? Is there a work-around--perhaps a property I can
disable?

 

[Jeff Boyce]

In many instances, you don't need to create a new table on the fly with a
Make Table query.

You've not described why you are using this approach, so folks here may not
know enough to offer specific suggestions.

As one alternative, you could create a table with the characteristics (keys,
fields, etc.) that could hold your data, then use a delete query to remove
all records and an append query (instead of make-table) to reload it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NOTdbRanger]

Jeff,
Didn't want to inflict this on you--but here we go.

I have a Students table with student data, a Courses table with course data,
and a Training table that records courses assigned to students with due dates
and completion dates. The database will be replicated for multiple sites.
Each site manager assigns the courses (industrial health and safety courses)
to their people based on the industrial applications at their
site--compressed gas, overhead crane, confined spaces, etc.

The site manager sets a REQUIRED flag (check box) in the Courses table to
make that course available to select from a combo box in the Training Data
Entry form connected to the Training table. There are over 100 courses
total, but each site may require only 15-20 courses for their people and I
want just this subset of courses to show up in the Training table.

The form for the Training table allows the user to sort through the students
in the top level form, then sort through and assign training courses in a sub
form. Therefore, the Training table needs a unique record for each REQUIRED
course for every student (I'm using CourseID and StudentID respectively).
This is where the make table query comes in.

When the REQUIRED flag is set for a course, here is my current (proposed)
data flow:
1. a Select Query picks up the CourseID with no students assigned
2. a seperate Select Query picks up the full set of StudentID's
3. the Make Table Query creates a table that associates every StudentID with
the new CourseID.
4. an Append Query then adds that table to the Training table so that now
the new course is visible in my Training subform.

I'll try what you suggested but am also open to an alternative design.
Thanks.

Steve

 

[Jeff Boyce]

If you need all students X new course, you can do this with a query (not a
Make Table query). Look up "Cartesian Product" ... this is when you add two
"tables" (or queries) to a new query but do not join them. You get back
every possible combination of records (kinda like "multiplying" the two
together).

You can then use that query (the Cartesian Product) as a source for a
subsequent query, without ever having to create a new table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NOTdbRanger]

Jeff,
Still working logic errors but the path you sent me down eliminated four of
the potential six queries I was using including the make-table query. I am
now using a select query to obtain the new CourseID followed by an append
query, using your cartesian product approach to get all my StudentIDs, which
then appends the new recordset directly into theTraining table. Very nice.

On a side note, I work between two offices one with Access 2007 and the
other with Access 2003. After building the db in Access '07 I took it with
me to the other office, opened it up in '03 to do some more work on it, and
without changing anything got plenty of execution errors. Is it not
recommended to "downgrade" your Access version once the db is built?

Steve

 

[david]

I avoid Cartesian Products at all costs. I deliberately put dummy
fields in my source queries (one: 1) so that I have dummy fields
to join (on one = one). The dummy fields have no logical effect.
Logically there is no difference between an ordinary Cartesian
Product, and a join on a field where all the values are identical
by definition.

I avoid the Cartesian Product because experience has taught me
that Cartesian Products cause crashes and selection failures in
complex JET SQL queries. From the beginning, JET was designed
to use the new Inner Join, Left Join, Right join syntax, and lots
of ( ). I don't think that the old Cartesian Product syntax (used
by old 'ANSI' database systems) ever got the same level of testing
and support.

Your mileage may vary.

 

[Jeff Boyce]

Thanks for the contrary view ... I had not run into that problem, so I'll
keep it in mind for future suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NOTdbRanger]

David,
Does the problem you've experienced with cartesian products result just from
complex queries (which I don't believe I have) or high numbers (1000's?) of
records? I don't expect the two tables I will be joining with a cartesian
product to ever have more than 100 or so records each.

 

[david]

Complex queries, not particularly large numbers although possibly 1000's
on each side, still, when you have complex queries and only reasonably large
numbers of records, the product can get quite large, so who knows?

I went through and added a potentially useful company index field to our
data tables and to our single-record company installation data table because
I thought it was a good idea: I can't remember that I ever actually had a
problem there.

(david)