Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    One order table, multiple address types (Access 2003 SP2)

    I have a database that has worked very fine for the last 3-4 years. Now my boss wants me to set it up so we can order new product out of it. Previously we were using it solely to track how much product was being sent out. My problem is, we have 4 different types of addresses. Theoretically I could possibly make do with 2 types, but prefer to keep them separate as we run reports separately for each type. Just a quick example, for inmates we must have a Booking Number Field, a Housing Unit Field plus a Prison Name Field. Someone more technically savvy than me would probably program something that would work but I prefer to keep them all separate.

    I used separate order tables for each of those address types, i.e. tblPrisonAddresses/tblPrisonOrders; tblInternationalAddresses/tblInternationalOrders. Now I am realizing that to get an accurate count of product In Stock minus product sent out, I really need 1 tblOrderDetails. Can someone give me an idea on how to set this up?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    You could add a field AddressType (number, long integer) to tblPrisonAddresses and use an update query to set this field to 1 in all existing records.
    Also add AddressType to tblInternationalAddresses, and use an update query to set this field to 2 in all existing records.
    You should now be able to combine the two address tables into one table. The AddressType field will enable you to distinguish the prison addresses from the international addresses.
    If the tables have a different structure at the moment, you'll have to do some work to make the structure the same, for example by adding extra fields, renaming fields and changing the order.
    You can then copy/paste records from one table into the other, or use a union query.

    You could do something similar for the orders tables.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    I never like to post my database because it takes so long to strip it down small enough for the Lounge, but it seems to help explain my dilemma.

    What I think I am needing is to have 1 tblOrderDetails instead of tblInternationalOrderDetails and tblPrisonOrderDetails in order to correctly track inventory sent out and in stock. If there is a way that you see I can make it work with the current setup, I'd like to do that, but it looks to me like it would be a very complex query.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    Yes you do need a single Order Details table. The existing tables have the same fields, so you can simply merge them into one table. But before you do so, you have to merge the Orders tables. These don't have exactly the same structure, so you'd have to take care of that first. For example, add a field ShipBookingNumber to tblInternationalOrders.

    BTW, why does tblPrisonOrders have DateofBirth and ReleaseDate fields? Those are properties of prisoners, not of orders, or am I being naive?

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    The reason why I have it in orders is that we send out a form for the inmates to complete and when it is sent back, we enter their birthdate and release date in orders as that is where we look for their information generally if a package is returned. I'm studying your response.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    >But before you do so, you have to merge the Orders tables. These don't have exactly the same structure, so you'd have to take care of that first. For example, add a field ShipBookingNumber to tblInternationalOrders.

    OK. I'll try to clarify my problem. I really want to keep these orders separate (tblInternationalOrders and tblPrisonOrders), but use the same tblOrderDetails for both of them because of the difference in order types/addresses. If I use the same tblOrders, there will be many orders that don't have any ShipBookingNumber or ShipHousingUnit, as we ship quite a bit more internationally than to inmates. (We are a distributor of gospel literature)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    I don't think that's feasible.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    That's what I was afraid of. So, in the long run, it shouldn't hurt the integrity of the database to have (let's say in a 5-years time) 1,000 records in tblOrders that have several fields that are not relevant to the International order because that address type does not need BookingNumber and Housing Unit, etc.?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    An empty text field takes up only 1 byte, so even if you have 10 empty text fields in 10,000 records, they contribute only 100 KB to the size of the database.

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: One order table, multiple address types (Access 2003 SP2)

    Thanks for the info!

Posting Permissions

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