Append query problem

[BabyATX13 via AccessMonster.com]

I have a database that is just huge, and at some point in time the company
will have to archive the data in the tables, for the most part I can do this,
the problem is with my linked tables. I don’t know how to archive them all at
once.

Example

Invoice table:
InvoiceNo Auto Number Primary Key
DateFrom Date/Time
DateTo Date/Time
CompanyName Number

15 Blling Tables:
BillingID Auto Number Primary Key
InvoiceNo Number
And some other irrelevant stuff, all different from table to table, depending
on the billing type.

All 15 Billing tables are linked to the one Invoice table by InvoiceNo. My
problem is, I am archiving the invoice table by date and I want the linked
Billing tables’ information to go with the invoice number for those dates
that are archived. I now realize, had I thought of it years in advance, that
a date field in the billing tables would have been helpful, but it is too
late to fix now, as there are thousands of records in them now.

Any help on this matter would be greatly appreciated.
Thank you,
K Board

 

[S Panja]

Try linking BillingID to DateTo as BillingID is related to InvoiceNo in
Billing table and InvoiceNo has a DateTo in Invoice table.

 

[BabyATX13 via AccessMonster.com]

Actually InvoiceNo in Invoice table is related to InvoiceNo in Billing table
One invoice will have many billing details.

Try linking BillingID to DateTo as BillingID is related to InvoiceNo in
Billing table and InvoiceNo has a DateTo in Invoice table.

What will that do for me?

 

[BabyATX13 via AccessMonster.com]

Can anyone help me???? Please

I have a database that is just huge, and at some point in time the company
will have to archive the data in the tables, for the most part I can do this,
the problem is with my Related tables. I don’t know how to archive them all at
once.

Example

Invoice table:
InvoiceNo Auto Number Primary Key
DateFrom Date/Time
DateTo Date/Time
CompanyName Number

15 Blling Tables:
BillingID Auto Number Primary Key
InvoiceNo Number
And some other irrelevant stuff, all different from table to table, depending
on the billing type.

All 15 Billing tables are related to the one Invoice table by InvoiceNo. My
problem is, I am archiving the invoice table by date and I want the linked
Billing tables’ information to go with the invoice number for those dates
that are archived. I now realize, had I thought of it years in advance, that
a date field in the billing tables would have been helpful, but it is too
late to fix now, as there are thousands of records in them now.

Any help on this matter would be greatly appreciated.
Thank you,
K Board

 

[John Spencer (MVP)]

You are going to need 15 queries to do what you need since you have 15 Billing
Tables (and Don't Tell me why you have what is apparently a bad design, I don't
want to know.)

Repeat the following query 15 times to

Insert Into TheArchiveTable (BillingID, InvoiceNo, ...)
SELECT B.BillingID, B.InvoiceNo, ... IN
FROM BillingTableOne as B INNER JOIN Invoice as I
ON B.InvoiceNo = I.InvoiceNo
WHERE I.DateFrom < #1/1/04#


After you have archived all the billing table information, then you can archive
the Invoice table information in a separate query.

 

[BabyATX13 via AccessMonster.com]

John Spencer (MVP) wrote:
You are going to need 15 queries to do what you need since you have 15
Billing
Tables (and Don't Tell me why you have what is apparently a bad design, I
don't
want to know.)



Well you’re going to hear it anyway.





I know I need 16 different queries not 15, I just don’t know how to start.
Also, it’s not a bad design and I don’t appreciate you saying it is. I have
15 very different billing types each has a different looking invoice with
very different information in it. I do not want to duplicate invoice numbers
so they all access the same Invoice table, if I did put all that information
in one Invoice table I would have over 95 fields in my table, and to me, that
is bad design; give me a little credit geeeees.

In spite of your rudeness I will try your suggestion.

K Board

Repeat the following query 15 times to

Insert Into TheArchiveTable (BillingID, InvoiceNo, ...)
SELECT B.BillingID, B.InvoiceNo, ... IN
FROM BillingTableOne as B INNER JOIN Invoice as I
ON B.InvoiceNo = I.InvoiceNo
WHERE I.DateFrom < #1/1/04#

After you have archived all the billing table information, then you can
archive
the Invoice table information in a separate query.

Duh !!!

 

[John Spencer (MVP)]

I apologize for being basic in my answer, but it is hard to estimate the degree
of competence of the poster based on the limited information in the posting.

I did miss a key phrase in your original post and that was that your 15 tables
had some base fields, but also they had additional different fields. I still
suspect that your tables could be further normalized, but you know your data.

By the way, I won't be posting any further replies to your requests for advice.

Good luck and may you be blessed.

 

[BabyATX13 via AccessMonster.com]

John,

I do appreciate your help getting me headed in the right direction. And I
appreciate your apology that was good of you to do. And it seems I owe you
one as well. It’s just the way you came off initially was very rude to me and
it really got under my skin, and I was very mad when I responded to you, so I
am sorry I was so harsh in my reply.

Thanks again for your help.
K Board

I apologize for being basic in my answer, but it is hard to estimate the degree
of competence of the poster based on the limited information in the posting.

I did miss a key phrase in your original post and that was that your 15 tables
had some base fields, but also they had additional different fields. I still
suspect that your tables could be further normalized, but you know your data.

By the way, I won't be posting any further replies to your requests for advice.

Good luck and may you be blessed.

You are going to need 15 queries to do what you need since you have 15
Billing

[quoted text clipped - 29 lines]

Duh !!!