Multiple Table Query

[Guest]

I am asking two tables to provide fields data into a new table. Both are
autonumber fields. The resultant table cannot have more than one autonumber
field. Does anyone have a solution?

Thanks, Bill

 

[Rick B]

What are you trying to accomplish? I can see no valid reason to take two
tables and combine the autonumber fields, then place them in a new table.
It is redundant.

Also, in the new table, they would not be auto-number fields, they'd just be
number fields. If you are pasting values into a new table, you would not
need that table to "auto number" the field.

For more help, tell us what you are trying to accomplish and why.

 

[Guest]

Hi Bill,

I haven't had a need to do this before, but I would think that you may be
able to get what you want if you substitute an expression such as
Clng([YourIDField]) as the field source for one or both of the fields in your
append query rather than just the field name itself.

Post back if this doesn't work and I'll look for other ways to do it.

-Ted

 

[Guest]

Thanks guys, I'm sorry I wasn't clear the first time.
I have three tables Frame, Layer and File
When a user creates a Frame. In the Frame table it is given a unique FrameID
(autonumber)
Next they create a Layer. In the Layer table it is given a unique LayerID
(autonumber) and associates it with the FrameID created in the Frame table.
Finally they create a File (a gfx or swf) and it is given a unique FileID
(autonumber) associated to the LayerID in the Layer table

Frame.FrameID's have been deleted but not the associated Layer.FrameID,
Layer.LayerID or File.FileID's.

I am trying to create a make-table query to identify orphan Layer.FrameID,
Layer.LayerID and File.FileID's.

Here's what I'm doing......

1st Query
SELECT [Layer].[FrameID], [Layer].[LayerID], [Frame].[TitleText] INTO
FrameExtra
FROM Layer LEFT JOIN Frame ON [Layer].[FrameID]=[Frame].[FrameID]
WHERE ([Frame].[FrameID] Is Null);

This works fine

Next a make-table query to capture the FileID's

2nd Query
SELECT [File].[FileID], [File].[LayerID], [File].[Type] INTO FileID_temp
FROM File;

This works fine

Finally a make-table to ID the orphan Frame.FrameID, Layer.LayerID and
File.FileID

3rd Query
SELECT [FrameExtra].[LayerID], [FileID_temp].[FileID] INTO Clean_final
FROM FrameExtra LEFT JOIN FileID_temp ON
[FrameExtra].[LayerID]=[FileID_temp].[LayerID];

Here lies my problem... I receive an error stating "Resultant table not
allowed to have more than on Autonumber field."

I do appreciate your time....thanks

Hi Bill,

I haven't had a need to do this before, but I would think that you may be
able to get what you want if you substitute an expression such as
Clng([YourIDField]) as the field source for one or both of the fields in your
append query rather than just the field name itself.

Post back if this doesn't work and I'll look for other ways to do it.

-Ted

I am asking two tables to provide fields data into a new table. Both are
autonumber fields. The resultant table cannot have more than one autonumber
field. Does anyone have a solution?

Thanks, Bill

 

[Guest]

Hi Bill,

This seems like it may have been the long way to do this, you probably could
have just run individual delete queries on the tables containing the orphans
and just deleted all records where there is no match in the Frame table.
But, it seems like you are most of the way through your method, so I'll offer
the following:

It appears that the first two tables that you have generated, and the third
that you want to generate, are temporary tables. Also, it seems that the
first two make table queries set the field type of the respective ID fields
to Autonumber because that is what the original table has. The third
make-table query is trying to do the same, but there are two autonumber
fields which it can't do. What I would do is go into design view of each of
your first two temporary tables and change the ID fields from Autonumber to
Number (Long Integer). Then, you should be able to run your third make table
query (since it will no longer try to create two autonumber fields).

Incidentally, I did not review all of the logic of your queryies, but I will
give the standard recommendation to back up the database prior to running any
delete queries or deleting any tables just in case they don't work as
intended.

HTH, Ted Allen

Thanks guys, I'm sorry I wasn't clear the first time.
I have three tables Frame, Layer and File
When a user creates a Frame. In the Frame table it is given a unique FrameID
(autonumber)
Next they create a Layer. In the Layer table it is given a unique LayerID
(autonumber) and associates it with the FrameID created in the Frame table.
Finally they create a File (a gfx or swf) and it is given a unique FileID
(autonumber) associated to the LayerID in the Layer table

Frame.FrameID's have been deleted but not the associated Layer.FrameID,
Layer.LayerID or File.FileID's.

I am trying to create a make-table query to identify orphan Layer.FrameID,
Layer.LayerID and File.FileID's.

Here's what I'm doing......

