Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple reports (2000-SR1)

    I have a series of reports that are very similar, with only one difference between them: the customer may have ordered Item 1, or Item 2 or Item 3, etc. The reports are a list of the names and contact info for each person ordering Item 1, each person ordering Item 2, etc. Currently, they are each based on separate queries that are identical except for that one field for the item selected (each of those are a "yes/no" field).
    As it is now, I need to select each report separately to view or print or email...would it be better to have a form to select which reports are needed and code to then generate each report with the right variable selected? It would be cleaner, it seems, to not have so many nearly identical queries and nearly identical reports.

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

    Re: Multiple reports (2000-SR1)

    Can you provide more info about the structure of the table(s) and the queries?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple reports (2000-SR1)

    Wow; you might want to consider revamping the design to get rid of separate fields for each product (unless, of course, you've only got 3 products, then it's not too bad, but still ill-advised). Otherwise, a combo box (or option group) and command button with a case statement might do the trick.

    Example:

    Private Sub cmdButton_Click()
    Select Case Me.cboReportNumber 'assumes your combo box is named cboReportNumber
    Case "Report1"
    DoCmd.OpenReport "Report1"
    Case "Report2"
    DoCmd.OpenReport "Report2"
    Case "Report3"
    DoCmd.OpenReport "Report3"
    Case Else
    MsgBox "Pick a report, dummy!"
    End Select
    End Sub

    The text in quotation marks
    Case "<span style="background-color: #FFFF00; color: #000000; font-weight: bold">Report1</span hi>" denotes options selectable from your combo box.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple reports (2000-SR1)

    The

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple reports (2000-SR1)

    I don't doubt that the structure could use some serious revising. Unfortunately, this project grew on me as the user kept adding on what she "needed" (read, wanted). I'm not sure how best to modify the structure, open to suggestions.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Multiple reports (2000-SR1)

    Depending on how much work you have done versus how much work has to be done, I would split that table now.

    I recently took over a database that had 50 times 3 field in a table, that had to be normalised. I don't know how many queries I got rid of, but it was over 100.

    These kinds of databases are a nightmare to change in the future.

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

    Re: Multiple reports (2000-SR1)

    As NYIntensiity and patt have already suggested, you should change the structure. You should split the Orders table into two: the Orders table should only contain general info about the order, and the OrderDetails table info about items. The OrderDetails would contain a separate record for each ordered item.

    See the Northwind.mdb sample database that comes with Access - it contains a structure similar to what you need.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple reports (2000-SR1)

    Just to expand on what Hans has said, the "general information" he's referring to about orders would be the Customer Name, addresses, etc, each with it's own primary key, and the orders specific information would contain the item, quantity, color, etc., linked to the customer's primary key. You can set up this relation in the relationships window, so you don't have to make the relationship every time you create a query.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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