Make Table Query

A

AJ

I am currently using a Make Table query for the below - but I was hoping
there is an easier way.

I have to download a report from our Central office which is in another an
excel file. (wp.xls) I then put this data into Access to run reports, etc on
it. The excel file doesn't link into Access with the correct formats so I
created a make table query which also formats the data.

The problem I am having is once you join this table to others to run reports
and forms you can no longer use the make table query. I get this message:
"you cannot delete the table because it is participating in one or more joins
or relationships."

Is there anyway to do this without using a Make Table query. I could use an
update query but the data we download is not always the same and then I would
need an append query too. It would be much easier to overwrite the data.
 
J

John W. Vinson

I am currently using a Make Table query for the below - but I was hoping
there is an easier way.

I have to download a report from our Central office which is in another an
excel file. (wp.xls) I then put this data into Access to run reports, etc on
it. The excel file doesn't link into Access with the correct formats so I
created a make table query which also formats the data.

Ummm?

Formatting is COMPLETELY INDEPENDENT of data storage.

You can link to the spreadsheet, and create a Report (for printing) and/or a
Form (for viewing), with controls bound to the fields in your linked table.
You can set the Format property of these controls however you like; you don't
need to have the table fields formatted.
The problem I am having is once you join this table to others to run reports
and forms you can no longer use the make table query. I get this message:
"you cannot delete the table because it is participating in one or more joins
or relationships."

Well, the error message says it all. In addition to running a make table query
you've created relationships to other tables (unnecessarily, you can create a
Query linking tables without any need to have relational integrity enforced).
You must delete those relationships before you can delete the table - select
the join line in the relationships window and delete it.
Is there anyway to do this without using a Make Table query. I could use an
update query but the data we download is not always the same and then I would
need an append query too. It would be much easier to overwrite the data.

An alternative would be to run a Delete query to empty the table, followed by
an Append query to repopulate it.

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top