How to structure records which require different parameters/fields? (1 Viewer)

[JGalletta]

I am creating a database to keep pest scouting records in order to determine which pests are above my company's thresholds - to make recommendations for combating these pests. Scouts are sent out into fields to search for these pests. They require different information based on which pest they find. My current structure looks like this:

I'm running into an issue with my entry form where I am interested in keeping all records for a given field visible at any instance. The issue arises due to the fact that different pests require us to record different parameters.

See this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=242023

Is this a structure issue or an entry form issue? What can be done to fix this issue?

 

[The_Doc_Man]

In general, you CANNOT have variant records in Access. That is, if you have a table with 10 x LONG fields, it is always 10 x LONG, never 8 x LONG + 1 x QUAD or something like that.

This means that you cannot mix/match disparate field types in a single table. You cannot use Access tables as though they were Excel spreadsheets because Excel stores data types in each cell if needed. Access stores data types in the "column descriptor" (actually called a FieldDef) for each field in the table where it appears.

At first blush, therefore, it seems as though you can't do what you want. This is not, however, always true. There are such things as UNION queries that can be used to build recordsets based on disparate tables. UNION queries allow you to build a single query based on merging selected fields from multiple similar (but not identical) tables, naming the selected fields with the same name each time you draw from a different table even though those fields might have different names in their "natural" locations.

We have a totally different - yet somewhat similar - situation at my office, where we have personnel data for military types - officer and enlisted - and their dependents. We keep different data for the three categories, but they are all people of course. (Well, at least we THINK most of them are ...)

What we do is break the tables up in a somewhat artificial way, putting all the common data in a Personnel table along with a record-type identifier. Then we have tables such as OfficerSupplemental, EnlistedSupplemental, and DependentSupplemental. Things that need to be aggregated about people are in the Personnel table. The structures of the three supplemental tables differ because of different regulations that apply to each case. In terms of the number of records, each record in our Personnel table matches one - and only one - of the entries in ONE of three possible Supplemental tables. It never occurs that one Personnel entry matches two records, one in each of two supplemental tables. Therefore, technically speaking, the PK of the personnel table matches a sparse FK in the supplemental tables. Let's not even THINK about what happens when two persons in the Personnel table get married and create a little dependent...

When we need to process the individual cases, we have three joins, one for each category, so that we have a full personnel + supplemental query record for each person, but each person is in one and only one of the query recordsets. Of course, it took a pot-load of time to get the structure right, and I suspect it will take some serious skull-sweat for you to get your part just right as well.

The basic guideline, as I see it, has to be "divide and conquer" so that you can treat common stuff in common and yet keep the separated stuff separate. It is going to boil down to your individual abstraction skills as you try to find the commonalities and the incompatibilities between data subsets.

My final and best advice is that if you try to implement this too quickly before you have the details of this design worked out, you will make your head hurt a lot and will do a lot of retrofitting. You are doing something very complex and Access doesn't like the variability of your data.

 

[JGalletta]

Can the union query be written with SQL to make a recordset to loop through for calculations? Not sure I'll need to do this, but is it possible?

 

[Galaxiom]

Hopefully you will be able to do the calculations in the query. I expect you would do the calculations first as subqueries, applying the formula that are relevant for the particular field types then union the results from the subqueries.

Otherwise a Union query is not updateable so it is not possible to include other fields which would later be calculated via a recordset loop.

However it is possible to create a fabricated ADO recordset then populate it with the data from the union query's recordset, then loop through it to update calculated values.