import and append excel file to main table and sub table

[socasteel21 via AccessMonster.com]

I have a table called "tblClaim" that stores information such as customer and
machine information. There are 2 sub tables, one for parts replaced and one
for jobs performed. The parts sub table (tblPartPerClaim) has fields called
ClaimID, ITEM, and Quantity. The jobs performed sub table
(tblJobCodesPerClaim) has fields called ClaimID, JobCode, and Hours.
ClaimID is an autonumber and is the pk in each table. There is a one to many
relationship set up between tblClaim and tblPartPerClaim and between tblClaim
and tblJobCodesPerClaim.

I have created an Excel file with 3 tabs (one for each of the above mentioned
tables). I would like to be able to fill out the information in the Excel
file and import the information without interrupting the autonumber sequence.
I already have 2000+ claims in the database.

I have imported the first tab to tblClaim with success. It keeps with the
autonumber sequence, but I cannot figure out how to import the two sub tables
and keep the correct autonumber for each record.

I will importing to these tables constantly.

Any help would be greatly appreciated.

Shannan

 

[KARL DEWEY]

Use the ClaimID to do it for you. The ClaimID should be unique in tblClaim.
You can use it as your primary key and as foreign key in the other tables.

Another way still uses the ClaimID with your autonumber in tblClaim. Set
the autonumber as primary and a number field - integer - as foreign key in
the other tables. When you append to the other tables join the ClaimID field
from tblClaim to the fields in the other tables. Append the autonumber from
tblClaim as you append the Excel data.

 

[socasteel21 via AccessMonster.com]

Karl,

Thanks for your help.

The problem I am having is that I do not have the ClaimID field in the excel
file that I am importing. Therefore, I would have to add the field to the
excel file before I import it, but it would contain null values. Then when I
try to set it as a pk it won't let me because an index or primary key may not
contain null values. I have actually imported the excel file to tblClaim
with success, but getting the ClaimID on the other two tables to set
themselves equal to the correct ClaimID has somewhat mystified me. I
apologize if I misread your post, and thanks again for your help.

Shannan

Use the ClaimID to do it for you. The ClaimID should be unique in tblClaim.
You can use it as your primary key and as foreign key in the other tables.

Another way still uses the ClaimID with your autonumber in tblClaim. Set
the autonumber as primary and a number field - integer - as foreign key in
the other tables. When you append to the other tables join the ClaimID field
from tblClaim to the fields in the other tables. Append the autonumber from
tblClaim as you append the Excel data.

I have a table called "tblClaim" that stores information such as customer and
machine information. There are 2 sub tables, one for parts replaced and one

[quoted text clipped - 19 lines]

Shannan

 

[KARL DEWEY]

I thought that you had ClaimID in the Excel.

Your Excel worksheets have to have data that is common otherwise they never
can be correlated. Is there anything common? What releates them?

Karl,

Thanks for your help.

The problem I am having is that I do not have the ClaimID field in the excel
file that I am importing. Therefore, I would have to add the field to the
excel file before I import it, but it would contain null values. Then when I
try to set it as a pk it won't let me because an index or primary key may not
contain null values. I have actually imported the excel file to tblClaim
with success, but getting the ClaimID on the other two tables to set
themselves equal to the correct ClaimID has somewhat mystified me. I
apologize if I misread your post, and thanks again for your help.

Shannan

Use the ClaimID to do it for you. The ClaimID should be unique in tblClaim.
You can use it as your primary key and as foreign key in the other tables.

Another way still uses the ClaimID with your autonumber in tblClaim. Set
the autonumber as primary and a number field - integer - as foreign key in
the other tables. When you append to the other tables join the ClaimID field
from tblClaim to the fields in the other tables. Append the autonumber from
tblClaim as you append the Excel data.

I have a table called "tblClaim" that stores information such as customer and
machine information. There are 2 sub tables, one for parts replaced and one

[quoted text clipped - 19 lines]

Shannan

 

[socasteel21 via AccessMonster.com]

The files I will be importing will be coming from an outside source that is
totally unaware that a database even exists. The only thing unique about
each row in the Excel table would be a field called "Dealer Reference Number".
However, this is not the pk in the tblClaim. ClaimID is the pk. ClaimID is
an autonumber. The Dealer Reference Number is a number that the dealer would
create on his/her own.

Thanks for your help.

Shannan

I thought that you had ClaimID in the Excel.

Your Excel worksheets have to have data that is common otherwise they never
can be correlated. Is there anything common? What releates them?

Karl,

[quoted text clipped - 25 lines]

 

[KARL DEWEY]

