Hi all,
I'm an Access newbie and I'm trying to create a database to replace a (horrible) excel spreadsheet which has been in my company for about 18 years.
So the main table has a field for sample ID, and fields for each sample's associated chemistry. Each sample belongs to a drill hole, and some drill holes have 100 samples others have 5. The bulk of the data is entered by a lab who copy and paste from excel, and their sheets don't have the drill hole data, so this is always missing when they do the data entry. I was thinking there must be an easier way to enter the drill holes into the main table too rather than either manually or doing an excel sheet and copy/pasting it over, so I created a second table which has drill hole ID, sample from and sample to. E.g., drill hole DH1 contains from sample S001 to S100.
Is there a way to make the drill hole ID on the main table auto complete? As in, can I have a formula saying "if a sample ID is entered, and is between S001-S100, drill hole ID = DH1?" I have no idea how to do formula/macro/VBA code in Access (I do have experience with all in Excel).
And yes, unfortunately the hole ID has to be there on the main sheet as the geology department use the sheet and need to sort and filter using hole id.
I hope I've explained everything ok Any help is appreciated.
Thanks,
EJ