Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 48
  1. #16
    OlBill is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    52
    Quote Originally Posted by Bob Fitz View Post
    Can you post a copy of your db
    It's over the size limit, even with just the tables. 1.50 MB (1,572,864 bytes)

  2. #17
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by OlBill View Post
    It's over the size limit, even with just the tables. 1.50 MB (1,572,864 bytes)
    Zip it? using explorer
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #18
    OlBill is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    52
    Let's see if this works.
    Attached Files Attached Files

  4. #19
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Yep, a DB in Excel structure.

    Any time you need to add a number to a data item, that generally means you need a new table to told that data and then have a 1 to many relationship.
    You could even just have a field to identify whether Asbestos or Lead in the many table.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #20
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by OlBill View Post
    I don't know how to do it. I found it.
    You can get instruction at the top of this thread by clicking on "How to Attach Files"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #21
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You need two tables and not 250 + fields, something like;

    tbSampleHeader tbSampleResults
    SampleID ResultID (Autonumber)
    SampleUnit SampleID_Fk (Links Back to the Header table)
    AssetType For (Should this be a ClientID? if so it probablybelongs in the header)
    AssetID Group
    DataEntryDate CPC_SampleID
    InMeridium SampleDate
    LA_Package Abater
    Comments MaterialColour
    SampleType (lead / asbestos / honey / etc.) WO
    Results
    ResultDate


    This means you can record 1, 10 or 100 samples per header and never need to change your table design.
    You can also add multiple other sample types without adding another table.

    I took the liberty of removing all the spaces from your field names, you will grow to hate all the extra typing that can cause.
    you can label the entries on the forms any way you like.
    Not sure about the attachment field, they are generally advised against using, so what's it's purpose?
    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. #22
    OlBill is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    52
    Minty,
    I see what you're doing. I don't really care about the Sample ID, I care about the Asset ID. I've got one Asset with 20 samples. Some of them L, some of them A. The AssetID is unique to each physical asset. It's like a history of what was done on that piece of equipment. I've also got to report it by asset. I don't know if that helps.

  8. #23
    OlBill is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    52
    I started over. I rebuilt my tables and took out the spaces. I didn't hit the record limit this time. I only have one table now. This is what it looks like. An example of an AssetID is U09C005. L is reported in numbers, A is reported as Yes/No

    An example of a sample ID is - PB090822TI-01. It may have 01-12 and each one may belong to a different Asset.
    Attached Files Attached Files

  9. #24
    OlBill is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    52
    I have to spend the rest of the day in meetings, but I do appreciate the help. I'll check back in Monday. Thank you all.

  10. #25
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by OlBill View Post
    I started over. I rebuilt my tables and took out the spaces. I didn't hit the record limit this time. I only have one table now. This is what it looks like. An example of an AssetID is U09C005. L is reported in numbers, A is reported as Yes/No

    An example of a sample ID is - PB090822TI-01. It may have 01-12 and each one may belong to a different Asset.
    No, you definitely need at least two tables, pretty much as @Minty indicated?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #26
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You are still storing your results in a spreadsheet.

    If I interpret your description and data correctly you have various assets that you record repetitive samples data about?
    However you know your business model and we don't, so we are guessing.

    It would appear that the Asset drives the data not the sample - so you have an asset table with the Asset specific data ONLY in it. You give it an autonumber.
    You then store your samples data against that Asset data in a table similar to the one I showed above. You link those sample to the autonumber for the asset.

    You should never ever have repeating numbers of fields with the same key data elements in a database, it just doesn't work that way, and will lead you down a rabbit hole of increasing difficulty and pain.

    Search on here for committing "Spreadsheetitis" and research Normalisation
    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 ↓↓

  12. #27
    OlBill is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    52
    If I interpret your description and data correctly you have various assets that you record repetitive samples data about?

    Correct. Multiple samples of both types.

    I'm with you. So my AssetID would be the autonumber on the AssetTBL. Primary Key. One What should my Foreign key be on my SampleTBL? Many?

  13. #28
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm afraid I'm on an urgent late night project for a client, so will have to pass the baton on further advice until next week, as I'm away on a trip until Monday.
    Hopefully someone else will assist.
    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 ↓↓

  14. #29
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by OlBill View Post
    If I interpret your description and data correctly you have various assets that you record repetitive samples data about?

    Correct. Multiple samples of both types.

    I'm with you. So my AssetID would be the autonumber on the AssetTBL. Primary Key. One What should my Foreign key be on my SampleTBL? Many?
    If you want to link assetid to the many table, then it would be the pk of the one table?, in this case assetid?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #30
    OlBill is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    52
    Thank you.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 34
    Last Post: 07-04-2021, 04:55 AM
  2. Replies: 2
    Last Post: 03-13-2018, 12:32 PM
  3. Replies: 9
    Last Post: 07-19-2017, 11:01 AM
  4. Replies: 6
    Last Post: 10-16-2014, 12:55 PM
  5. Linking 2 Tables or Queries
    By vdanelia in forum Database Design
    Replies: 4
    Last Post: 03-01-2011, 03:07 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