Good write-up explaining appending/flowing data to child tables?

[AverageAmy]

I’m looking for a good write-up explaining techniques for flowing data to child tables, probably using Append Queries. Preferably the write-up would have examples, but that is not absolutely necessary. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
It seems that many references on Access do not go into Append queries that far. I have posted to this forum and got a reply (Thanks, SydneyGeek!) that was helpful in getting me started in writing a Select Query to get information like Primary Key from the Parent table, then referencing that Select Query in an Append Query. This did work to flow data to the Child table. However, the queries get very complicated when I get to the lower tables. I think I’m still missing something and could use a detailed explanation of theory or effective techniques. <o></o>
<o></o>
Can someone suggest a good book (or other reference) to look in for this?

 

[jackd]

Can you describe your project/proposed application? We have no idea of the context of your request. Please help us, help you.

Do you have a model of the database involved?

 

[AverageAmy]

jackd: Yes, I will write something up and post it. It's long, though. I'm really looking for people's "favorite" Access references that go beyond what just the parts of a database are, and expands into more complex database actions.

 

[jackd]

The first few topics here are great for design. I'm sure some of these would be helpful in various situations.
http://www.rogersaccesslibrary.com/forum/topic238.html

the home page has links also http://rogersaccesslibrary.com/

 

[AverageAmy]

My project is for civil engineering work where we obtain soil samples from borings and run tests on those soil samples. The database has 6 levels:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Client table
ClientID (PK)
Client name

Project table - multiple projects for each client
ProjID (PK)
ClientID (FK)
ProjectName
ProjectLocation

Borings table - multiple borings for each project
BoringID (PK)
ProjID (FK)
BoringNum

Samples table - multiple samples for each boring
DepthID (PK)
BoringID (FK)
Depth_ft
SampleType
etc

RCTests table - multiple tests for each depth
RCTestID (PK)
DepthID (FK)
RCTestNum

RCTestDetails table - multiple data points for each test
RCDetailID (PK)
RCTestID (FK)
MeanStress
Gmax<o></o>
<o></o>
And at some point there will be more tables at the RCTests table and RCTestDetails table levels (different types of tests)

I would like to add data to all the related tables from my Excel file from which I am converting or mining data from. I have imported this table into Access and it is titled RCDataMinerTable_4Append2qry.

The Excel file has about 10,000 rows and 40 columns. There is 1 row for each RC Test Detail entry (for example: 10,000 RC test details, 2,500 RC tests, 2,000 samples, 400 borings, 100 projects, 75 clients).

