Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Question (A2K)

    Good thing I don
    Cheers,
    Andy

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    You should try to establish some relationships between your tables at the outset. I have put a 1 to Many relationship from your Master, Issued, Received & Returned Tables. Try the Sum function to get a subtotal for each criteria and combine them (See the SumQuery in the attached database.
    Easy Access

  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: Query Question (A2K)

    Andy, what is your end goal? I understand you wish to list everything in your inventory - but what then? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    ____________________________
    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
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    Nasir,

    thanks most kindly for looking into my problem. At first glance, you have upon the head of the nail, hit. As soon as I get back to my office, I'll digest this in detail and get back to you with I hope not too many questions,
    Cheers,
    Andy

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    Jeremy,

    that's a really good question, embarassing, but good. Seriously, this is all leading back at some point in time to my convoluted Inventory scenario. The light will be shown and all will make sense. Or else I'll take up lighthouse keeping. The bonus is that also learned some neat stuff from Nasir's reply posting.
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    Good Sunshiny Morning Nasir!

    Thank you again for your guidance. The end result of what I was attempting to do can be seen in the report rpt Inventory - Master - Summary. I diddled with the relationships within the queries in order to be able to display the records in the fashion that I perceived to be correct. You might want to have a look at those and let me know if I did it correctly or not.

    The next thing I have to do with this report, and I'm obviously open to suggestions, is how not to print a line where there is no data available for the part number.

    All of this is leading, as I mentioned to Jeremy, to the big Inventory Calculation ( Average Costing ) in the Sky! I'd also want to mention your demo on how to set up these types of relationships has been invaluable to me and will definetly be used in other applications.

    I hope the over the shoulder programmers get as much benefit out of this little gem as I have,
    Cheers,
    Andy

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

    Re: Query Question (A2K)

    1) Where have the relationships created by EasyAccess gone? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    2) You don't need group headers and footers in your report. You can move the controls from the group header into the detail section. You can delete the second grouping level altogether, and turn off Group Header and Group Footer for the first one (after moving the controls).
    3) You can use the On Format event of the detail section to suppress it if there are no data:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If IsNull(Me.SumOfInv_Received_Qty) And _
    IsNull(Me.SumOfInv_Returned_Qty) And _
    IsNull(Me.SumOfInv_Issued_Qty) And _
    IsNull(Me.Inv_Mst_QtyAllocated) Then
    Cancel = True
    End If
    End Sub

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    Good Morning to you too Hans,

    I renamed EasyAccess's query names and made relationship changes as I mentioned to get the output I needed. I don't think I messed up. His are still viewable in his download. I'm going to try your suggestions right now. Thanks again for your help,
    Cheers,
    Andy

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    Hans,

    your code worked perfectly once I replaced the DataField names you used with the text box name for the appropriate fields, like

    If IsNull(Me.txtReceived) And _
    IsNull(Me.txtReturned) And _
    IsNull(Me.txtIssued) And _
    IsNull(Me.txtAllocated) Then
    Cancel = True
    End If

    The reason for the way that the report is setup is that once I got this portion working correctly, I was going to add subReports in the detail section. I've already tried this and it appears to be working.

    While I'm in the neighbourhood, the following code works when I want to bring in data from the previous record when working on a form.

    =DlookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Forms![frm Inventory - 03 - Received]![subFormInput]![Inv_Received_SysKey]-1")

    Is there equivalent coding that would bring code forward on a report DETAIL line from the previous DETAIL line? The source data portion prior to the = sign is the same but I would want to show it in my sub report rpt Inventory - Master - Summary
    Cheers,
    Andy

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

    Re: Query Question (A2K)

    Try replacing

    Forms![frm Inventory - 03 - Received]![subFormInput]![Inv_Received_SysKey]

    with

    Reports![rpt Inventory - Master - Summary]![rpt Inventory - Master - Summary - SubReceipts]![Inv_Received_SysKey]

    perhaps with the text box name instead of the field name at the end.

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    Hans,

    Using your example I tried the following with no success,

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports![rpt Inventory - Master - Summary]![rpt Inventory - Master - Summary - SubReceipts]![Inv_Received_SysKey]-1")

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports![rpt Inventory - Master - Summary]![subRepReceipts]![Inv_Received_SysKey]-1")

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports![rpt Inventory - Master - Summary]![rpt Inventory - Master - Summary - SubReceipts]![txtSysKey]-1")

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports![rpt Inventory - Master - Summary]![subRepReceipts]![txtSysKey]-1")

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports![rpt Inventory - Master - Summary]![rpt Inventory - Master - Summary - SubReceipts]![Inv_Received_SysKey]")

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports![rpt Inventory - Master - Summary]![rpt Inventory - Master - Summary - SubReceipts]![Inv_Received_SysKey]")

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports![rpt Inventory - Master - Summary]![rpt Inventory - Master - Summary - SubReceipts]![txtSysKey]")

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports![rpt Inventory - Master - Summary]![subRepReceipts]![txtSysKey]")

    Must be something in the way I'm interpreting your code,
    Cheers,
    Andy

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

    Re: Query Question (A2K)

    It would be best if you posted a version of your database with the report + subreport.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Question (A2K)

    I hope you've got a few typos in what you typed into the post. Otherwise, your code has some missing braces [ ] that will prevent your DLookups from working, i.e.:

    =DLookUp("[Inv_Received_Qty]","qry Inventory - Received","[Inv_Received_SysKey]=Reports<font color=red>]<!/t></font color=red>![rpt Inventory - Master - Summary]![rpt Inventory - Master - Summary - SubReceipts]![Inv_Received_SysKey]-1")
    Charlotte

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    Hans,

    Absolutely, will do so after lunch as I have to be out for while.
    Cheers,
    Andy

  15. #15
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (A2K)

    Thanks for popping in Charlotte. I'll double check my stuff when I get back,
    Cheers,
    Andy

Page 1 of 2 12 LastLast

Posting Permissions

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