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
Let's see if this works.
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
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 ↓↓
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.
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.
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.
No, you definitely need at least two tables, pretty much as @Minty indicated?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.
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
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 ↓↓
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?
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 ↓↓
If you want to link assetid to the many table, then it would be the pk of the one table?, in this case assetid?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?
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
Thank you.