Results 1 to 10 of 10
  1. #1
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65

    Problem with query parameters

    Hi all,



    New to the forums. Thanks in advance for any help.

    I am currently having problems with a query that is working before I add a between date parameter. What the query is doing is counting the number of particular documents in database. What I need it to do is count the quantity of each different type of document over a given time frame that the user will enter. The user enters the dates from a custom form. Not sure if it isn't recognizing the input incorrectly or if I have possibly entered the parameters incorrectly.
    Code:
    Between [Forms]![frmProductTotals]![txtBeginDate] And [Forms]![frmProductTotals]![txtEndDate]
    Here is the attached SQL code for the query itself:

    Code:
    SELECT [%$##@_Alias].DocumentType, Count([%$##@_Alias].TrackingNo) AS CountOfTrackingNo
    FROM tblMain, (SELECT DISTINCT DocumentType, TrackingNo FROM DataProduct) AS [%$##@_Alias]
    GROUP BY [%$##@_Alias].DocumentType, tblMain.TrialDate
    Having (((tblMain.TrialDate) Between [Forms]![frmProductTotals]![txtBeginDate] And [Forms]![frmProductTotals]![txtEndDate]))

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    That is the correct syntax for dates. I use it all the time, it works.
    BUT try doing it in 2 queries....Q1 is the date select
    select * from table where dates...

    THEN Q2 uses Q1 to count.

  3. #3
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    Okay thanks, doing it that way didn't even cross my mind. I will give that a shot and see if that fixes the problem

  4. #4
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    I couldn't get the syntax or something right using 2 queries. However, I was able to get it working with just one query, but the counts for each individual product are coming out with ridiculous numbers that aren't even possible with the number of entries in the table.

    Here is the SQL code:
    Code:
    SELECT [ReportLog].DocumentType, Count([ReportLog].TrackingNo) AS CountOfTrackingNo
    FROM (SELECT DISTINCT DocumentType, TrackingNo From [ReportLog] AS [%$##@_Alias], tblMain INNER JOIN [ReportLog] ON tblMain.TrackingNo = [ReportLog].TrackingNo
    WHERE (((tblMain.TrialDate) Between [Forms]![frmProductTotals]![txtBeginDate] And [Forms]![frmProductTotals]![txtEndDate]))
    GROUP BY [ReportLog].DocumentType;
    Like I said the count numbers are in the tens and hundreds of thousands when they should be at most in the hundreds.

    Thanks

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Does your subquery return the correct records? If not, you may need to alter the join or include the DISTINCT or DISTINCT ROW predicate.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    I does return the correct types of products that are within in the time frame, it is just the count that is completely off

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then your subquery needs to be fixed first. Try altering the joins or the predicate.

  8. #8
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    I tried altering the joins and it didn't work. Not sure which predicate you are suggesting I change. If you could be a little more specific that would help a ton. I am capable in Access, but by no means an expert. I got this project at work because I was one of the few people with any significant Access experience. Thanks

  9. #9
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Your issue is in the from statement of the query.


    Code:
    SELECT DISTINCT DocumentType
     ,TrackingNo
    FROM [ReportLog] AS [%$##@_Alias]
     ,tblMain
    INNER JOIN [ReportLog]
     ON tblMain.TrackingNo = [ReportLog].TrackingNo
    WHERE (
      (
       (tblMain.TrialDate) BETWEEN [Forms] ! [frmProductTotals] ! [txtBeginDate]
        AND [Forms] ! [frmProductTotals] ! [txtEndDate]
       )
      )
    There is a cross join occurring. ReportLog occurs twice in your query and once it is not joined to anything--creating duplicate records for every entry that occurs in ReportLog. It is listed once as [%$##@_Alias] and once as [ReportLog].

    Rewrite this portion of the query as:


    Code:
    SELECT DISTINCT DocumentType
     ,TrackingNo
    FROM tblMain
    INNER JOIN [ReportLog]
     ON tblMain.TrackingNo = [ReportLog].TrackingNo
    WHERE (
      (
       (tblMain.TrialDate) BETWEEN [Forms] ! [frmProductTotals] ! [txtBeginDate]
        AND [Forms] ! [frmProductTotals] ! [txtEndDate]
       )
      )
    And ultimately reinsert it into your aggregation as:


    Code:
    SELECT DocumentType
     ,Count(TrackingNo) AS CountOfTrackingNo
    FROM (
     SELECT DISTINCT DocumentType
      ,TrackingNo
     FROM tblMain
     INNER JOIN [ReportLog]
      ON tblMain.TrackingNo = [ReportLog].TrackingNo
     WHERE (
       (
        (tblMain.TrialDate) BETWEEN [Forms] ! [frmProductTotals] ! [txtBeginDate]
         AND [Forms] ! [frmProductTotals] ! [txtEndDate]
        )
       )
     )
    GROUP BY DocumentType;
    Cheers,

    Jeff

  10. #10
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    Jeff,

    Thanks for all the help it is working like a charm now.

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

Similar Threads

  1. Particular Crosstab Query Problem (w/ parameters)
    By McArthurGDM in forum Access
    Replies: 14
    Last Post: 12-29-2014, 02:46 PM
  2. Parameters in a query?
    By Carol Geddes in forum Queries
    Replies: 5
    Last Post: 11-05-2013, 05:00 PM
  3. Problem about parameters in query
    By ryantam626 in forum Programming
    Replies: 5
    Last Post: 08-22-2012, 08:06 PM
  4. Replies: 14
    Last Post: 03-07-2012, 03:46 AM
  5. query help with parameters
    By Madmax in forum Access
    Replies: 2
    Last Post: 03-05-2012, 03:07 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