1st Query
SELECT [Layer].[FrameID], [Layer].[LayerID], [Frame].[TitleText] INTO
FrameExtra
FROM Layer LEFT JOIN Frame ON [Layer].[FrameID]=[Frame].[FrameID]
WHERE ([Frame].[FrameID] Is Null);

This works fine

Next a make-table query to capture the FileID's

2nd Query
SELECT [File].[FileID], [File].[LayerID], [File].[Type] INTO FileID_temp
FROM File;

This works fine

Finally a make-table to ID the orphan Frame.FrameID, Layer.LayerID and
File.FileID

3rd Query
SELECT [FrameExtra].[LayerID], [FileID_temp].[FileID] INTO Clean_final
FROM FrameExtra LEFT JOIN FileID_temp ON
[FrameExtra].[LayerID]=[FileID_temp].[LayerID];

Here lies my problem... I receive an error stating "Resultant table not
allowed to have more than on Autonumber field."

I do appreciate your time....thanks

Hi Bill,

I haven't had a need to do this before, but I would think that you may be
able to get what you want if you substitute an expression such as
Clng([YourIDField]) as the field source for one or both of the fields in your
append query rather than just the field name itself.

Post back if this doesn't work and I'll look for other ways to do it.

-Ted

I am asking two tables to provide fields data into a new table. Both are
autonumber fields. The resultant table cannot have more than one autonumber
field. Does anyone have a solution?

Thanks, Bill

 

[Guest]

Thanks Ted.... Because these are make-table queries it deletes the existing
table before running the query (Iwould like to reuse these queries in case it
happens again) so each time it inherits the autonumber property.

I wanted to give the person performing this action the ability to review the
results before deleting but if it is easier I could query and delete in the
same action.

Hi Bill,

This seems like it may have been the long way to do this, you probably could
have just run individual delete queries on the tables containing the orphans
and just deleted all records where there is no match in the Frame table.
But, it seems like you are most of the way through your method, so I'll offer
the following:

It appears that the first two tables that you have generated, and the third
that you want to generate, are temporary tables. Also, it seems that the
first two make table queries set the field type of the respective ID fields
to Autonumber because that is what the original table has. The third
make-table query is trying to do the same, but there are two autonumber
fields which it can't do. What I would do is go into design view of each of
your first two temporary tables and change the ID fields from Autonumber to
Number (Long Integer). Then, you should be able to run your third make table
query (since it will no longer try to create two autonumber fields).

Incidentally, I did not review all of the logic of your queryies, but I will
give the standard recommendation to back up the database prior to running any
delete queries or deleting any tables just in case they don't work as
intended.

HTH, Ted Allen

Thanks guys, I'm sorry I wasn't clear the first time.
I have three tables Frame, Layer and File
When a user creates a Frame. In the Frame table it is given a unique FrameID
(autonumber)
Next they create a Layer. In the Layer table it is given a unique LayerID
(autonumber) and associates it with the FrameID created in the Frame table.
Finally they create a File (a gfx or swf) and it is given a unique FileID
(autonumber) associated to the LayerID in the Layer table

Frame.FrameID's have been deleted but not the associated Layer.FrameID,
Layer.LayerID or File.FileID's.

I am trying to create a make-table query to identify orphan Layer.FrameID,
Layer.LayerID and File.FileID's.

Here's what I'm doing......

1st Query
SELECT [Layer].[FrameID], [Layer].[LayerID], [Frame].[TitleText] INTO
FrameExtra
FROM Layer LEFT JOIN Frame ON [Layer].[FrameID]=[Frame].[FrameID]
WHERE ([Frame].[FrameID] Is Null);

This works fine

Next a make-table query to capture the FileID's

2nd Query
SELECT [File].[FileID], [File].[LayerID], [File].[Type] INTO FileID_temp
FROM File;

This works fine

Finally a make-table to ID the orphan Frame.FrameID, Layer.LayerID and
File.FileID

3rd Query
SELECT [FrameExtra].[LayerID], [FileID_temp].[FileID] INTO Clean_final
FROM FrameExtra LEFT JOIN FileID_temp ON
[FrameExtra].[LayerID]=[FileID_temp].[LayerID];

Here lies my problem... I receive an error stating "Resultant table not
allowed to have more than on Autonumber field."

I do appreciate your time....thanks

Hi Bill,

I haven't had a need to do this before, but I would think that you may be
able to get what you want if you substitute an expression such as
Clng([YourIDField]) as the field source for one or both of the fields in your
append query rather than just the field name itself.

Post back if this doesn't work and I'll look for other ways to do it.

-Ted

:

I am asking two tables to provide fields data into a new table. Both are
autonumber fields. The resultant table cannot have more than one autonumber
field. Does anyone have a solution?

Thanks, Bill

 

[Guest]

Hi Bill,

