append/delete query used to archive invoiced statements

[GinaStar]

have two tables, a current table and an archive table. The current table is the tickets that have not been invoiced and the archive table is the tickets that have been invoiced. After invoicing, tickets are moved to an archive table and deleted from the current table. have been having problems with the queries that move the tickets back and forth, so am rebuilding the queries. and need some advice. will queries work better if put all the fields in (which is the way I previously had it) or if use the asterisk* field and the criteria fields (note: all the field names are the same from the two tables).

there are several queries involved:
one moves to from the current table to the archive table(after it is invoiced out)

one moves from the archive table to the current table by date (in case we need to see the tickets for a certain date again)

one moves from the archive table to the current table by invoice (in case we need to review that invoice number)

one moves from the archive table to the current table by automated number (special invoice number)

here are two of the queries I was previously using
This appends the Tickets and Billing to the Archive:

INSERT INTO tblDataTicketArchive ( guidDetailID, guidTKNO, guidDRID, datTicketPost, datTicketHaul, datTicketLoad, strTicketNo, dblTicketQuan, guidPER, curHaul, strNotes, strAutomated, dblTicketInvoiceNo, datTicketInvoiceDate, dblTicketStatementNo, datTicketStatementDate )
SELECT tblDataTicket.guidDetailID, tblDataTicket.guidTKNO, tblDataTicket.guidDRID, tblDataTicket.datTicketPost, tblDataTicket.datTicketHaul, tblDataTicket.datTicketLoad, tblDataTicket.strTicketNo, tblDataTicket.dblTicketQuan, tblDataTicket.guidPER, tblDataTicket.curHaul, tblDataTicket.strNotes, tblDataTicket.strAutomated, tblDataTicket.dblTicketInvoiceNo, tblDataTicket.datTicketInvoiceDate, tblDataTicket.dblTicketStatementNo, tblDataTicket.datTicketStatementDate
FROM tblDataTicket
WHERE (((tblDataTicket.dblTicketInvoiceNo)>0));

INSERT INTO tblDataBillArchive ( guidDetailID, datBillPost, datBillHaul, dblQuan, guidPER, curBill, strBillNotes, strBillPO, dblBillInvoiceNo, datBillInvoiceDate, strBillAutomated, dblBillStatementNo, datBillStatementDate, fBillInvStatePaid )
SELECT tblDataBill.guidDetailID, tblDataBill.datBillPost, tblDataBill.datBillHaul, tblDataBill.dblQuan, tblDataBill.guidPER, tblDataBill.curBill, tblDataBill.strBillNotes, tblDataBill.strBillPO, tblDataBill.dblBillInvoiceNo, tblDataBill.datBillInvoiceDate, tblDataBill.strBillAutomated, tblDataBill.dblBillStatementNo, tblDataBill.datBillStatementDate, tblDataBill.fBillInvStatePaid
FROM tblDataBill
WHERE (((tblDataBill.dblBillInvoiceNo)>0));

have to have both tickets and billing. also have companion delete queries to match. If anyone can suggest better ways of handling this, I would love to hear it!

Regina Parker
ginastar@sonet.net

 

[Toga]

While there may be a very reason your doing it this way, it appears like your overcomplicating a simple process that Access is ready and able to handle....

Why not use the Current Table for everything and forget the archive table. Simply add another field to the Current Table named "Invoiced", which could be a check box.

That way, you simply use that check box as a filter depending on if you want to see the "Invoiced" tickets or tickets that are not "Invoiced".

 

[GinaStar]

actually, yes, there is a reason that we are doing it this way. front end databases and back end databases. Boss was cheap and wouldn't let us network until recently, actually this past year (been using this database since 96/97). had to keep file sizes small enough to fit on diskettes before we networked and before we got a good zip program. Only way to do this was to be able to put tables in different .mdbs before they grew too large (even tho were compacted AND zipped). Forms are in front end. Not billed are in one table, billed in another. As it was, even when spanning multi disks, took 9+ diskettes to transfer data from mine to office manager. Forms is easy, just one diskette. Is better now that are networked, but have found that we like the archive setup and want to keep it.

This program also does our Personnel, Payroll, DBE/Certified Payroll, Maintenance, Accident Records, Worker's Comp, and Benefits. <g> can you tell that I'm kinda proud of it? We are the only Dump Trucking company in the area that does our books on computer

Have been using it for a long time, but am always tinkering with it trying to 'improve' it, especially as I learn more programming skills

 

[Toga]

If all the table names are the same, I'd use the asterisk* field and the criteria fields. The same approach for the delete queries. Yes, I can tell your very proud of it as well you should be! I doubt your boss has a clue on how much time you've spent working on this and thinking about how to solve certain programming issues.

A word of caution though.....If you happen to be in a multiuser situation at all or one of these queries fails because a record is locked(another user accessing the same record or whatever), not all of your tables will be updated. Can be a very confusing mess to unravel.

It might be worth your time and effort to read up on the use of recordsets so that you can utilize transactions. Transactions allow you to rollback all your queries in the event there was an error in updating one of your tables.

Toga

 

[GinaStar]

thanks. I will look into that!

I've decided to go for a combination approach. I'm using the asterisk for the delete queries, since it doesn't matter about the primary keys (autonumber fields). I'm using all fields except the primary keys on the append queries and letting the program assign new primary keys. Every once in a while, everything is billed all at once so the table on one side will be empty and I can run a compact/repair operation to reset all my autonumbers to zero and I can move all my data back from the archive to the current and run a compact/repair operation to reset the autonumbers in the archive table there.

Regina