Results 1 to 6 of 6
  1. #1
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103

    How to organize a form with multiple variables on single page vs multiple pages

    I am in the process of setting up a DB for field testing of water samples. We have 65 customers with multiple sample location at each site (1 to 7) and multiple tests per sample location (1 to 25) which vary per each quarterly period. In Excel, I have created a spreadsheet showing the required sampling by each customer, sample location, test and quarterly period. (All in one Table with the various locations (under one Location_ID field) and sampling tests by quarter as their own fields).



    However, once I created the same table in Access, I am having difficulty in organizing a single page form which is to show each customer with all locations and corresponding tests. (Physically it can be done on a standard size page).
    The issue that I am having is that instead of a single page form for each customer, I am getting multiple pages which correspond to the actual number of locations per each customer.

    I can accomplish what I need by exporting the DB Table into Excel and then using the transpose function. However, now in Excel, the Customer names appear in multiples reflecting the number of locations.

    How can I aggregate each customer with the various test locations and the corresponding test variables on a single page in Access?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sounds like your tables is not normalised. Give details about the table design.
    Screenshots of the table & its Excel equivalent would help. Ditto for the form layout
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Click image for larger version. 

Name:	Transposed Excel spreadsheet.JPG 
Views:	23 
Size:	40.0 KB 
ID:	33521Click image for larger version. 

Name:	Access Form page1.JPG 
Views:	23 
Size:	61.4 KB 
ID:	33522Click image for larger version. 

Name:	Access Form Pag2.JPG 
Views:	23 
Size:	65.6 KB 
ID:	33523
    at left is the transposed Excel sheet which I exported from the the DB (by choice shows only Q2 tests) and at right are two pages of the same data (by choice showing all four Quarters) on Access form

  4. #4
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    OK, so after more digging into this there are 2 issues that actually exist.

    1. I did not create the proper table relationships (one to many)
    2. I did not make use of a sub-form to create the "many" part of the report (the sampling matrix).

    Thank you for pointing me on the correct path to solve this problem.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to the info from ridders52 and your post update, here is a link to info on Database Planning and Design. I recommend the tutorials from RogersAccessLibrary mentioned in the link. Work through 1 or 2 (30-60 minutes) and you will learn about database design.
    Good luck.

  6. #6
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Great, excellent resources. Again, thank you.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-10-2017, 10:07 AM
  2. Replies: 0
    Last Post: 04-15-2016, 08:08 AM
  3. Replies: 14
    Last Post: 03-15-2016, 01:22 PM
  4. Replies: 2
    Last Post: 02-08-2012, 01:03 PM
  5. Multiple records on a single page
    By neo651 in forum Forms
    Replies: 1
    Last Post: 06-29-2011, 10:21 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