I have set up several “GetID” queries where I match the data in the current child table to the parent table. For example, this is the SQL view of the GetProjID query:<o></o>
SELECT DISTINCT Project.ProjectID, [RCDataMinerTable_4Append2qry].ProjectNumber, [RCDataMinerTable_4Append2qry].BoringNumber, [RCDataMinerTable_4Append2qry.ProjectNumber] & " " & [RCDataMinerTable_4Append2qry.BoringNumber] AS ProjBor<o></o>
FROM RCDataMinerTable_4Append2qry INNER JOIN Project ON [RCDataMinerTable_4Append2qry].ProjectNumber=Project.ProjectNumber<o></o>
ORDER BY Project.ProjectID;<o></o>
<o></o>
The ProjBor field in the select query is calculated from the project number and boring number, and then I have this field in my destination table and I do not allow duplicates. <o></o>
<o></o>
Then this is the append query that uses that GetProjID to take data from my import table to the Boring table:<o></o>
INSERT INTO Boring ( ProjectID, BoringNumber, TotalDepth_m, Longitude, Latitude, ProjBor )<o></o>
SELECT qryGetProjID.ProjectID, RCDataMinerTable_4Append2qry.BoringNumber, RCDataMinerTable_4Append2qry.TotalDepth_m, RCDataMinerTable_4Append2qry.Longitude, RCDataMinerTable_4Append2qry.Latitude, qryGetProjID.ProjBor<o></o>
FROM RCDataMinerTable_4Append2qry INNER JOIN qryGetProjID ON (RCDataMinerTable_4Append2qry.BoringNumber = qryGetProjID.BoringNumber) AND (RCDataMinerTable_4Append2qry.ProjectNumber = qryGetProjID.ProjectNumber); <o></o>
<o></o>
This isn’t too bad and I feel like it’s a pretty good approach (at least to my little newbie brain). However, as I proceed down more levels, it gets more complicated to write the “Get” query and the append query. I am finding that I essentially need to grab more and more information from the parent table to get the records appended to not be duplicated. Here is a set of queries for one of the lowest level sets of tables:<o></o>
<o></o>
qryGetRCSampleID<o></o>
SELECT DISTINCT Sample.DepthID, RCDataMinerTable_4Append2qry.ProjectNumber, RCDataMinerTable_4Append2qry.BoringNumber, RCDataMinerTable_4Append2qry.SampleNumber, qryGetBoringID.RCDepth_m, [qryGetBoringID.ProjBorSamp] & " " & [RCDataMinerTable_4Append2qry.RCDepth_m] AS ProjBorSampDepth<o></o>
FROM (RCDataMinerTable_4Append2qry INNER JOIN qryGetBoringID ON (RCDataMinerTable_4Append2qry.ProjectNumber = qryGetBoringID.ProjectNumber) AND (RCDataMinerTable_4Append2qry.SampleNumber = qryGetBoringID.SampleNumber) AND (RCDataMinerTable_4Append2qry.BoringNumber = qryGetBoringID.BoringNumber) AND (RCDataMinerTable_4Append2qry.RCDepth_m = qryGetBoringID.RCDepth_m)) INNER JOIN Sample ON (qryGetBoringID.SampleNumber = Sample.SampleNumber) AND (qryGetBoringID.BoringID = Sample.BoringID);<o></o>
<o></o>
qryAppend_SampleRCTest<o></o>
INSERT INTO RCTest ( DepthID, Depth_m, ProjBorSampDepth )<o></o>
SELECT qryGetRCSampleID.DepthID, qryGetRCSampleID.RCDepth_m, qryGetRCSampleID.ProjBorSampDepth<o></o>
FROM RCDataMinerTable_4Append2qry INNER JOIN qryGetRCSampleID ON (RCDataMinerTable_4Append2qry.SampleNumber = qryGetRCSampleID.SampleNumber) AND (RCDataMinerTable_4Append2qry.ProjectNumber = qryGetRCSampleID.ProjectNumber) AND (RCDataMinerTable_4Append2qry.BoringNumber = qryGetRCSampleID.BoringNumber);

 

[SydneyGeek]

Amy,

Two books that I own and like are Access 2002 Inside Out, and Expert One-on-One Access Development, both by Helen Feddema.
John L. Viescas wrote Building Microsoft Access Applications.

The last two of these are built around specific projects and includes all the code and the reasoning behind the designs.

There are also some good books on Access VBA (the above have VBA as well); including
Access 2003 VBA, Access 2010 Programmer's Reference, Pro Access 2007.

Denis

 

[AverageAmy]

Thank you very much for the reference suggestions, both of you.
I also bought Grover Park George on Access and it arrived yesterday.

 

[HiTechCoach]

I’m looking for a good write-up explaining techniques for flowing data to child tables, probably using Append Queries. Preferably the write-up would have examples, but that is not absolutely necessary.

After reading your followup post by "flowing data to child tables" I think what you are wanting to lean about is converting denormalized data to a normalized structure.

When taking denormalized data, usually a single row/record, and converting to a normalized structure, a multiple table relational database, you don't use append queries much. An append query can generally only be used on the lowest level table. Using recordsets the best way I know. It is possible to do with lots of append queries but it requires adding extra temporary field(s) to table(s).


To see how it is done with a check out: Duplicate a Main Form and Its Subform Detail Records

Most people do not have to deal with converting or importing data like this. I have not yet seen a book that covers this topic. Every conversion is different. Not only do you have to match fuels you may also have to clean up the data. I have been specializing in data conversions for 30+ years. It has been my experience that it is really an art form.

 

[AverageAmy]

Thank you for the input. I will check out the link you provided and also look for the term "recordsets."