Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Unanswered: combining two almost similar tables in a report listing

    The sales and purchases tables in my db are almost identical. One difference is a prefix for the autonumber - clearly pointing out that this is a sale or this is a purchase.
    The sales and purchases are concerned with a sort of inventory, so one cannot sell more of a specific item than what is in stock.

    I want to create a report that lists all sales and purchases in one chronologic listing, where the output fields are as follows:

    date, number, transaction type (sales or purchase), item name, amont of items (sold or purchased), and the new total of each specific item.

    This will be stored in a separate inventory or history table. I am not sure if I should use two columns for previous total amount and new total amount, or make the calculation of the stored value for each new amount base itself upon a dynamic calculation of previous records. I fear that the latter will put unnecessary strain on the db performance.

    What is a good way of (in code or QBE) of doing this?

    Furthermore, I am also wondering how to make a history/log of all updates in another table. This should work in such a way that when the forms associated with sellling/purchasing updates/adds a record, that one is automatically copied/appended to a separate log table, probably with its own auto-numbering field, storing the indexed original ID/keys in normal number fields.

    Lastly, both the log table and the original table should be updated in another database, NOT using replication as it fuks up the autonumbering that relies on Standard prefixes and incrementation, not only uniqueness. This is one-way, the receiving table is only for backup. The log table is to enable the users to reconstruct what has happened if something goes wrong or looks strange.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: combining two almost similar tables in a report listing

    Originally posted by kedaniel
    The sales and purchases tables in my db are almost identical. One difference is a prefix for the autonumber - clearly pointing out that this is a sale or this is a purchase.
    The sales and purchases are concerned with a sort of inventory, so one cannot sell more of a specific item than what is in stock.

    I want to create a report that lists all sales and purchases in one chronologic listing, where the output fields are as follows:

    date, number, transaction type (sales or purchase), item name, amont of items (sold or purchased), and the new total of each specific item.

    This will be stored in a separate inventory or history table. I am not sure if I should use two columns for previous total amount and new total amount, or make the calculation of the stored value for each new amount base itself upon a dynamic calculation of previous records. I fear that the latter will put unnecessary strain on the db performance.

    What is a good way of (in code or QBE) of doing this?

    Furthermore, I am also wondering how to make a history/log of all updates in another table. This should work in such a way that when the forms associated with sellling/purchasing updates/adds a record, that one is automatically copied/appended to a separate log table, probably with its own auto-numbering field, storing the indexed original ID/keys in normal number fields.

    Lastly, both the log table and the original table should be updated in another database, NOT using replication as it fuks up the autonumbering that relies on Standard prefixes and incrementation, not only uniqueness. This is one-way, the receiving table is only for backup. The log table is to enable the users to reconstruct what has happened if something goes wrong or looks strange.
    For starters, you might want to create a union query of the two tables and then create a field using an IIf statement to differentiate between the Sales and the Purchases. With all the data in one recordset and easily identified as to type it gives you a good starting point.

    Most of your requirements, if broken down into individual tasks, should be doable though.

    Don't have time to think more at the moment. Back to work. Will check back later.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •