Results 1 to 6 of 6
  1. #1
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74

    Should I use separate tables?

    Hi all, I just want some opinions on best practices in this scenario. I have two data sources coming into my database, one from excel and one from quickbooks. I have one table for each of these line item data sources because the fields vary wildly. There are some common elements like "Line Item Description" and "Heading", but aside from that, they are too different to store the info together because I need to collect different information on each. My question is how to store the data that relates to both of these tables. For each line item in both tables, I need to store 60 months worth of data (ID, DataMonth, DataValue). I need to eventually display the info together. Does it make more sense to:
    (1) have two separate data tables, join each to their respective Line Items table, then union them
    (2) store the data in one table, separate it to join it to its respective line items table, union it after the fact
    (3) try harder to figure out a good way to put all of the line items from excel and quickbooks in one table even though these items aren't used together at all unless the data gets involved and I need different fields depending on which data source it comes from


    (4) something else that's totally obvious and way better that I didn't think of.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'd vote for the union query, using aliases for the common fields that have different field names. Don't see the sense of creating meshed tables plus keeping the originals in the db, and dumping the originals would be a concern for me unless I had absolute confidence in the accuracy of any operation that creates a combined table. The union query can be treated as a table.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    Thanks for weighing in. Although I just realized one very important thing - I need to be able to pull up all of the data in an updateable recordset and I can't do that based on a union query, so that rules out option #1, but option #2 could still be a contender.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    maybe not?
    store the data in one table, separate it to join it to its respective line items table, union it after the fact

  5. #5
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    As long as I can edit the data all at once, it should work. Then I can union it after I work with the data for display purposes. But the not updateable thing makes me want to stay away from union queries altogether. I don't think I'd need to edit the end product now, but I don't want to back myself into a corner in case I do need to update it in the future

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure you'd need to union if you mesh the data into one table if that's the route taken. If you're talking about editing after bringing it together, combining it first should allow that. If you're talking about separate tables and then combining in a query and trying to edit you may end up with an non updatable query/recordset. Linking the 2 tables into another db could allow you to mesh it without losing the original data, plus you wouldn't have 3 tables in one db with basically the same info, which could introduce file size issues. I say "could" because I have no idea how much data 60 months represents.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Separate tables or a set of unified tables?
    By fret hack in forum Database Design
    Replies: 2
    Last Post: 12-21-2017, 12:48 PM
  2. How to merge separate tables
    By RogueIII in forum Queries
    Replies: 25
    Last Post: 01-20-2016, 09:52 AM
  3. Replies: 4
    Last Post: 07-31-2014, 05:07 PM
  4. Memo fields in separate tables
    By mhart in forum Database Design
    Replies: 1
    Last Post: 12-02-2011, 05:51 PM
  5. run a report from 2 separate tables
    By Kajinga in forum Reports
    Replies: 2
    Last Post: 11-23-2011, 05:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums