Extracting data to new tables (1 Viewer)

Hi folks it's been a while since I last visited but I've not been doing much DB development in the past few years.

I've recently helped a PhD student by redesigning an access database setup for recording different types of artefacts. The database consisted of a central table holding artefact ids and some common descriptions, with a series of related tables holding attributes of different types of artefact. What could be recorded was very proscribed and the student was finding that it didn't suit the data and what she wanted to record, and it was information that had been overlooked in the past as the existing database didn't allow for new attributes.

The structure was not normalised, and there were a number of irritating design features (e.g. tables, forms and queries all with exactly the same names), but despite that it worked and is used quite widely by a number of specialists.

I have now resigned the db to one central table holding PK, find Id and common/core attributes, and two related tables, one holding text attributes, and one holding numeric attributes.

Each related table contains attributes from all the different types of artefact with pk, find id, 'attribute type' and 'attribute value', text or numeric. 'Attribute types' are stored in a separate table that combines attribute with artefact type, and is a combo on the recording form. New attributes can be defined in this table for different artefacts, and described as numeric or text.

This design is now up and running and is good for recording. It is compact and elegant. The same form can be used for different artefacts, and new types of attribute can be defined and recorded with no change to db structure.

Where I am struggling is how to get the data out in to a simple spreadsheet so that the data can be summarised. I effectively need to make tables using the "Attribute" descriptor of the two attribute tables (1 text, 1 numeric) as the field name, and the attribute value as value.

Has anyone got any thoughts?

thanks
Matt

Why would 1 table have text and another table have numbers? Why export to spreadsheet for summarizing? If you want to provide db for analysis, follow instructions at bottom of my post.

.... because some attributes are text, eg round, square, clay, stone, and some are numeric, eg 11 mm., 55 g etc.

It looks to me that you have created an Entity-Attribute-Value (EAV) model, if you knew it or not.
https://en.wikipedia.org/wiki/Entity–attribute–value_model

Recently discussed on this forum
https://www.access-programmers.co.uk/forums/showthread.php?t=304932&page=2

Normally in these types of models you need to use crosstab queries to change rows into columns. In the EAV model instead of a lot of sparsely filled columns you have many rows. A crosstab will allow you to change the attributes into columns.

thanks vMajp, yes an EAV model is exactly what I have done - very streamlined, efficient and flexible. I'll check out the wiki, previous threads and cross tab, but I wonder if I need to make tables with some vba./sql to define fields depending on the content of the Eav tables.
Thanks again.

In the thread I reference, I describe using a single table to store values which requires tracking datatypes so that you can convert. Using two or more tables for the values is quite appropriate as stated here

The value

Coercing all values into strings, as in the EAV data example above, results in a simple, but non-scalable, structure: constant data type inter-conversions are required if one wants to do anything with the values, and an index on the value column of an EAV table is essentially useless. Also, it is not convenient to store large binary data, such as images, in Base64 encoded form in the same table as small integers or strings. Therefore, larger systems use separate EAV tables for each data type (including binary large objects, "BLOBS"), with the metadata for a given attribute identifying the EAV table in which its data will be stored. This approach is actually quite efficient because the modest amount of attribute metadata for a given class or form that a user chooses to work with can be cached readily in memory. However, it requires moving of data from one table to another if an attribute’s data type is changed. (This does not happen often, but mistakes can be made in metadata definition just as in database schema design.)

Click to expand...

If you can post your db that may help. You can remove data if proprietary. But definitely look into crosstabs, it should be the answer.

How do I get through cross tabs needing to group values with aggregate functions whereas I want to display the actual value - not a count, an average etc.

I'd rather not post the data as it represents several years research by the student I'm helping. If ultimately I need to, I'll pair it down to something of no significance.

Here are my tables.

T_finds:
FindID (unique)
Findcategory (constrained to keep consistency)

eg Find1
Findctegory: Spindle whorl

TS_Attributes:
Findcategory
Attributetype
If text then entry values are constrained to a drop down with the ability to add new.

eg Spindle Whorl
Attributetype: Hole shape

T_Attributes:
FindID
Attributetype
Value

eg Spindle Whorl
Attributetype: Hole shape
Value: eg Round or ovoid or irregular

I want a data grid to show

Find ID in Rows
Attribute Types (hole shape) as Columns
and Attribute (round, ovoid, irregular) as the cell value.

And then repeat this for the other attributes of spindle whorls, and all the other artefact types that there are. The artefacts would be filtered into different tables.

thanks again

1. Make a clean db.
2. Import the tables and delete any real data and trim it down to enough fake records for demonstration purpose.

It will be a lot easier to demo something for you with a common starting point.

MajP said:

1. Make a clean db.
2. Import the tables and delete any real data and trim it down to enough fake records for demonstration purpose.

It will be a lot easier to demo something for you with a common starting point.

Click to expand...

OK thanks, zip attached

Instead of compound key with Site and ContextID, why not save T_Contexts.RecID? Recommend avoiding compound keys. Can set compound index to prevent duplicate pairs. The compound link between T_Findregister and T_Contexts not even key fields.

Advise giving RecID different name in each table. RecID is defined as primary key in all but one table yet it is saved as foreign key for only 1 relationship.

Designated primary key should be saved as foreign key. Suggest you fix relationships.

Why would a comment field be included in a compound key?

.... Should have explained t_contexts and t_sites etc not resolved yet and still have elements of original design. It's how to get the data out of t_findregister, t_metrics and t_attributes that is my concern.

I always design with independent autonumber PK and separate unique index, but thanks for advice - appreciated.

Including autonumber is not an issue. It is designation of primary key field that is. Autonumber does not have to be the primary key. The field whose value is saved as foreign key should be the primary key.

thanks - so its using 'First' in the cross tab - so I now have:

TRANSFORM First(T_Metrics.Metric) AS FirstOfMetric
SELECT T_Metrics.FindID
FROM T_Metrics
GROUP BY T_Metrics.FindID
PIVOT T_Metrics.MetricDescription;

I just need to compile the different tables in a query and then run the transform.

thanks very much - exactly what I needed.

Two Things
1) If you want to ensure an order for your columns you can specify all your column headings, but that may be labor intensive
2) In places where you do not have a value, but you want that value to be zero or something else you can use the NZ function.
3) If you union the attributes and metrics you could have text and value attributes in your query. Then make a crosstab

http://allenbrowne.com/ser-67.html

Crosstab after union example

Thanks, yes I've done the union and cross tabbed that.
cheers