I was assuming that you were just doing a one-time cleanup of data. This
definitely wouldn't be the best way to plan on handling this in the future.
For one thing, it is rarely a good idea to run a make table query to replace
one of your database tables because you lose all of the custom
settings/properties in the table (indexes, validation, etc). In addition, if
the database relationships are properly defined with referential integrity,
you shouldn't be encountering orphans at all in the future.

I'm not sure if you are familiar with how to set relationships and
referential integrity. If you aren't, you definitely want to learn because
it is one of the most important pieces to setting up a database. In this
case, once you get rid of the existing orphans, I would go immediately to the
relationships window and define the relationships between the table (by
dragging lines to connect the primary keys to the foreign keys in the related
tables). Then, double click the relationship lines so that you can turn on
referential integrity, and probably also check cascade updates and deletes.

Cascading deletes will delete a record in the related table if a record in
the main table is deleted. Referential integrity will prevent orphans in any
case. If you cascade deletes, the related record will automatically be
deleted, if you don't, Access will not let you delete a record from the main
table until the related records are manually deleted.

Now, back to your current problem, I'm not sure if you ever tried the Clng()
function with your third query as I originally mentioned to try to avoid the
creation of the autonumber field in the third table, but that may work. It
sounds like a delete query may also work.

Post back with your current status of your tables, and what you have
remaining to accomplish. If you just need to delete some records from the
third table based on the records in the other two tables, I'm sure that can
be accomplished easily with a delete query.

-Ted Allen

Thanks Ted.... Because these are make-table queries it deletes the existing
table before running the query (Iwould like to reuse these queries in case it
happens again) so each time it inherits the autonumber property.

I wanted to give the person performing this action the ability to review the
results before deleting but if it is easier I could query and delete in the
same action.

Hi Bill,

This seems like it may have been the long way to do this, you probably could
have just run individual delete queries on the tables containing the orphans
and just deleted all records where there is no match in the Frame table.
But, it seems like you are most of the way through your method, so I'll offer
the following:

It appears that the first two tables that you have generated, and the third
that you want to generate, are temporary tables. Also, it seems that the
first two make table queries set the field type of the respective ID fields
to Autonumber because that is what the original table has. The third
make-table query is trying to do the same, but there are two autonumber
fields which it can't do. What I would do is go into design view of each of
your first two temporary tables and change the ID fields from Autonumber to
Number (Long Integer). Then, you should be able to run your third make table
query (since it will no longer try to create two autonumber fields).

Incidentally, I did not review all of the logic of your queryies, but I will
give the standard recommendation to back up the database prior to running any
delete queries or deleting any tables just in case they don't work as
intended.

HTH, Ted Allen

Thanks guys, I'm sorry I wasn't clear the first time.
I have three tables Frame, Layer and File
When a user creates a Frame. In the Frame table it is given a unique FrameID
(autonumber)
Next they create a Layer. In the Layer table it is given a unique LayerID
(autonumber) and associates it with the FrameID created in the Frame table.
Finally they create a File (a gfx or swf) and it is given a unique FileID
(autonumber) associated to the LayerID in the Layer table

Frame.FrameID's have been deleted but not the associated Layer.FrameID,
Layer.LayerID or File.FileID's.

I am trying to create a make-table query to identify orphan Layer.FrameID,
Layer.LayerID and File.FileID's.

Here's what I'm doing......

1st Query
SELECT [Layer].[FrameID], [Layer].[LayerID], [Frame].[TitleText] INTO
FrameExtra
FROM Layer LEFT JOIN Frame ON [Layer].[FrameID]=[Frame].[FrameID]
WHERE ([Frame].[FrameID] Is Null);

This works fine

Next a make-table query to capture the FileID's

2nd Query
SELECT [File].[FileID], [File].[LayerID], [File].[Type] INTO FileID_temp
FROM File;

This works fine

Finally a make-table to ID the orphan Frame.FrameID, Layer.LayerID and
File.FileID

3rd Query
SELECT [FrameExtra].[LayerID], [FileID_temp].[FileID] INTO Clean_final
FROM FrameExtra LEFT JOIN FileID_temp ON
[FrameExtra].[LayerID]=[FileID_temp].[LayerID];

Here lies my problem... I receive an error stating "Resultant table not
allowed to have more than on Autonumber field."

I do appreciate your time....thanks

:

Hi Bill,

I haven't had a need to do this before, but I would think that you may be
able to get what you want if you substitute an expression such as
Clng([YourIDField]) as the field source for one or both of the fields in your
append query rather than just the field name itself.

Post back if this doesn't work and I'll look for other ways to do it.

-Ted

:

I am asking two tables to provide fields data into a new table. Both are
autonumber fields. The resultant table cannot have more than one autonumber
field. Does anyone have a solution?

Thanks, Bill