Results 1 to 8 of 8
  1. #1
    0REDSOX7 is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    6

    Question View Data based on Form Input?

    Hi all,

    New to the forums as far as posting but been a long time reader. Im relatively new to working with Access on the design side - used to using forms and reports already made...

    So here is my question...I can't figure it out (and am not 100% up to par on VB Coding)...

    I have a form that will open when the database is open. It asks for a date range and then will display the data based on a query.

    What I would like to do is have a button that would spit out the raw data that the form is compiled of using the data range already entered by the user.

    So, for example... the form will list the below...

    Number of transactions: X
    Average amount of transactions: $XXX
    Amount of Discount: $XXX
    Transaction Date: XX/XX/XXXX

    Now, what I want is a button that will display each of the transactions that the form is compiled of, but have no clue how to do it. I have a duplicate query that will list the data without any grouping, but the user still has to enter the date range again, which I am trying to avoid.



    Is this even possible?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As a start I would suggest opening the form in Design view, and see what the Recordsource of the Form is.

    Open form in design view, look at Form's properties, then Data tab, then record source.

  3. #3
    0REDSOX7 is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    6
    The recordsource is the Query that I built that will provide the "overview" of the form, which is correct...the number of transactions, date, average price, average discount, etc.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Does the query have the Date parameters ( asking for Date range)?
    Can you copy and paste the query SQL here?

  5. #5
    0REDSOX7 is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    6
    Sure, it is as follows...

    SELECT Count([OG Request Data].Acuitive_Reference_Number) AS CountOfAcuitive_Reference_Number, Sum([OG Request Data].Total_Air_Cost) AS SumOfTotal_Air_Cost, Avg([OG Request Data].Air_CPU) AS AvgOfAir_CPU, Avg([OG Request Data].Air_Transit_Time) AS AvgOfAir_Transit_Time, Sum([OG Request Data].Total_Alternative_Cost) AS SumOfTotal_Alternative_Cost, Avg([OG Request Data].AA_CPU) AS AvgOfAA_CPU, Avg([OG Request Data].AA_Transit_Time) AS AvgOfAA_Transit_Time, Sum([OG Request Data].Total_Freight_Savings) AS SumOfTotal_Freight_Savings, Avg([OG Request Data].Total_Freight_Savings) AS AvgOfTotal_Freight_Savings, Avg([OG Request Data].Savings_Per_Unit) AS AvgOfSavings_Per_Unit
    FROM [OG Request Data]
    WHERE ((([OG Request Data].Date_Requested) Between [Enter Start Date:] And [Enter End Date:]));

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What you have is an aggregate query that shows Counts, Sums and Averages.

    The raw data that preceded this query exists somewhere in your system.
    It would appears that all raw data resides in Table OG Request Data and the data involved in your aggregate query is constrained to those records that are ON or Between [the Start Date] and [End Date] that you supply.

    You could try this query

    Parameters [StartDate] Date, [EndDate] Date;
    SELECT Acuitive_Reference_Number
    ,Total_Air_Cost
    ,Air_CPU
    ,Air_Transit_Time
    ,Total_Alternative_Cost
    ,AA_CPU
    ,AA_Transit_Time
    ,Total_Freight_Savings
    ,Savings_Per_Unit
    FROM [OG Request Data]
    WHERE Date_Requested Between StartDate and EndDate;



    You would open a new query in design view, go to sql View,
    and paste this query.

    Since it is Read only it will NOT affect existing data.
    Give it a try and post back if there are issues.

  7. #7
    0REDSOX7 is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    6
    Ahhh, perfect. I manipulated it just a bit but it works just as I want it to now! Thank you so much!

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Glad you have it working.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-13-2011, 08:01 AM
  2. Run a query based on an input from a form
    By apoorv in forum Queries
    Replies: 4
    Last Post: 07-11-2011, 01:39 PM
  3. Replies: 2
    Last Post: 06-01-2011, 09:32 AM
  4. Replies: 2
    Last Post: 06-17-2010, 04:15 PM
  5. Replies: 1
    Last Post: 06-14-2010, 02:31 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