Results 1 to 6 of 6
  1. #1
    CurtisC is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    11

    report logistics

    I have a report that lists equipment used on a job. I have a field for each piece of equipment so 10 fields for 10 possible pieces of equipment.


    on the invoice I want only the fields that are populated to display. is there a way to filter this and subtotal for only the fields that are populated and if there are none to have a text box that says no equipment was used.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it's not too late, your design is flawed. The items used should be in a related table, just as items sold on an invoice would be in a related table. That way you can have 1 item or 100. You'd show the relationship with a form/subform or report/subreport. The concept is called normalization.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    2 tables: job table & JobEquip table
    the query for the report would join the 2 tables.

    on the report ,it would need a count of the equip needed.
    if the count = 0 ,show the NO EQUIP label.

    lblNoEquip.visible = txtEqCount=0

  4. #4
    CurtisC is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    11
    I do have a junction Table that links the equipment and the cost response tables in a many to many type relationship but just not sure what to do from there. i.e. what type of query and how to create the subform

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    paul is talking about this

    I have a field for each piece of equipment so 10 fields for 10 possible pieces of equipment.
    you need a table to detail jobs, another for equipment and a third which links the two together - so instead of 10 fields, you have up to 10 rows - but only for where equipment is allocated to the job

  6. #6
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    is there a way to filter this and subtotal for only the fields that are populated
    Specifically to this question there is the old Null to Zero function:
    Code:
    =Nz([Equip1Cost])+Nz([Equip2Cost])+Nz([Equip3Cost])

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

Similar Threads

  1. Replies: 16
    Last Post: 03-13-2018, 05:24 PM
  2. Replies: 9
    Last Post: 07-03-2017, 11:24 PM
  3. Replies: 4
    Last Post: 03-24-2012, 08:37 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