Extracting data to new tables

[matt beamish]

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

 

[June7]

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.

 

[matt beamish]

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

 

[MajP]

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.

 

[matt beamish]

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.

 

[MajP]

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.)

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.

 

[matt beamish]

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

 

[MajP]

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]

AttributeFindID	AttributeType	AttributeValue	FindID_FK
1	Hole Type   Ovoid	1
2	Color	         Black       1
3	Length       1 inch	1
4	Velocity     4 m/s	1
5	Hole Type  Circular	2
6	Color	        Blue          2
7	Length      7 inches	2
8	Velocity     10 m/s	2

TRANSFORM First(Table1.AttributeValue) AS FirstOfAttributeValue
SELECT Table1.FindID_FK
FROM Table1
GROUP BY Table1.FindID_FK
PIVOT Table1.AttributeType;

FindID_FK	Color	Hole Type	Length	Velocity
1	Black         Ovoid       1 inch        4 m/s
2	Blue          Circular     7 inches   10 m/s

 

[matt beamish]

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.

OK thanks, zip attached

 

[June7]

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?

 

[matt beamish]

.... 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.

 

[June7]

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.

 

[MajP]

FindID	calculated_weight	Hole_dia_maximum	Hole_dia_minimum	length_max	Length_min	No_holes	thickness_max	thickness_min	Weight	width_max	width_min
BUR-P45-7-SF336				5254	5253	5255			5252		
BUR-P49-2-SF250	1770	4121		998	1384	314	3314	2928	612	2156	2542
BUR-P49-2-SF271	1771	4122		999	1385	315	3315	2929	613	2157	2543
BUR-P49-2-SF272	1772	4123		1000	1386	316	3316	2930	614	2158	2544
BUR-P49-2-SF273	1773	4124		1001	1387	317	3317	2931	615	2159	2545
BUR-P49-2-SF274	1774	4125		1002	1388	318	3318	2932	616	2160	2546
BUR-P49-2-SF275	1775	4126		1003	1389	319	3319	2933	617	2161	2547
BUR-P49-2-SF276	1776	4127		1004	1390	320	3320	2934	618	2162	2548
BUR-P49-2-SF305		5257	5258			5256					
BUR-P49-2-SF306		5260	5261			5262			5259		
BUR-P49-2-SF320									5271		
BUR-P49-2-SF321		5263	5264			5265			5270		
BUR-P49-2-SF322		5266	5267			5268			5269		
BUR-P49-2-SF323									5272		
BUR-P49-2-SF324									5273		
BUR-P49-2-SF325									5275		
BUR-P49-2-SF326									5274

 

[matt beamish]

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.

 

[MajP]

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

FindID	No_holes	Position_of_holes	Hole_dia_maximum
BUR-P45-7-SF336	2	across base	
BUR-P49-2-SF250	1	near top	8
BUR-P49-2-SF271	1	46mm from top	13
BUR-P49-2-SF272	1	51mm from top	12
BUR-P49-2-SF273	1	29mm from top	11
BUR-P49-2-SF274	2	38mm from top	12
BUR-P49-2-SF275	2		0
BUR-P49-2-SF276	1	43mm from top	13
BUR-P49-2-SF305	3	across corners	10
BUR-P49-2-SF306	3		12
BUR-P49-2-SF320			
BUR-P49-2-SF321	2	across corners	10
BUR-P49-2-SF322	4	across base	10

 

[matt beamish]

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