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

    list boxes in reporting

    I have a report where a user entering a call can enter all the equipment used in a field called equipment used using a multiselect list box. When reporting I would like to list these preferably in columns only listing those that have been selected and possibly in a text box that expands when needed for large lists and contracts on smaller lists.



    The field is titled Equipment used and the listbox is populated from a table of the equipment which houses about 25 items but new equipment may be added. HELP

  2. #2
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,442
    Is the "equipment" field a multi-value field
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,126
    Based on my interpretation of your post, you table(s) design is not normalized. You don't add data across in fields (columns) you do it down in records (rows). Sounds like you have used your Excel brain to design your db. If so, it will only continue to plague you with problems. You probably ought to have a table for equipment, which the listbox is populated from, but the rest is a guess. If it's equipment for a job for example, then you'd have a table with everything related to the job details (that does not include equipment and the like) and then a junction table that relates the job data to the equipment used on it. Normalization needs to be the #1 aspect that you get right in a relational database. #2 is normalization. #3 is normalization. I think by now you get my drift!
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    CurtisC is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    11
    yes it is a multi select list box so that they can choose all equipment used on a form it is populates into the field on the related table. i am creating the report for this worksheet.

  5. #5
    CurtisC is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    11
    i have a good basis in Excel you are correct. so this leads to confusion on many facets making this database. each records in teh table is related to a single callout where multiple units(personnel) is used and multiple equipment is used. for the personnel and equpment fields I have used a multiselect list box on the form for user input. this creates a list in the field separated by commas, which could work for what i want it to but I want to for the personnel however for the equipment i needs to list each on its own line for the report so that I can perform calculations based on the cost per unit/hour.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,126
    I thought so. Most seasoned developers would not use a multi value fields, nor lookup fields in tables & other things/methods. So why is it there, you ask? Good question. I see it as a shiny, low hanging bobble on the Christmas tree. Looks nice and is easy to reach so why would you not settle for it? On the other hand, if you knew more or less what you wanted to end up with but had no clue how to do it, you'd research and hopefully learn to do it the right way. Some will say that multi value fields are an indicator that a db is not fully normalized (if at all). Others argue that it really is - it's just that the normalization is represented by hidden system tables that actually store the values you think you're looking at. Regardless, the real values are not stored where you see them, which inevitably starts presenting problems when you want to query data or make reports. If I was Micro$oft I might be on the side of mvf's because it's a crutch that rather than frustrate you, makes it more likely that you'll enjoy using my product.

    One issue that you might find wrt mvf's is that the likes of me on this forum don't know a whole lot about them. What I know comes from trying to help others work around the problems they induce.

    Two examples of how to approach a situation"
    1) a table of sales orders with all the info about the sale + a mvf to store x out of y possible products that they ordered.
    2) a table of sales orders and a table of order items with all the ordered items in records (rows). tblOrders is linked to tblOrderDetails by the fact that the primary key of orders is found in order details as a foreign key and they are related/linked. Therefore any order ID can be found in order details, which will tell you everything about the order (who, when, etc) plus what.

    If you must stay the present course, we can try to help you with what you've got but I'd strongly recommend you research normalization and follow those principles.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

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

Similar Threads

  1. Replies: 15
    Last Post: 11-20-2013, 04:30 PM
  2. Cascading List Boxes
    By Gee in forum Access
    Replies: 13
    Last Post: 02-07-2013, 01:16 PM
  3. Combo/List Boxes
    By refryguy in forum Forms
    Replies: 3
    Last Post: 07-28-2011, 01:34 PM
  4. List Boxes.
    By Willtc in forum Programming
    Replies: 2
    Last Post: 02-12-2010, 04:12 AM
  5. List box to populate other list boxes
    By Nathan in forum Forms
    Replies: 0
    Last Post: 03-03-2009, 07:22 PM

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 - Senior Forums