Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2009
    Location
    Atlanta
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is my first attempt at building a query from list boxes. I have a form that has three list boxes. The first list box is generated from a table and contains years (lstFY). The second list box is also generated from a table and contains months (lstMonth). The third list box is a value list of three boxes (lstProgram). From these selections I'd like to make a query using four tables.
    1. tblGL - this contains the data I'd like to query
    2. tblFY - this contains the Fiscal Year and is linked to tblGL. This table also generates lstFY
    3. tblMonth - this contains the month and is linked to tblGL. This table also generates lstMonth
    4. tblBudgetDetail - this is linked to tblGL, but list box is refering to tblBudgetApprop. tblBudgetApprop is linked to tblBudgetOrg which is linked to tblBudgetDetail. I want the query to show tblBudgetDetail.Description based on the list box selection from tblBudgetApprop.Description.

    The query should be:
    tblFY.FY; tblMonth.month; tblBudgetApprop.Description; tblBudgetDetail.Description; tblGL.Amount

    The first three tables should be have the parameters of the list box. The tbl.amount should be summed by, the rest group by. Finally I'd like tblFY decending and tblMonth ascending.

    I appreciate any help. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    You can refer to a list box on a form in the Criteria line of a query like this:

    [Forms]![NameOfTheForm]![NameOfTheListbox]

    with the appropriate names substituted.

    It's difficult to give more specific help without knowing how the tables are linked to each other (i.e. on which fields).

  3. #3
    New Lounger
    Join Date
    Apr 2009
    Location
    Atlanta
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ideally the user would not got to the query, but upon clicking ok on the form the results would show. The code or event procedure for "on click" would run the query.
    The ideal query is attached as a screen shot in MS Word.
    Again thanks for the help.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't need the FY and Period tables in the query - you can use the FY and Per fields from the 'GL thru Feb 09' table, and make them lookup fields in the query or use combo boxes in a form/report based on the query to display the corresponding fields from the FY and Period tables.

    All three list boxes should use the key field from their source table as hidden first column; this column will be the bound column. So even while the third list box displays descriptions, its value will correspond to the Child field in 'Org Code Budget Appropriation'.

    You can use expressions such as I proposed in my first reply in the Criteria row of the query.

Posting Permissions

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