Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Parameter Query/Double Entry (Access 2003/SP 1)

    I have a parameter query that I use to retrieve records between 2 dates. For some reason it wants me to enter the dates twice. If I skip the first 2 parameter windows and enter them in the second set, it will return the data, but if I enter it in the first set and not the last it returns empty. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query/Double Entry (Access 2003/SP 1)

    Could you do the following?
    - Open the query in design view.
    - Select View | SQL.
    - Copy the text you see to the clipboard (Ctrl+C)
    - Paste it into a reply.
    Thanks in advance.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Parameter Query/Double Entry (Access 2003/SP 1

    Edited by HansV to correct problems with field name starting with "tr" (it is interpreted as a table row tag by the Lounge software)

    Here it is:
    SELECT [dataInt Tract Totals].Country, Sum([dataInt Tract Totals].<!t>[Tract Totals]) AS [SumOfTract Totals], Sum([dataInt Tract Totals].Packages) AS SumOfPackages, Sum([dataInt Tract Totals].[Letter Count]) AS [SumOfLetter Count], Sum([dataInt Tract Totals].Bibles) AS SumOfBibles, Sum([dataInt Tract Totals].[New Testaments]) AS [SumOfNew Testaments], Sum([dataInt Tract Totals].[Gospel of John]) AS [SumOfGospel of John], Sum([dataInt Tract Totals].Books) AS SumOfBooks, Sum([dataInt Tract Totals].SSQ) AS SumOfSSQ, Sum([dataInt Tract Totals].MOT) AS SumOfMOT
    FROM [dataInt Tract Totals]
    GROUP BY [dataInt Tract Totals].Country
    HAVING ((([dataInt Tract Totals].Country) In (SELECT [Country] FROM [dataInt Tract Totals] As Tmp GROUP BY [Country] HAVING Count(*)>0 )))
    ORDER BY Sum([dataInt Tract Totals].<!t>[Tract Totals]) DESC;

    I made this query probably 2 years ago when I was learning how to use Access. ( I still am) I have it referenced to a query that pulls specific data by date. The query it pulls data from (dataInt Tract Totals) pulls the data I want but I need to know the sum of each class by country and it shows the sum for every date, not by month. I know there's a solution and I can probably do it in 1 query, but haven't figured it out.

    Here is the SQL view of the original query.
    PARAMETERS [Enter beginning date] DateTime, [Enter ending date] DateTime;
    SELECT tblInternational_Orders.Country, tblInternational_Orders.<!t>[Tract Totals], tblInternational_Orders.Sent, tblInternational_Orders.Packages, tblInternational_Orders.[Letter Count], tblInternational_Orders.Date, tblInternational_Orders.Bibles, tblInternational_Orders.[New Testaments], tblInternational_Orders.[Gospel of John], tblInternational_Orders.Books, tblInternational_Orders.SSQ, tblInternational_Orders.MOT
    FROM tblInternational_Orders
    WHERE (((tblInternational_Orders.Country) In (SELECT [Country] FROM [tblInternational_orders] As Tmp GROUP BY [Country] HAVING Count(*)>0 )) AND ((tblInternational_Orders.Date) Between [Enter beginning date] And [Enter ending date]))
    ORDER BY tblInternational_Orders.<!t>[Tract Totals] DESC;

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query/Double Entry (Access 2003/SP 1

    If you want to group data by month, you can add two calculated columns to the original query:

    Y: Year([Date])

    and

    M: Month([Date])

    You can then make this query into a totals query, and set the Total option for the Date field to Where (this will automatically clear the Show check box for this column).

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Parameter Query/Double Entry (Access 2003/SP 1

    I understand the concept you are talking about, but I'm lost as to how to put it into my query. I've seen that in other sample databases but can't remember where. Is it something like this? Date: Format([Date],"mmmm")

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query/Double Entry (Access 2003/SP 1

    No, exactly what I wrote.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Parameter Query/Double Entry (Access 2003/SP 1

    That did the trick. Thanks a lot for your help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •