Calculating a field in a form based on two tables

[SalmonDB]

Hi Everyone,

I have a bit of a complicated problem. I am creating a database for DNA samples. Samples are collected at a number of locations so I have one table that contains each location, and another that keeps track of each DNA collection (each collection has 100 samples). When I'm entering data for a new collection, I have a continuous form that is limited (using cascading combo boxes in the header) to all the kits for that specific location.

I have pre-assigned a range of sample numbers for each location, and this range is stored in the fishing locations table. What I would like to happen is this:

On the form, when I click on the DNA sample number start (SampleNo_Start) field, I would like it to determine the next in the sequence of sample numbers and automatically fill in the sample number start and end values. It could do this by referring to the location_ID, and then querying the table that has all the DNA collections, finding all the samples that match that location, and then finding the max sample number, and adding 1. In the event that there are no DNA collections existing for that location yet, it would go back to the fishing locations table and determine the starting sample number for the given location.

Does that make any sense?

Here's an example:

tblFishingLocations

ID Location SampleRange_Start SampleRange_End
1 Fraser_River 12001 30000
2 Haida_Gwaii 30001 35000
3 Vancouver_Island 35001 40000

tblDNACollections
Collection_ID Location_ID SampleNo_Start SampleNo_End
1 2 30001 30100
2 1 12001 12100
3 1 12101 12200
4 2 30101 30200

frmDNACollection_request
Location: 1

Collection_ID Location_ID SampleNo_Start SampleNo_End
2 1 12001 12100
3 1 12101 12200
5 (New) 1 = max(tblDNACollections!SampleNo_Start
WHERE Location = Me.Location_ID
IF no (Location_ID) in tblDNACollection then
refer to
tblFishingLocations!LocationID!SampleRange_Start

OK, obviously this is not the correct code at all, I'm just trying to convey the purpose of what I'm trying to accomplish. I am very new to VBA and access in general, and I suspect I might be taking a totally incorrect approach to this problem.

Any hints or suggestions are very much appreciated!
Erica

 

[MarkK]

I have pre-assigned a range of sample numbers for each location

This is an arbitrary requirement, right? You could also sequentially generate sample numbers for DNACollections, and then prefix them with the LocationID, so a new batch might have the number 1.17, which indicates it was taken on the Sto:lo (Fraser) river, and that it is overall the 17th DNACollection. The range requirement you specified is harder to implement, and doesn't seem to me to more adequately provide human readable information.

Is there some requirement that the numbering scheme be implemented as you describe? Is it an industry standard?

Consider the simplicity of this system...

tblLocation
ID Location
1 Sto:lo
2 Haida_Gwaii
3 Vancouver_Island

tblDNACollection
CollectionID LocationID
1 2
2 1
3 1
4 2

CollectionID LocationID HumanReadableID
2 1 1.2 (1-100), -> so a code for the 75th sample is 1.2.75
3 1 1.3 (1-100), -> code for the 12th sample is 1.3.12
* and for new records, autonumber fields automatically generate the IDs, so all work managing the scheme is eliminated. All you do is report the numbers.

Does that make sense?

 

[SalmonDB]

Hi lagbolt, thanks for the reply!

Unfortunately I need to keep the format consistent with past years for now since there are a number of people and programs set up to run on the old format. I'm definitely looking into a more straightforward numbering system and hopefully I will be able to switch it over in the years to come. Your way of doing it certainly looks much easier! I had actually been thinking about using an alphanumeric code, but your format with the decimal places would be handy as well.

Do you know, if I had a code such as 2.3.75, would I be able to use VBA to isolate the last digits at then perform simple mathematical functions, such as a subtraction, in order to determine the number of samples collected? We've had some trouble changing between number and text formats in the past.

Thanks for the heads up about the Sto:lo too!

Erica

 

[MarkK]

Just teasing about the Sto:lo. Simon Fraser showed up around 200 years ago. Evidence of Sto:lo habitation in the "Fraser" valley, as I understand it, is 5000 years old.

Do you know, if I had a code such as 2.3.75, would I be able to use VBA to isolate the last digits at then perform simple mathematical functions, such as a subtraction, in order to determine the number of samples collected?

Check out the VBA.Split() function, which splits a string on a particular character, and returns an array of the "delimited" elements. Super handy for parsing something like "1.4.23.666.hellothere.123"

The problem with what you've presented is that it duplicates data, so the end of a number range, say 30000 on the Fraser, and the beginning of the next range, the 30001 on Haida Gwaii, is in fact the same data point. There is one threshold, but it is defined in both records, so, like the worst possible outcome for you building a database is that your data's in conflict with itself. One day a user will update the end of one range and not realize that ranges now overlap, and now how does your system know which one is wrong?

Same for DNACollections, where you specify start and end range, but also in that case you have 100 numbered sub-elements, don't you? Does each one of those get a record in a table too? Does it ever happen one of those items is destroyed, or removed, or not counted? If you want to keep track of that you want your counts accurate, so typically in a database you don't assign the child count to a "hard-coded" value in the parent record, but rather you count the actual data present in the database at the moment you query it, which gives you the most up-to-date summary of your actual data.

Sorry gotta go, but does any of that make sense?

 

[SalmonDB]

Hey Thanks again, yes that totally makes sense, and I will use it to try and persuade the bosses to update the way we number our samples.