Results 1 to 13 of 13
  1. #1
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26

    Multitable dynamic query

    Afternoon everyone!

    I may not be phrasing this right, but I cannot for the life of me get this to work.

    I have a multi-table database, small (less than 2k records) but is growing. We have multiple queries for different data sets and purposes, but I'm trying to create an all encompassing query that let's a user search for anything they want.

    For example, they could plug in a brand, and it pulls all data for that brand. Or a batch number, and it pulls all data for that batch. Or a date, and it pulls all activity for that date.

    Is this feasible? I'd rather allow users to query what they need rather than have me build a query for them.



    Again, I may not be phrasing this right.

    Thoughts?

    Jeremy

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Running a search in all fields is likely to be slow and inefficient. Not recommended
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    with such a wide range of requirements, think you will struggle. You could base a query on your relationships as a starting point, then in a form have a dropdown to select a field from all the fields and a textbox to enter a value.

    Set the dropdown rowsource to your big query and the rowsource type to field list. Then you would need code to create your filter - bearing in mind you have number, date and text datatypes.

    But it will get messy - what if they want to know what happened on this day or that day - or this day and that day or between a range of dates? What is the logic between say a table of invoices and purchase orders? Or they are looking for something that contains only part of a value. Or they want to know a combination of things or where something does not equal the value they enter? Basically you will need to train your users how to write a sql query.

    You would probably be better just to have your large query in a datasheet form and allow them to filter as required from the column headings

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Concur fully with ajax ...
    but if you want to see an example database of mutliple field searching done by an expert (Allen Browne), see this link
    http://allenbrowne.com/ser-62.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Quote Originally Posted by Ajax View Post
    with such a wide range of requirements, think you will struggle. You could base a query on your relationships as a starting point, then in a form have a dropdown to select a field from all the fields and a textbox to enter a value.

    Set the dropdown rowsource to your big query and the rowsource type to field list. Then you would need code to create your filter - bearing in mind you have number, date and text datatypes.

    But it will get messy - what if they want to know what happened on this day or that day - or this day and that day or between a range of dates? What is the logic between say a table of invoices and purchase orders? Or they are looking for something that contains only part of a value. Or they want to know a combination of things or where something does not equal the value they enter? Basically you will need to train your users how to write a sql query.

    You would probably be better just to have your large query in a datasheet form and allow them to filter as required from the column headings

    I sort of knew it would be something that would just be too big, messy, and cumbersome.

    So let me elaborate and see what you all think.

    The database is for quality data from a lab at the brewery I work for.

    We have different sets of data that each lab tech records, and then posts it to their respective table. Let's call the tables A, B, and C.

    If a user wants to pull data for a specific batch of beer, with some data from each table - what would be the easiest route? In other words, I want to pull data on beer batch 123, with some info from Table A, B, and C.


    Does that make sense? I've got queries built for each table, but the request is for a generic query that will allow a user to pull any data they see fit.

    Thoughts?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You are back to my suggestion - but at least only 3 tables. However your elaboration doesn't explain any more than your original request except to put a label on one field and one table

  7. #7
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Thanks all.

    I'm talking myself in circles it seems.

    Ajax, to answer your point yes - I think it will get too messy.

    I have 11 tables worth of data all linked via a batch number. They all have unique data points.

    Ideally, I want to build a query where the user can select their batch number, and whatever other data points they want. Be this as a parameter entry, or a dropdown etc.

    BUT, it looks to just be too much.

    Currently, I have queries built for each table, that the user can then export as suggested. This may be the best route.

    Any other thoughts?

    I hope I was a little more clear.

    Click image for larger version. 

Name:	Capture.jpg 
Views:	13 
Size:	72.1 KB 
ID:	33766

  8. #8
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Holy table mess Batman

    I would guess from all the repeated fields (Brand, Legacy Number, FV# etc) in those tables your data is not normalised, and as a result your queries are turning into unfathomable mushes.
    I don't understand the use of Batch number in every table, if that data really is always related it belongs in the same table.

    You should have a master table for the batch, that has all the unique things for that batch in it.
    Any data that can have multiple related versions of an entity should be in another table with the Batch number as the Foreign Key, not the primary key.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Quote Originally Posted by Minty View Post
    Holy table mess Batman

    I would guess from all the repeated fields (Brand, Legacy Number, FV# etc) in those tables your data is not normalised, and as a result your queries are turning into unfathomable mushes.
    I don't understand the use of Batch number in every table, if that data really is always related it belongs in the same table.

    You should have a master table for the batch, that has all the unique things for that batch in it.
    Any data that can have multiple related versions of an entity should be in another table with the Batch number as the Foreign Key, not the primary key.

    Yeah....big mess I know. I didn't build the tables - I'm cleaning it up and trying to implement.

    So if I create a master batch number table, why wouldn't the batch number be primary? That's the common entity across all tables.

    Master table = Batch num, legacy num, FV#, Brand, Yeast Type. Other than that every table has unique data.

  10. #10
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Look better?

    Attachment 33767

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your attachment doesn't work (for me anyway)

    it may be unique data, but not necessarily unique data types

    blended batch number appears in many tables - should be in a table of its own with another field to indicate Gyl Grav/Packacking etc

    a similar principle for Diacetl - should be a table with date, passcount and value

  12. #12
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Quote Originally Posted by Ajax View Post
    your attachment doesn't work (for me anyway)

    it may be unique data, but not necessarily unique data types

    blended batch number appears in many tables - should be in a table of its own with another field to indicate Gyl Grav/Packacking etc

    a similar principle for Diacetl - should be a table with date, passcount and value

    Try this

    Click image for larger version. 

Name:	Capture.JPG 
Views:	12 
Size:	116.5 KB 
ID:	33770

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Looking at your relationships window, only one of your tables (BatchRepository) contains a primary key (PK) field.
    PK fields are essential in (almost) all tables to ensure records are unique and certain actions such as update queries depend on these so Access can identify which records to update
    Personally I use them in all tables

    The obvious PK field is BatchNumber though its very unusual to have the same field in EVERY table.
    Ajax has already suggested some changes to table structure

    Similarly a search will not work reliably unless all records have a PK field.
    As previously stated trying to search an entire database will be slow & inefficient

    It is also unrealistic to expect users to build the query they need from a generic query

    Instead I recommend using a form with a number of combo boxes for users to select the criteria they need
    e.g. BatchNumber, Yeast type, brand, date range etc.
    You would then use code to build filter criteria based on all selected fields with the values or range of values required
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 7
    Last Post: 05-11-2016, 05:08 AM
  2. Autonumber in multitable form
    By Fairlane in forum Forms
    Replies: 5
    Last Post: 08-29-2013, 06:15 PM
  3. Dynamic Form, Dynamic labels/ captions?
    By JFo in forum Programming
    Replies: 15
    Last Post: 10-12-2011, 08:33 PM
  4. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  5. Dynamic SQL Query
    By Squeaner in forum Queries
    Replies: 0
    Last Post: 09-25-2008, 02:37 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