Results 1 to 10 of 10
  1. #1
    AccessForumUser is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    19

    Help with Summary and Organizing Multiple Table Columns into a Report.

    Hello, I've added a small portion of a database i'm building, and am wondering if someone could help me out with a report i'm trying to create.
    Based on a date range search, I'm trying to take 3 input fields from the main input form and combine them into two different results to show in a report.


    - See the attachment for the database example..

    For the first result, I'd like to show a number for how many times a tech has added his/her badge to a case..
    for example:
    badge 123 - 3 instances (they have populated all 3 fields in a case)
    badge 456 - 2 instances


    The second result would show a summary of how much work was done during the date range search for all techs.. So it would just add all 3 fields up in every case if it fell between the date range search, and display a total number.. so in my case, there are 2 records in my attached database and all 3 fields have been populated on both records.. the total would be 6 if the date range search fell between those 2 records.

    I hope i'm not sounding too confusing here.. when you see the database, it should all make sense hopefully.
    I've tried using a subform inside my main form, but the techs were able to break it pretty quick and corrupt the database! lol..
    I'd like to keep the input fields as they are to make it simple, but i can't seem to figure out how to add the columns together to get what I want..

    I tried a cross tab query but have a feeling my code is bad because i could not get it working properly.. i'm wondering if a sum(iif is needed somewhere?

    I created a query but have only referenced the date range search in it.. and the report is empty as well..

    Thanks!!
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have "Committed Spreadsheet".... this means you have design your table like a spread sheet.
    Spreadsheets are Short and Wide.
    Access tables are Tall and Narrow.

    Your table design should/might be:
    tbl_Main
    ------------------
    MainID_PK (autonumber) (PK field)
    TechBadge (Text - 10)
    CaseNumber (Text - 50)
    CaseDate (Date/Time)
    DateCreated (Date/Time)
    Comments (Text - 255)


    Or you might have a main table and a sub table.
    I don't know what you are trying to do and don't have enough info the suggest more.


    You could keep your current table structure, but I think it will take some VBA code to accomplish your goal.

  3. #3
    AccessForumUser is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    19
    Quote Originally Posted by ssanfu View Post
    You have "Committed Spreadsheet".... this means you have design your table like a spread sheet.
    Spreadsheets are Short and Wide.
    Access tables are Tall and Narrow.

    Your table design should/might be:
    tbl_Main
    ------------------
    MainID_PK (autonumber) (PK field)
    TechBadge (Text - 10)
    CaseNumber (Text - 50)
    CaseDate (Date/Time)
    DateCreated (Date/Time)
    Comments (Text - 255)


    Or you might have a main table and a sub table.
    I don't know what you are trying to do and don't have enough info the suggest more.


    You could keep your current table structure, but I think it will take some VBA code to accomplish your goal.
    Thanks ssanfu. I have purposely created the database in this way. I'm not sure what you mean by table design.. the table design in the example is setup exactly how you mentioned it above..

    When i do a date range search and click the badge report button, i'd like a query or code to take all the badges and summarize them to a number.. so for example tech 123 happened to populated 4 input fields in the date range searched.. and tech 456 populated 2 fields..
    The report would show the below output:
    123 - 4
    456 - 2

    secondly, i'd like to add the 3 input fields together (if populated) so I know how much work was done in a date range.. and represent it by a number..
    In my database example, all 3 fields were populated with the two records, so i would like the Report to show an output of 6.. it's just adding the fields together and giving an output.

    I tried a subform in the main form and it did not work.. every time an entry was erased on the subform it created issues on the main table, and was difficult to report on.

    you are correct when you say what i need IS some VBA code.. or SQL, or ??

    To me, it doesn't sound very difficult to add some fields together and separate some information from the fields, but I'm not a pro at VBA or SQL code just yet so i'm putting it out there to see if anyone has any ideas..

    Thanks.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What Steve is saying is that because of the way you have stored your data (Incorrectly IMHO like it was still in a spreadsheet), the query you want to write has suddenly become very awkward / not possible in a single simple query.

    By normalising your data - Storing each item(Entity) as data not as a field name, the queries and reports you need become simple.
    Have a quick read here : http://rogersaccessblog.blogspot.co....on-part-i.html for why the design of your data storage is the most important part of your design.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    AccessForumUser is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    19
    Thanks Minty.. so in your opinion, would creating a subform be the best way to go about this? I had it setup like that originally but ran into problems.. maybe I should rethink this and change it around..

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    No in my opinion you would change the table(s) design. Take a step back and describe in non database terms what you are trying to do - break down the parts of your process down into data areas.

    It appears in basic terms you have Technicians, and things they repair.
    This probably means you also have customers that own the items being repaired.
    So already, without any more information, we should have a separate tables for Customers, RepairJobs , Technicians.

    I can't work out the what purpose the badges have in the great scheme of things, so I'll ignore those but feel free to explain in more detail..
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Your spreadsheet (non-normalized design):
    tbl_Main
    ------------------
    ID
    tbl_Badge1
    tbl_Badge2
    tbl_Badge3
    tbl_Date1
    tbl_Date2
    tbl_Date3
    tbl_DateCreated
    tbl_Comments1
    tbl_Comments2
    tbl_Comments3


    Due to the repeating fields, this design violates 1st normal form.

    My suggested (normalized) design:
    tbl_Main
    ------------------
    MainID_PK (autonumber) (PK field)
    TechBadge (Text - 10)
    CaseNumber (Text - 50)
    CaseDate (Date/Time)
    DateCreated (Date/Time)
    Comments (Text - 255)



    There may be other fields/tables involved, but from the dB you posted, this is the table design you should begin with.


    From a post by orange:
    "You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."



    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


    Design your table structure and relationships using paper & pencil, whiteboard, cardboard, sticky notes, etc, BEFORE jumping on the computer. Try adding data to see if the design accomplishes what your goal is.
    It takes time, but it will save you lots more time and save you from major headaches and a possible redesign later on when you hit a wall.

    Good luck with your project........................





    ---------------------------------------------------------------------------------------------------
    For your reading pleasure:

    Normalization http://www.utteraccess.com/wiki//Normalization


    What is Normalization?
    =======================
    What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
    What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
    What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
    What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
    What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


    Entity-Relationship Diagramming
    ==============================
    Entity-Relationship Diagramming: Part I http://rogersaccessblog.blogspot.com...ng-part-i.html
    Entity-Relationship Diagramming: Part II http://rogersaccessblog.blogspot.com...g-part-ii.html
    Entity-Relationship Diagramming: Part III http://rogersaccessblog.blogspot.com...ming-part.html
    Entity-Relationship Diagramming: Part IV http://rogersaccessblog.blogspot.com...-i-ii-and.html



    The Normal Forms
    =========================
    The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
    The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
    The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...nd-normal-form.
    The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
    The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html

  8. #8
    AccessForumUser is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    19
    Thanks Steve! I wish I had these links a year ago before I started making this database!
    I'm coming really close to solving this issue of mine.. I wrote some SQL code to connect the fields together using the UNION ALL/UNION..
    SELECT [tbl_Badge1] ,Count(*) AS [Badge Total] FROM [qry_badge_stat] WHERE [tbl_Badge1] IS NOT NULL GROUP BY [tbl_Badge1] UNION ALL
    SELECT [tbl_Badge2] ,Count(*) AS [Badge Total] FROM [qry_badge_stat] WHERE [tbl_Badge2] IS NOT NULL GROUP BY [tbl_Badge2] UNION ALL
    SELECT [tbl_Badge3] ,Count(*) AS [Badge Total] FROM [qry_badge_stat] WHERE [tbl_Badge3] IS NOT NULL GROUP BY [tbl_Badge3];

    I'm getting the numbers I want to see, however it duplicates the badges up to 3 times depending on if the badge shows up in all the 3 fields

    Have any of you experienced this before? I some how need to take this one step further and concatenate the results from the combined results..

    I understand I may not have made this database using the general rule of thumb, however to recreate it would take months.. it currently has over 400+ records with multiple tables and queries and reports..
    What I have provided in my original .zip was just a tiny stripped out portion of my main database that I recreated.. the important thing about this was the 3 fields i was looking for some code on..

    If any of you have an idea on how I can merge the joined results, that would help me out huge..

    Thanks again for all your help and links. I'm no expert with access but i've learned a lot through this forum over the past year.
    This issue had come up because of a change that was needed after the fact, and feedback from bossman and techs.. the original db was working like a well oiled machine.. groan..

    Thanks.

  9. #9
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'd still redesign it. What happens when you add a badge number, or date 6 or comment 12 ?
    You really would have to start again. 400 records is tiny, and I suspect the gain in long term usability would far outweigh the immediate short-term pain.

    I've rebuilt a system here that had in excess of 100,000 records in the main data table linked to 10,000 + customers records and 100,000's of comment records.
    I changed the entire structure for both the customer records - including bespoke pricing information and added multiple detailed repair information to the main tables.
    It wasn't without a bit of graft and thought, but I was able to let the old system keep running whilst Ninja-ing in the changes.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with Minty. You need to redesign/rebuild your dB. As Minty pointed out, what happens if you need to add another (or 3) badges?
    Answer is, you have to modify the table, then the queries, then the forms, then the code, then the reports! Yep, that is a TOTAL rebuild....

    You also need to have a better naming convention. I was really confused looking at this SQL:
    Code:
    SELECT [tbl_Badge1] ,Count(*) AS [Badge Total] FROM [qry_badge_stat]  WHERE [tbl_Badge1] IS NOT NULL GROUP BY [tbl_Badge1] UNION ALL
    WHY, I wondered, was the table placed at the beginning of the SQL??? Whaaattt???
    Finally looked at your dB and realized that "tbl_Badge1" is a FIELD name!

    And I also hope you have the dB split into a FE and a BE, AND every used has a COPY of the FE on their computer.


    ---------
    3 rules of programming:
    1) Back up
    2) Back up
    and
    3) BACK UP!

    I usually create a back up around every 10 minutes (sometimes sooner)... it only takes losing everything once to understand back ups are your friend.

    Speaking of which: I got my first computer, an Apple II, and was typing in a BASIC program (at 1 am). After 2 hours of typing, I got up to get a drink.
    When I touched the computer again, a spark jumped the gap and scrambled the memory. AND NO BACK UPs!! I had not saved once in those 2 hours.
    Nothing I could do , so I shut down and went to bed.


    ---------
    I have a dB that has a table with over 8,000 employees, with the main table approaching 1.5 million records. Being on a network, the larger the main table became, the slower the response of the forms/reports.

    I was directed to move/change the BE from Access to SQL Server Express (soon to be SQL Server Standard).
    So now I am done with alpha testing and into beta testing.
    Once everything was working (more or less) correctly, I started converting the Access queries to T-SQL queries and stored procedures on the server to reduce the number of records transferred across the network.

    And lots of studying!!! Data types changed, had to learn about Views, stored procedures, stored functions and started working on getting my head around Transactions.


    ---------
    I would advise you design your NEW table structures/relationships on paper, whiteboard, cardboard, sticky notes, etc, BEFORE creating anything in Access. Add test data and start fixing the queries, forms, code and reports. Once all is working right, create the queries and code to transfer the real data. Once the data transfers correctly, delete the data and transfer real data one last time. (Probably over a long weekend.)


    ---------
    I used dry erasable markers on the windows in the office (yes, I have a "Beautiful Mind" - but everyone just laughs at me ) to do table design, then for writing code. Works great!

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

Similar Threads

  1. Help organizing a table or tables
    By CharissaBelle in forum Access
    Replies: 10
    Last Post: 05-19-2016, 02:19 PM
  2. Replies: 1
    Last Post: 04-18-2016, 11:48 AM
  3. Multiple summary total in a report
    By vnms2001 in forum Access
    Replies: 3
    Last Post: 05-19-2015, 12:02 PM
  4. Report summary count with multiple criteria
    By crimedog in forum Reports
    Replies: 1
    Last Post: 09-19-2014, 01:33 PM
  5. Re-organizing records in a Table
    By swb1 in forum Access
    Replies: 1
    Last Post: 07-28-2014, 05:03 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