Results 1 to 12 of 12
  1. #1
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21

    Combining (un-normalized) Tables for general purpose reporting

    Hi!



    I am trying to develop a good base table for reporting purposes, that will attempt to reconcile data from 3 sources and I was hoping to get some much valued outside input. I am very downstream from the data sources, and am attempting what is likely a non standard approach. Any alternative suggestions are appreciated.

    Unfortunately, the data sources are not normalized and I was hoping to achieve some degree of normalization from my efforts. Another motivating factor is that each one of these data sets is up to a million records. However, once we omit certain fields and they are grouped down the biggest one is about 9,000 records. Linking them up to different tables for reporting is cumbersome (especially when reporting against all 3) and we usually have to kick out a bunch of fields anyway. Also, a lot of the attributes in each table are very similar(Cust#, ST, PurchaseDate, etc). So my goal is to consolidate into one table, while minimizing excess columns and enforcing some kind of additional integrity.

    I have started by creating 3 group by queries on each data source (MassPurchases, GroupPurchases, IndividualPurchases), which effectively limits the ID field in each table to one record. I use these queries to create 3 tables. Although each resultant table has an ID field, they are not related to one another and so I was planning on using that as a Primary key in each table. From there, I was going to add a field that simply specifies the data Source to each table, and then make combine that with ID for a Composite Key.

    So for instance, I have made the tables MassPurchases, GroupPurchases, IndividualPurchases.

    The composite keys in each table would be ID and Source (Source is essentially the source table name). From here, I would do a make table with IndividualPurchases, and then append the other two (Mass and Group)to the final Purchases table. Then, the final table would have the same composite key structure of ID-Source. The idea being I have all three sources in one table, and could do summaries, crosstabs, and ad hoc stuff more readily from that base table.

    Is all of this overkill? Does it even make sense? Are there any alternative suggestions?
    I have a feeling that I am getting into ETL/Datawarehousing waters, and so apologize if this is less of a design question.

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you going to keep receiving new data that will have to be integrated into your normalized structure?

    Does the ID field in each table have some meaning beyond these tables (SSN, CustNumber) - how is it generated? Are there related child tables dependent on this ID? If all no, then why do you need this constructed ID field? Just let autonumber field generate an ID and have a field for the Source category.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21
    Yes new data will flow in at regular intervals, but the nature is pretty well known (We have been using this group by solution on one of the tables for a while now), and we only keep the fields that we know will group. If there are inconsistencies, we can catch them and have corrected at source using a check on the count of distinct IDs. Does adding a normalization "filter" add any additional benefit when you are downstream from the source?

    Also, the ID field in each does tie back to some other important tables, and is treated as part of a unique identifier upstream from my group. Ideally, my ID field will be used as the foreign key for various reporting/summary tables - all of which will be extended from the base table I am trying to create. I am trying to model what data miners would call building a customer signature, but the domains we are reporting are deal types instead of customers. Probably should have made that clear to start with...

    So at this point I am pretty sure that consolidating these three sources is the way to go, and am thinking that I will map the other related fields to each other (ie MassPurchaseDate and IndividualPurchaseDate will just be PurchaseDate), and exclude some of the ones that don't line up.

    Thanks for the response, and if you have any other suggestions they are much appreciated!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Options:

    1. New 'master' table that is routinely updated with new data by use of INSERT and UPDATE sql actions.

    2. UNION query. There is no wizard or designer for UNION, must type in SQL View of query designer. Limited to 50 lines. Also, VBA doesn't seem to like to open Recordsets where a UNION is involved.

    SELECT ID & "I" AS ID, data1, data2 FROM IndividualPurchases
    UNION SELECT ID & "M", data1, data2 FROM [Mass and Group]
    UNION SELECT ID & "P", data1, data2 FROM Purchases;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21
    Thank you for the suggestions.

    It seems like a tough choice. I actually require VBA accessibility for this table. UNION does seem like the most logical approach, as I can combine the 3 tables in one step. One good thing about being downstream is that I don't have to worry about keeping a history as much (and if I ever do I can save the files as CSV in a folder somewhere on our network). So maybe I can use UNION and then CREATE TABLE when I need to report off it, hoping that VBA will just treat it as a normal table.
    Too bad Access was not built to support this type of query. From what I understand UPDATE queries are slow and can only be done one field at a time.

    I am also really interested in UNION queries now. Thanks for pointing that out to me.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Domain aggregate functions used in VBA can pull data from UNION query. It's just opening a Recordset doesn't seem to be agreeable. And that's if a UNION is involved anywhere in query sequence leading up to the dataset trying to open recordset on. I had to redesign a process to not rely on UNION query because of this.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21
    wow that is surprising... I wonder if ADO and VBA are more agreeable to working with UNION?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I use ADO recordsets. Hadn't tried DAO with UNION. Maybe I should.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21
    Hey June, as a thanks (and out of personal interest) I did a search and found this *minor* confirmation that DAO may open the recordset.
    http://www.utteraccess.com/forum/Tro...-t1622961.html

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That code doesn't declare the recordset type so I guess it defaults to DAO.

    Thanks for the followup.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    mantooth29 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Aug 2012
    Posts
    21
    Actually its missing from the provided code, but the answerer suggests adding the DAO reference and making it an explicit DAO recordset. Once the OP did this I believe he was able to open.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have not experienced slowness issue with UPDATE queries but then I don't have to use it on large sets of data.

    An UPDATE can be done on more than one field at a time (this I do).

    Building a report on UNION query is not an issue. Also, all graphs I have designed are based on UNION queries (because of my sin of non-normalized data).

    It is only VBA ADO recordsets that I had problems with when UNION was involved.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-16-2012, 02:10 PM
  2. General section in Tables
    By recek05 in forum Access
    Replies: 4
    Last Post: 06-22-2012, 04:58 PM
  3. What's the purpose of multi key for one table?
    By latestgood in forum Access
    Replies: 3
    Last Post: 07-01-2011, 12:15 PM
  4. Hide tables from general users
    By stryder09 in forum Access
    Replies: 0
    Last Post: 03-28-2011, 11:13 AM
  5. Creating Form from Normalized Tables
    By heathers in forum Forms
    Replies: 2
    Last Post: 09-10-2009, 03:43 AM

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