change one table into two?

[silentwolf]

Hi guys,

I made a misstake and have created a table with ID , Date, Amount, SupplierIDRef (FK), KennIDRef(FK)

However now I like to change this into two tables for example like this

tblSupplierBills and tblSupBillDetails...

in other words I like to split the Bill of the supllier into two tables.. how can I achive this by a query withouth entering again all Bills again?

Hope this is understandable and someone can give me a bit of help with this..

Many thanks

SW

 

[ranman256]

Create a make table query using your existing table as the source.
add the fields you need.

 

[xenou]

Or remove the fields you don't need

Assuming there will be a parent child relationship, the existing table probably has the "details" already, but you will need to create a parent table with the data that is currently duplicated (such as supplier ID, order date, order number or whatever).

A make table query is another way to do that ....
Usually however, after running a make table query, I will delete the data then review all the data types, add keys, indexes, relationships or whatever is needed. Then run the query again as an append query instead of a make table query. I mean, for any table that is going to be a permanent and important table in the database, just using the make table query as a quick way to get it started since it will create most of what you need immediately.

 

[silentwolf]

Hi guys,
thanks for your reply!
The data I have at present is a table called tblLiefRechnungen this table is linked to tblKennzahlen and tblLieferanten... tblLieferanten(Supplier) and tblKennzahlen("LikeDetails") ..
And now I need to get each Supplier which has more then one Detail into a seperate table.. I kind of understand what should be done but still unsure how to go about it...

But I will try your suggestion and hope I will get it to work .)

Thanks to both of you!

 

[xenou]

Okay.
The first step is to write a query to get a list of each supplier that has a detail record.
But it sounds like you already have that?? -- tblLieferanten(Supplier) ??

And now I need to get each Supplier which has more then one Detail into a seperate table..

Usually, don't think in terms of "more than one" in a case like this. Treat those who have one as being the same as those who have two, or three, or four ....

 

[silentwolf]

Hi Xenou,
yes I have a list of all suppliers tblLieferanten .)
The issue I got is that I have in one table tblLiefRechnungen have the LiefIDRef ("SupplierID") and the KenZIDREf(those are the details..)
Of course I can have a query now wich gives me all Suppliers with the Details... but how to change the appropiate ID so that I can split it into a new table??

I hope you can understand...

 

[xenou]

Of course I can have a query now wich gives me all Suppliers with the Details... but how to change the appropiate ID so that I can split it into a new table??

Hi, Sorry I don't understand. You simply keep an ID that relates parent to child. The ID in the parent table (normally a primary key, such as SupplierID) is a foreign key in the child table. The child table should have it's own primary key (such as DetailID). If keys don't exist you must create them.