Results 1 to 11 of 11
  1. #1
    swngdncr is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    6

    query to find records in different tables within same date range

    I have a DB with tables for different activities, such as Education Events and Projects Completed. I need a query that will find all the records from these different tables within a date range entered by the user without having to enter the date range multiple times. So, I need to generate a report that lists, for instance, all the supplies purchased, the people contacted, the projects completed between DateA and DateB that the user can enter when they generate the report. The problem is that there is no other relationship between these different tables. So, when I do a query with two tables, it lists everything multiple times, even if I select "unique records" in the property sheet. It has been a long time since I did any DB development, and at that I learned late in life, so it is coming back slowly. Suggestions appreciated.

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    It sounds like you may have a normalization issue with your initial design. You may want to reconsider your design. Look at this link on db design. Once you normalize your data, then it will minimize your issues.

    http://r937.com/relational.html

  3. #3
    swngdncr is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    6
    Thanks, but I don't think this is the problem. I tried to simplify my database description to focus it down to the key issue. I think what I am going to need is 2 global variable like "VarStartDate" and "VarEndDate", and maybe it is a form that they click on to set those variables. Then in my query, the criteria is set to "VarStartDate" & "VarEndDate".

    Quote Originally Posted by alansidman View Post
    It sounds like you may have a normalization issue with your initial design. You may want to reconsider your design. Look at this link on db design. Once you normalize your data, then it will minimize your issues.

    http://r937.com/relational.html

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would agree that there could be a design problem. If you wish to continue with it, you can't use variables directly in a query. You can create public functions that return the value of each variable, use TempVars instead of variables, or perhaps most simply is use a form to gather the user input and point the queries there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    swngdncr is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    6
    Alan and Paul, on second thought, yes, I think you are right... I don't know why it is that I always have to walk away from it before the light bulb comes on... Got to process this a little more later tonight or tomorrow... Its just been so long since I did DB development...

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think you'll be happier in the long run. Allan was happy to help and I was happy to pile on.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Dittos

    Alan

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oops, sorry for the 2 l's Alan.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Two ll's are better than no ll's

  10. #10
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    While the other respondents may be right on improving your design, you can do this. The limitation is that your columns output from each table source must be identically named. Here's a simple example:

    http://accessdatabasetutorial.com/20...g-union-query/

    Best of luck,


    Jeff

  11. #11
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    I built something for you. I hope it works like you want: DB_Swngdncr.accdb

    The first thing that popped into my mind was a UNION query. So I built 2 mock tables called Education Events and Projects Completed and populated them with some data. Then, I made a Union Query that joins them into a single table - event hought they have no relationship. The thing about a UNION though is that you have to have the exact same number of columns.

    Step 1) Click on the Query I called "UnionQueryExample". That should show you what combining 2 tables looks like. Examine the SQL and duplicate it for each other table in your project. It should be pretty templated no brainer process. I think you can UNION as many tables as you want, but I'm not sure.

    Step 2) Next I added the Parameter for a date range. I had to make another query for that . No worries, simply click on the Query I called "ParameterQueryByDateRange" and enter in your parameters.

    ***I hope thus far it works exactly as you intended and you can just template this to fit your needs. But for fun I continued and made a Form/Report. If you don't like it, delete.

    Step 3) Click the Form I called "View Activities" and enter your date range and press the button. The resulting report may look a little nicer. Feel free to play with the "Format" contextual tab all the way on the right in the ribbon to further adjust color, size, etc.

    I hope all this helps. I learned a little about Union Queries myself.
    Best of luck to you Sir!

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

Similar Threads

  1. Find Avg based on Date Range
    By mpaulbattle in forum Queries
    Replies: 6
    Last Post: 11-22-2016, 03:06 PM
  2. Replies: 3
    Last Post: 02-26-2016, 01:03 PM
  3. Replies: 4
    Last Post: 04-16-2015, 05:01 AM
  4. Replies: 4
    Last Post: 07-18-2013, 03:14 AM
  5. Find Min and Max in a selected date range
    By rkalapura in forum Queries
    Replies: 9
    Last Post: 04-01-2013, 09:24 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