Do like I said but join on Dealer Reference Number instead of ClaimID.

The files I will be importing will be coming from an outside source that is
totally unaware that a database even exists. The only thing unique about
each row in the Excel table would be a field called "Dealer Reference Number".
However, this is not the pk in the tblClaim. ClaimID is the pk. ClaimID is
an autonumber. The Dealer Reference Number is a number that the dealer would
create on his/her own.

Thanks for your help.

Shannan

I thought that you had ClaimID in the Excel.

Your Excel worksheets have to have data that is common otherwise they never
can be correlated. Is there anything common? What releates them?

Karl,

[quoted text clipped - 25 lines]

Shannan

 

[socasteel21 via AccessMonster.com]

Okay. I imported the table from the Excel file and set the Dealer Reference
Number as the PK. Then I assigned a second key in my tblClaim so that I now
have ClaimID as the pk and Dealer Reference Number as a foreign key. I ran
an append query to import the file to tblClaim and the ClaimID kept in
sequence as it was supposed to, but when I ran the other query to append
records to my tblPartPerClaim table the ClaimID did not match the one that
was automatically entered in tblClaim. It actually entered the ClaimID field
as "0". I thought that since I related the 2 imported tables and then
related the imports to the tblClaim via the Dealer Reference Number. It
would keep with the ClaimID because ClaimID is the pk in tblClaim, but I was
obviously mistaken.

If you have any ideas on how I could accomplish what I am trying to do, I
would appreciate it. I have to keep ClaimID going as a field.



Shannan

Do like I said but join on Dealer Reference Number instead of ClaimID.

The files I will be importing will be coming from an outside source that is
totally unaware that a database even exists. The only thing unique about

[quoted text clipped - 17 lines]

 

[KARL DEWEY]

If you are going to use the ClaimID as primary key then set the relation
between the tables.

When you append the second table it will fill the field because of the
integerity in the relation -- but you must join the tables in the query on a
field that is common. If your Dealer Reference Number is common then use
that.

Okay. I imported the table from the Excel file and set the Dealer Reference
Number as the PK. Then I assigned a second key in my tblClaim so that I now
have ClaimID as the pk and Dealer Reference Number as a foreign key. I ran
an append query to import the file to tblClaim and the ClaimID kept in
sequence as it was supposed to, but when I ran the other query to append
records to my tblPartPerClaim table the ClaimID did not match the one that
was automatically entered in tblClaim. It actually entered the ClaimID field
as "0". I thought that since I related the 2 imported tables and then
related the imports to the tblClaim via the Dealer Reference Number. It
would keep with the ClaimID because ClaimID is the pk in tblClaim, but I was
obviously mistaken.

If you have any ideas on how I could accomplish what I am trying to do, I
would appreciate it. I have to keep ClaimID going as a field.



Shannan

Do like I said but join on Dealer Reference Number instead of ClaimID.

The files I will be importing will be coming from an outside source that is
totally unaware that a database even exists. The only thing unique about

[quoted text clipped - 17 lines]

Shannan

 

[KARL DEWEY]

If the last is of no help then post your fields with some examples of data.

Okay. I imported the table from the Excel file and set the Dealer Reference
Number as the PK. Then I assigned a second key in my tblClaim so that I now
have ClaimID as the pk and Dealer Reference Number as a foreign key. I ran
an append query to import the file to tblClaim and the ClaimID kept in
sequence as it was supposed to, but when I ran the other query to append
records to my tblPartPerClaim table the ClaimID did not match the one that
was automatically entered in tblClaim. It actually entered the ClaimID field
as "0". I thought that since I related the 2 imported tables and then
related the imports to the tblClaim via the Dealer Reference Number. It
would keep with the ClaimID because ClaimID is the pk in tblClaim, but I was
obviously mistaken.

If you have any ideas on how I could accomplish what I am trying to do, I
would appreciate it. I have to keep ClaimID going as a field.



Shannan

Do like I said but join on Dealer Reference Number instead of ClaimID.

The files I will be importing will be coming from an outside source that is
totally unaware that a database even exists. The only thing unique about

[quoted text clipped - 17 lines]

Shannan

 

[socasteel21 via AccessMonster.com]

Karl,

I'm sorry for not responding. I have been out of town and have not had
access to the internet during the past week. I will try what you have
suggested. Thanks for your help.

Shannan

If the last is of no help then post your fields with some examples of data.

Okay. I imported the table from the Excel file and set the Dealer Reference
Number as the PK. Then I assigned a second key in my tblClaim so that I now

[quoted text clipped - 20 lines]