Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need a more efficient way to generate *data analysis* queries

    Experts:

    I need some help with *streamlining* up to 6 queries IOT conduct data analysis. Allow me to provide some background for the three tables first:

    Table [ILLNESS]:
    - Includes 20 records with values from "ILL_FIELD_01" through "ILL_FIELD_20"

    Table [PERSON]:
    - Includes 10 records with values from "PER_FIELD_01" through "PER_FIELD_10"

    Table [01_tbl_ProductTables]:
    - Contains a total of 36 records
    - Includes three (3) source organizations (i.e., [SOURCE_FILE]): Alpha, Bravo, Charlie
    - The fourth source = "EXTRA" is a dummy organization
    - In field [AFSAS_FIELDNAMES], I reference the 20 ILLNESS-related and 10 PERSON-related fields.
    - In field [FIELDNAME_STANDARDIZED], I make reference to legacy field names.

    More info on [01_tbl_ProductTables] FOR ILLNESS-related records:
    - There is a total of 24 ILLNESS-related records in [01_tbl_ProductTables]:
    - In the case of organization = "Alpha", there is one record where [FIELDNAME_STANDARDIZED] = "Legacy_Field_1" is mapped to [AFSAS_FIELDNAME] = "ILL_FIELD_01"
    - In the case of organization "Bravo, there are 3 records. Having similar mapping, legacy #1 is mapped to AFSAS #1, legacy #2 is mapped to AFSAS #2, and so forth.
    - In the case of organization "Charlie, there are 4 records. Similar mapping between legacy and AFSAS applies for "Charlie".
    - Finally, given that "Charlie" has up to 4 mapped fields, source file = "EXTRA" includes AFSAS_FIELDNAMES from "ILL_FIELD_05" through "ILL_FIELD_20".

    More info on [01_tbl_ProductTables] FOR PERSON-related records:
    - There is a total of 12 PERSON-related records in [01_tbl_ProductTables]:
    - For each organization (Alpha to Charlie), there are 2 records mapped from legacy to AFSAS.
    - And just like for ILLNESS, the "missing" records (up to 10 PERSON records) are covered by dummy organization "EXTRA".

    Now to the queries:
    - For each product (i.e., ILLNESS and PERSON) I currently have 3 queries:
    - For example, for the ILLNESS product, I have "qry_ILLNESS_FieldsUtilized", "qry_ILLNESS_Gap_Input", and "qry_ILLNESS_Gap_Output"
    - Same set of 3 queries for the PERSON product.

    ILLNESS-related queries:
    Info on query #1 "qry_ILLNESS_FieldsUtilized":
    - Upon opening, I am prompted to enter a source. Let's say "Alpha".
    - Now, remember the total # of ILLNESS-records in table [ILLNESS] = 20 records.
    - In this case, the query returns 17 records. So, I'm missing information about 3 records (i.e. legacy fields not mapped to AFSAS).

    Info on query #2 "qry_ILLNESS_Gap_Input":
    - This serves as a "helper query"... let's skip the explanation for now. I'll get back to it.

    Info on query #3 "qry_ILLNESS_Gap_Output":
    - In query #1, we entered "Alpha" and it returned 17 out of 20 records.
    - When opening ILLNESS' Q3 and enter "Alpha", it provides me the 3 missing records.
    - Same applies when entering, e.g., "Bravo" in both query #1 and query #3. That is, Q1 returns 19 records while Q3 returns 1 record.
    - For Charlie, however, I get 20 records Q1; thus, I get 0 records in Q3.

    PERSON-related queries:


    - Alpha returns 8 records on Q1 and 2 records on Q3... giving me the total of 10 PERSON records/fields.
    - In this case, same record counts of Bravo and Charlie (8+2 = 10). The only difference (in each Q1/Q3 query) are the values of the existing/missing fieldnames.

    Now, here's what I need some help with:
    a. For my actual data set, I have 5 organizations (instead only 3).
    b. More importantly though, I have 10 product tables (versus just 2 in this sample DB).
    c. Thus, in my current setup I would have to create another set of 8 x 3 (i.e., 24) queries giving me a total of 30 queries IOT to analyze which legacy fields have been mapped and/or are missing.
    d. Based on c), this appears to be very inefficient (e.g., criteria = ""ILLNESS" or "PERSON") are hard-coded in query #1.
    e. Also, as you can see, query #3 uses the "helper query" (query #2).

    My question:
    How can the 2 sets of 3 queries each be re-structured so that I do NOT NEED static helper query #2 and ultimately end up with, e.g., only 3 queries (or even less)?
    So, ultimately, I'd love to have a process (maybe via listbox in a form) where I pass the source (Alpha to Charlie) and product (ILLNESS and PERSON) into query criteria telling me which fields have been mapped / are not mapped.


    Thank you for your help in advance.

    Cheers,
    Tom
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    a form with listboxs: lstOrgs, lstProducts, etc...
    youd cycle thru each org, then products, etc, the would execute queries by reading the current item in the list as a variable to build the sql.

    like:
    Code:
    sub btnPrintRpts_click()
    dim vID, vDir , vFile,vCustName
    dim i as integer
    dim qdf as querydef
    
    vDir = "c:\temp\"
    
    
    for i = 0 to lstBox.listcount -1
        vCustName = lstBox.itemdata(i)      'get next item in listbox
        lstBox = vCustName          'set the listbox to this item
    
         'run the sql
       sSql = "select * from table where [Org]='" & lstBox & "' and [field] = '" & lstBox2 & "'"
       docmd.opensql "qsMyQry"
    
         'or build the query for exporting data to excel
    set qdf = currentdb.querydefs("qsMyQry")
    qdf.sql =sSql 
    qdf.close
    
           'export the query to excel
          vFile = vDir & "Invoice_" & vCustName & ".xls"
         docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel9,"qsMyQry", vFile, true, "Invoice"
    next
    end sub

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Instead of having 10 product tables templates similar to the two you have now (I called them templates because the fields are listed as records, not actual fields of the table) wouldn't it make more sense to only use the product table (01_tbl_ProductTables) for this analysis. By the way in post #38 of the previous one I was kinda' hinting to this new challenge.

    I think a solution would be to modify the "generate" code you have to make the make-tables be based on the fields of all organizations and just filter the data for the selected one, let me play with it for a bit.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- thank you for chiming in... knowing that you've responded to this thread already means I'm in "good hands".

    Btw, pls find attached the latest version of the "Generate Products... " DB. Due to version control, I realized the last time when posting a version in the other thread, something had fallen through the "cracks" so to speak. For instance, I started using "EXTRA" vs. "NEW". Also, the additional functionality for "deleting all queries" prior to each run had fallen off.

    To ensure I don't have to always stitch pieces together, this attached version has everything included based on latest development. The only exception is that the products table with generic example data does not match the products table from what I posted in today's thread. That is, today's thread focuses on the "data analysis" piece while the previous thread ("Generate Products") thread was focused on creating a bunch of tables.

    Since I can't post proprietary data in this forum, I'm not entirely sure how we can integrate these two *dummy data* data sets. However, since you're well familiar w/ the background, I feel it's ok though.

    Cheers,
    Tom

    P.S. Based on your post #38 reference... your expertise in this subject matter already allowed you to anticipate these type of scenarios. I'm far from your expertise level, so I guess I had to go through the "live and learn" process.
    Attached Files Attached Files

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    Please have a look at the updated file and let me know. I have simply modified the recordset that gathers the fields to not be filtered by the organization so it gets all legacy fields from all organizations but the data is filtered by organization.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Thank you for posting the updated DB. Maybe it's me but I'm not fully tracking on the process. In case we got our wires crossed (probably my fault for not including all details in post #1), allow me to clarify:

    1. The products tables (as of now) was derived based on legacy data and running a "delta" analysis.
    2. Ultimately, there's nothing wrong per se w/ the products table, EXCEPT that it is not complete.
    3. To better clarify information is post #1, I have included a spreadhsheet (for analysis of "Demographics" alone) which pictorially illustrates the current process.
    4. So, what is shown in cell range AX6:BA14 is merely a visual of the missing data. The query does NOT need to have this particular format.
    5. Ultimately, what I posted this morning gives me Table #2 (blue) in query #1.
    6. Then, in query #3, I show what included in AX:BA but for each organization by itself.

    So, ultimately, what I posted this morning in post #1 uses a different data set.
    However, the same process can be applied to this example data set.

    I hope this helps to clarify what I need to accomplish. That is, determine the gaps in AX (for Alpha) and then add 4 records to the products table. Then, determine the gaps for Bravo and add 3 records to the products table.
    Finally, determine the gap for Charlie and then add 4 records to the products table.

    Upon having added those 11 missing records, my queries should not return anything meaning that my products table is now complete for all organizations and thus create the proper tables.

    Hope this helps,
    Tom
    Attached Files Attached Files

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Please check this one out, in the initial one I forgot about the new fields from other organizations. I don't think you need to add new records for each organization to the product table; we just need to gather all existing fields from all organizations (legacy and new) from the product table.
    Tested this for Demographics and get all 9 fields for each of the 3 organizations.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I was about to post a follow-up to the XLS but then saw your post. Now I see what your version does... while the products table remains unchanged, the routine is "smart enough" to determine the deltas and automatically add the "deltas" to each source's products table. This is way better than what I anticipated.

    So, while this version works fine w/ the example data, it does not work w/ the actual data. Over the past few weeks, I ensured that my tables and fields names for both "example" and "actual" data are identical. This made it easier to replace example data with actual data. So, in this case, I merely copy tables "Pk", "String" and "Products" into your latest version.

    Upon execution though, I get an error 3371 (see attached). Given that my most recent "Products Generator" works fine and I identified any missing fields, etc. I don't know where to look for the potential error. Again, the same products tables (and PK / String) work fine in the version w/o the most recent changes.

    Is there anything that has been added in the VBA and requires synchronization based on my actual data set? Naturally, I cannot post the actual data set in this forum... which I understand makes it more difficult to trouble-shoot. Anyhow, is there maybe something obvious that I am overlooking?
    Attached Thumbnails Attached Thumbnails Error.JPG  

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Did you run the Analize code on this one? I will add some logging code to this so you get to see where it errors out.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I did ran it on the version prior to making today's changes. Just to be certain though, I re-ran the analyzer for all 5 sources (both Pk and String). All logs are empty (which is good). See attached JPG.
    Attached Thumbnails Attached Thumbnails AnalyzeLog.JPG  

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Here is a version which writes the errors in an external text file in the same folder as the Access application (this file). Please run it once then have a look at the text file and let me know if it helps.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Making progress... this version does NOT any errors (w/ actual data set). Unfortunately, not the same products tables are no longer created for the underlying sources.

    That is, prior to this version, I produced the following products:
    1. Source #1 resulted in 8 products
    2. Source #2 through 4 resulted in 9 products each.
    3. Source #5 results in 6 product tables.

    At this time, only five product tables are generated for each source. While it appears to be working fine for the example data (age, gender), it doesn't seem to work properly for the actual data set. Ultimately, I won't be able to use this version given that the correct # of product tables has a greater priority than the analysis to determine which fields are missing.

    When reviewing the actual data set (products tables), I have 3 kind of "record types". They are as follows:
    Record Type #1:
    [SOURCE_FILE] → “EXTRA”
    [FIELDNAME_STANDARDIZED] → Null
    [EXTRA_DATA_TYPE] → Data type corresponding to field in [AFSAS_FIELDNAME]

    Record Type #2:
    [SOURCE_FILE] → {Source_1}, {Source_2}, {Source_3}, {Source_4}, {Source_5}
    [FIELDNAME_STANDARDIZED] → Value of legacy field
    [EXTRA_DATA_TYPE] → Null

    Record Type #3:
    [SOURCE_FILE] → {Source_1}, {Source_2}, {Source_3}, {Source_4}, {Source_5}
    [FIELDNAME_STANDARDIZED] → Null
    [EXTRA_DATA_TYPE] → Data type corresponding to field in [AFSAS_FIELDNAME]

    Did this help to further evaluate as to why the code might be dropping (i.e., not creating a subset of tables)?

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom, did you have a look at the error log (text file). In this version I added error trapping so instead of showing each one they are written to a log.

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I did... I couldn't really tell what's going on in the log. See attached ASCII.
    Attached Files Attached Files

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    But it is all there for you, you are trying to cram two fields into one:

    [BLS_BODY_PARTID_MMAC_Tier_1] as INJ_BODY_PART_TIER1_ID,[BLS_BODY_PARTID_Tier_1] as INJ_BODY_PART_TIER1_ID

    Looks like all the errors are similar and caused by mapping errors.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple Data Analysis
    By dbuckmas in forum Access
    Replies: 6
    Last Post: 01-18-2018, 01:42 PM
  2. Inspection Record and Data Analysis Database
    By cap.zadi in forum Access
    Replies: 4
    Last Post: 06-17-2017, 03:50 AM
  3. Data Analysis Direction
    By canyon289 in forum Access
    Replies: 6
    Last Post: 03-05-2012, 11:51 PM
  4. Table Data Analysis
    By bdf48 in forum Programming
    Replies: 1
    Last Post: 12-01-2011, 02:21 PM
  5. Need Help with Queries-Trade Analysis
    By nybanshee in forum Queries
    Replies: 0
    Last Post: 03-08-2008, 11:50 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