Results 1 to 9 of 9
  1. #1
    aero is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    12

    Macro and Submacro to query and display a table of 4 queries based on 1 form

    Hello,
    I'm relatively new to Access and am trying to work on a macro to work as follows. I'm a novice programmer but don't know VBA or SQL yet and am trying to do the following:

    User form has two combo boxes-

    1) Reporting Period-not a key


    2) Company ID-not a key

    After the selection has been made for both comboboxes, I would like to run a query on the selected survey period, then re-run this query on the reporting period offset by -1 (the reporting period directly before the one selected in the dropdown list using listindex). I would like to use a for loop to rerun this query 3 time such that in total I receive four reporting periods. Finally I would like to display a joined table of these reports within a subform in datasheet view:

    Report Period Selection Report Period-1 Report Period-2 Report Period -3
    Product 1 . . . .
    Product 2 . . . .
    Product 3 . . . .

    I know this is super easy, but I haven't figured out the syntax for VBA or Expression builder in Access 2010 yet. Can anyone help? Thanks!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    how are you storing your reporting period? if it's a date (or a date ending) you can pick the top four periods through a query then select all records relevant to those surveys.

    For instance if your reporting period is actually stored as a start and end date this would be relatively simple

    You shouldn't need to re-run the reports for consecutive periods at all, just run one report that covers all four, inserting page breaks between periods.

  3. #3
    aero is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    12
    The date is a column in the table I am calling the query from formatted "MMDDYY"

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    that doesn't really answer the question, how is it being STORED, is it a text field following string formatting, is it being stored as date/time? a number?

    if it's a string you can convert it to a date like this:

    ConvertedDate : dateserial(right([datefield],2), left([datefield], 2), mid([datefield, 3, 2))

    Then you could sort your query by the converted date and use the TOP function to select the top 4 items to report on.

  5. #5
    aero is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    12
    Top won't work in this instance unless I use some sort of count. Each recrod has

    Report Period Text-selected from combobox
    Company ID Text-this one is selected from combobox
    Product Code-Text
    Location code-text
    Quantity-number

    So for each Company and each period there are varying counts of product codes under various locations
    I want a report/datasheet view to display the quantity in each location code for the first selected week, then join that with a column containing the matching data for the previous week, and so on so that I have 1 full month back displayed (left to right) Does that make sense? Top would work if there was 1 record for each report period but there are a variable number of records for each survey period and each company id.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Your original problem was to find the most recent four periods for a specific company. A query with the TOP 4 will produce that list. you can then use THAT list to drive the rest of your report, again, inserting page breaks between periods to keep them separate, instead of using code to cycle through the four most recent. Still don't know how you're storing the periodID, I'm assuming a text value but it's still not clear.

  7. #7
    aero is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    12
    I put that the report periods are stored as text in my latest post. I may not be clear in the description of the problem. I want to query the four previous periods relative to one selected in a drop down. Not the four most recent.,

  8. #8
    aero is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    12
    Accidently posted a previous reply earlier.
    Last edited by aero; 06-23-2014 at 12:07 PM. Reason: Accidently posted a previous reply earlier.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    how about a sample database because I don't think we're communicating well :P

    Regardless of which period you choose, you want to report on the one chosen, plus the three most recent PRIOR to that chosen one. It's the exact same problem I've given a solution to, it's just a matter of then linking the 'top four' back to the original data.

    If you want to supply a sample database just make copy of what you have (just the table(s) and the form we're discussing) and put some garbage data in there to simulate your real data , then compact/repair, zip it up and upload it to this website and I can have a look.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-18-2014, 10:40 AM
  2. Replies: 6
    Last Post: 11-13-2013, 04:17 PM
  3. Replies: 3
    Last Post: 03-11-2013, 05:11 PM
  4. RunMacro cannot find submacro object.
    By weasel7711 in forum Programming
    Replies: 2
    Last Post: 09-05-2012, 01:16 PM
  5. Replies: 11
    Last Post: 11-07-2011, 11:29 AM

Tags for this Thread

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