Append queries - to archive records 1

[integritycare]

Hi,
Our Db uses an invoice system that has a main form and a subform. The Main form holds general data, such as invoice number, client id, etc.
The subform holds service data, such as service type, date of service , employee times and charges with the subtotals etc.

The two tables are linked in the relationships in this manner;
The main form is related to the subform using referntial Integrity plus Cascade Update Related fields and Cascade Delete Related records.
What has to happen is that an append query will append records by date (from the main form to an archive table.
The subform records need to be appended to an archive table as well. Both Archive tables are a direct copy from the main accounts and accounts data tables.

The queries I have used are as below
The main query for the main form is

INSERT INTO Accounts_Archived ( ACS, CompanyID, BrokerStaffID, InvoiceNumber, DateRaised, DueDate, DateRcvd, ClientID, Firstname, Lastname, FileNo, Dept, EntBy, Companyname, Contact, ATTN, DTotal, NTT, PNT, SST, SUNT, PWTT, PHT, WORKHRS, Totals, GST, Contrib, SubTotal, InvoiceTotal, BalDue, AmmtRcvd, Notes, NONPrintNotes, CreditN, CreditT, BHRr, BDR, BNR, BPwknR, BPWndR, BSatr, BSunr, BPHR, InvGST, BillingAddress, ChqNo, PD, BC, InvTotal, AcctName, acType, Tax, AcctType, SAcct, Cost, ContRqd, CHK20, CHK21, [BY], PS, EPrint, Nextofkin, QueryInv, VRT, PO, NOKL, [Month], CurrencyType, AccountNo, Code, GstSgn, DebitTotal, ContractNo, DateEM, ChkSt, Discount, CostC, MultiInv, FP, AllocAmt, Signoff, [Date], DataChck, DataSign, Snd, CoNo, TypeOS, HDay, PPMt )
SELECT Accounts08.ACS, Accounts08.CompanyID, Accounts08.BrokerStaffID, Accounts08.InvoiceNumber, Accounts08.DateRaised, Accounts08.DueDate, Accounts08.DateRcvd, Accounts08.ClientID, Accounts08.Firstname, Accounts08.Lastname, Accounts08.FileNo, Accounts08.Dept, Accounts08.EntBy, Accounts08.Companyname, Accounts08.Contact, Accounts08.ATTN, Accounts08.DTotal, Accounts08.NTT, Accounts08.PNT, Accounts08.SST, Accounts08.SUNT, Accounts08.PWTT, Accounts08.PHT, Accounts08.WORKHRS, Accounts08.Totals, Accounts08.GST, Accounts08.Contrib, Accounts08.SubTotal, Accounts08.InvoiceTotal, Accounts08.BalDue, Accounts08.AmmtRcvd, Accounts08.Notes, Accounts08.NONPrintNotes, Accounts08.CreditN, Accounts08.CreditT, Accounts08.BHRr, Accounts08.BDR, Accounts08.BNR, Accounts08.BPwknR, Accounts08.BPWndR, Accounts08.BSatr, Accounts08.BSunr, Accounts08.BPHR, Accounts08.InvGST, Accounts08.BillingAddress, Accounts08.ChqNo, Accounts08.PD, Accounts08.BC, Accounts08.InvTotal, Accounts08.AcctName, Accounts08.acType, Accounts08.Tax, Accounts08.AcctType, Accounts08.SAcct, Accounts08.Cost, Accounts08.ContRqd, Accounts08.CHK20, Accounts08.CHK21, Accounts08.BY, Accounts08.PS, Accounts08.EPrint, Accounts08.Nextofkin, Accounts08.QueryInv, Accounts08.VRT, Accounts08.PO, Accounts08.NOKL, Accounts08.Month, Accounts08.CurrencyType, Accounts08.AccountNo, Accounts08.Code, Accounts08.GSTSgn, Accounts08.DebitTotal, Accounts08.ContractNo, Accounts08.DateEM, Accounts08.ChkSt, Accounts08.Discount, Accounts08.CostC, Accounts08.MultiInv, Accounts08.FP, Accounts08.AllocAmt, Accounts08.Signoff, Accounts08.Date, Accounts08.DataChck, Accounts08.DataSign, Accounts08.Snd, Accounts08.CoNo, Accounts08.TypeOS, Accounts08.HDay, Accounts08.PPmt
FROM Accounts08
WHERE (((Accounts08.ACS)="Paid") AND ((Accounts08.DateRaised) Between [Forms]![subfrmCompany-EOY]![txtStartDate] And [Forms]![subfrmCompany-EOY]![txtEndDate]));

and the subform append query is

INSERT INTO tblAccountsDataArch ( ACS, BrokerStaffID, ClientID, Firstname, Lastname, Dept, EntBy, Companyname, Client, Start, Fin, D1, N1, PN1, SS1, Sun1, PW1, PH1, PPHR1, La1, TWHrs, STotal, StGST, GST, InvGST, InvoiceNumber, StaffID, SFirstname, SLastname, Odost, OdoEnd, ODTotal, NGST, TDiff, SDate, AD, EPrint, QTY, CostC, Exp, TSchk, Chk1 )
SELECT tblAccountsData.ACS, tblAccountsData.BrokerStaffID, tblAccountsData.ClientID, tblAccountsData.Firstname, tblAccountsData.Lastname, tblAccountsData.Dept, tblAccountsData.EntBy, tblAccountsData.Companyname, tblAccountsData.Client, tblAccountsData.Start, tblAccountsData.Fin, tblAccountsData.D1, tblAccountsData.N1, tblAccountsData.PN1, tblAccountsData.SS1, tblAccountsData.Sun1, tblAccountsData.PW1, tblAccountsData.PH1, tblAccountsData.PPHR1, tblAccountsData.La1, tblAccountsData.TWHrs, tblAccountsData.STotal, tblAccountsData.StGST, tblAccountsData.GST, tblAccountsData.InvGST, tblAccountsData.InvoiceNumber, tblAccountsData.StaffID, tblAccountsData.SFirstname, tblAccountsData.SLastname, tblAccountsData.Odost, tblAccountsData.OdoEnd, tblAccountsData.ODTotal, tblAccountsData.NGST, tblAccountsData.TDiff, tblAccountsData.SDate, tblAccountsData.AD, tblAccountsData.EPrint, tblAccountsData.QTY, tblAccountsData.CostC, tblAccountsData.Exp, tblAccountsData.TSchk, tblAccountsData.Chk1
FROM tblAccountsData;

However while the main append query works, the append query will append all the data,. How can I get the subform data to append in relation to the mainform data. (ie; the Invoice number and its associated subform data). Hope I have explained this ok.

Many thanks,

Integrity

 

[PHV]

I'd JOIN tblAccountsData with Accounts08 on the proper foreign key and apply the same criteria

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[integritycare]

Hi PHV,
Many thanks for your reply.
Would you be able to give me some more information. For testing purposes I have set up an append query joining both tables as below. Is this what you meant?

INSERT INTO Accounts_Archived ( BrokerStaffID, InvoiceNumber, DateRaised, DueDate, DateRcvd, ClientID, Firstname, Lastname, FileNo, Dept, EntBy, Companyname, Contact, ATTN, DTotal, NTT, PNT, SST, SUNT, PWTT, PHT, WORKHRS, Totals, GST, Contrib, SubTotal, InvoiceTotal, BalDue, AmmtRcvd, Notes, NONPrintNotes, CreditN, CreditT, BHRr, BDR, BNR, BPwknR, BPWndR, BSatr, BSunr, BPHR, ACS, InvGST, BillingAddress, ChqNo, PD, BC, InvTotal, AcctName, acType, Tax, AcctType, SAcct, Cost, ContRqd, CHK20, CHK21, [BY], PS, EPrint, Nextofkin, QueryInv, VRT, PO, NOKL, [Month], CurrencyType, AccountNo, Code, GstSgn, DebitTotal, ContractNo, DateEM, ChkSt, Discount, CostC, MultiInv, FP, AllocAmt, Signoff, [Date], DataChck, DataSign, Snd, CoNo, TypeOS, AddPayments, PTax, HDay, LockedByUser, PPMt, ChangeInv, TCosts, ClientID, Firstname, Lastname, Dept, EntBy, Companyname, GST, InvGST, EPrint, CostC, ChangeInv, NOKL, Nextofkin )
SELECT Accounts08.BrokerStaffID, Accounts08.InvoiceNumber, Accounts08.DateRaised, Accounts08.DueDate, Accounts08.DateRcvd, Accounts08.ClientID, Accounts08.Firstname, Accounts08.Lastname, Accounts08.FileNo, Accounts08.Dept, Accounts08.EntBy, Accounts08.Companyname, Accounts08.Contact, Accounts08.ATTN, Accounts08.DTotal, Accounts08.NTT, Accounts08.PNT, Accounts08.SST, Accounts08.SUNT, Accounts08.PWTT, Accounts08.PHT, Accounts08.WORKHRS, Accounts08.Totals, Accounts08.GST, Accounts08.Contrib, Accounts08.SubTotal, Accounts08.InvoiceTotal, Accounts08.BalDue, Accounts08.AmmtRcvd, Accounts08.Notes, Accounts08.NONPrintNotes, Accounts08.CreditN, Accounts08.CreditT, Accounts08.BHRr, Accounts08.BDR, Accounts08.BNR, Accounts08.BPwknR, Accounts08.BPWndR, Accounts08.BSatr, Accounts08.BSunr, Accounts08.BPHR, Accounts08.ACS, Accounts08.InvGST, Accounts08.BillingAddress, Accounts08.ChqNo, Accounts08.PD, Accounts08.BC, Accounts08.InvTotal, Accounts08.AcctName, Accounts08.acType, Accounts08.Tax, Accounts08.AcctType, Accounts08.SAcct, Accounts08.Cost, Accounts08.ContRqd, Accounts08.CHK20, Accounts08.CHK21, Accounts08.BY, Accounts08.PS, Accounts08.EPrint, Accounts08.Nextofkin, Accounts08.QueryInv, Accounts08.VRT, Accounts08.PO, Accounts08.NOKL, Accounts08.Month, Accounts08.CurrencyType, Accounts08.AccountNo, Accounts08.Code, Accounts08.GSTSgn, Accounts08.DebitTotal, Accounts08.ContractNo, Accounts08.DateEM, Accounts08.ChkSt, Accounts08.Discount, Accounts08.CostC, Accounts08.MultiInv, Accounts08.FP, Accounts08.AllocAmt, Accounts08.Signoff, Accounts08.Date, Accounts08.DataChck, Accounts08.DataSign, Accounts08.Snd, Accounts08.CoNo, Accounts08.TypeOS, Accounts08.AddPayments, Accounts08.PTax, Accounts08.HDay, Accounts08.LockedByUser, Accounts08.PPmt, Accounts08.ChangeInv, Accounts08.TCosts, tblAccountsData.ClientID, tblAccountsData.Firstname, tblAccountsData.Lastname, tblAccountsData.Dept, tblAccountsData.EntBy, tblAccountsData.Companyname, tblAccountsData.GST, tblAccountsData.InvGST, tblAccountsData.EPrint, tblAccountsData.CostC, tblAccountsData.ChangeInv, tblAccountsData.Nokl, tblAccountsData.Nextofkin
FROM Accounts08 INNER JOIN tblAccountsData ON Accounts08.Invoices = tblAccountsData.Invoices;

How do I get this append query to append to both tables? I'm a bit lost on how to set this up.

Many thanks

Integrity

 

[PHV]

How do I get this append query to append to both tables
You can't.
You should use two append queries.
One for the main table and one for the child table (with the changes I suggested you)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886