Get a field to be in two different tables?

[sidalam]

I have a table that I want to normalise and so thus have used " Analyse Table" to do this.
How do I get a field like "Store ID" to be in two different tables?

 

[traycee]

Make StoreID the primary key in the table with the 'one' relationship and make StoreID the foreign key in the table with the 'many' side of the relationship.

 

[dhookom]

Then use a form with a subform to keep the StoreID in synch.

Duane
Hook'D on Access
MS Access MVP

 

[sidalam]

I have imported storeorders.txt (File/Get external data) in a blank database. I follow the Import Text Wizard, after doing this I click on Analyse Table which opens the wizard. In the wizard I take out Order and Date and Status to a new table which I call tblOrder. In table tblOrder I also want StoreID which is a foreign key and in the other table which is called tblStore it is a primary key. However when I use Analyse Table to move the field StoreID to tblOrder it no longer remains in tblStore.

What shall I do?

Note I have tried making StoreID a primary key in tblOrder but Access throws up an error about "repeated fields"

=========================================================
storeorders.txt (Sorry I am unable to attach this text file so I have copied and pasted it)

"StoreID","Address1","Address2","Address3","Postcode","Order","Date","Status","Perfume","Description","BatchQuantity"
"D1","67 Canal Street","Newton ","Durham","DH1 2BB",1001.00,12/12/2008 00:00:00,"Outstanding","Midnight Flight","Mens Aftershave",1.00
"D1","67 Canal Street","Newton ","Durham","DH1 2BB",1001.00,12/12/2008 00:00:00,"Outstanding","Aftermath","Women's perfume ",2.00
"D2","42 Cathedral Road","Belmont","Yorkshire","YK6 2FR",1003.00,29/11/2008 00:00:00,"Outstanding","Mirepoix","Mens Aftershave",2.00
"D3","43 Hylton Road","Newbridge","Derbyshire","DB7 2PT",1004.00,5/11/2008 00:00:00,"Processed","Midnight Flight","Mens Aftershave",1.00
"D3","43 Hylton Road","Newbridge","Derbyshire","DB7 2PT",1004.00,5/11/2008 00:00:00,"Processed","Crazy Knights","Mens Aftershave",3.00
"D4","Dene Street","Castleton","Wiltshire","WT1 3PZ",1006.00,3/12/2008 00:00:00,"Outstanding","Clubbin","Mens Aftershave",4.00
"D5","3 Elvet Bridge","Shire Hall","Oxfordshire","OX3 3PZ",1007.00,4/10/2008 00:00:00,"Processed","Midnight Flight","Mens Aftershave",1.00
"D5","3 Elvet Bridge","Shire Hall","Oxfordshire","OX3 3PZ",1007.00,4/10/2008 00:00:00,"Processed","Lotus Leaves","Women's perfume ",2.00
"D6","14 Market Place","Newton Hall","Cambridgeshire","PE41 4NQ",1009.00,15/11/2008 00:00:00,"Outstanding","Sabre","Mini Perfumes",1.00
"D7","1 Finchale Avenue","Peterborough ","Norfolk","NF1 5NT",1010.00,15/11/2008 00:00:00,"Outstanding","Joix","Women's perfume ",1.00

 

[sidalam]

Please help with my dilemma?

 

[dhookom]

I see three tables
[tt][blue]
tblStores
StoreID pk
Address1
Address2
Address3
PostCode

tblOrders
OrderID
StoreID
OrderDate
OrderStatus

tblOrderDetails
OrderID
ProductDescription
BatchQuantity
[/blue][/tt]

Create three append queries, one for each table. Two will group by and the order details will not.

Duane
Hook'D on Access
MS Access MVP