One-to-Many export to new Database, and retain structure.

[KyleB]

What I'm attempting to do is create a database storage of fixed information. This is the current situation:

Monthly, a user enters forecast information which is eventually finalized, and reports are generated. Then modifications are made, and new reports are generated, but the problem is that the original "finalized" data is now lost. What I'm attempting to do is create a repository where via command buttons, the forecast information for a given time period can be placed for future reference via a different front end database, and this copy of data becomes inviolate, and cannot be changed. In the meantime, the original data is still available as a working copy that can be modified and updated as time progresses. In this way I have a dynamic database that changes over time, but I also have a storage location where we have a snapshot of each month's forecast information as it was finalized, for future reference. The reason for wanting to do it this way is so that we can also do agglomeration's of data to query forecast numbers by quarter, biennial, yearly, by decade etc., as well as retain the original monthly forecast data un-adjusted due to day-to-day changes during the month. In order to do that I need a means of maintaining a copy of the forecast data that doesn't change, as well as a copy that can be modified due to real-world occurences.

My difficulty comes in trying to figure out how to select a set time period (as defined by a month field in my tables) and also select the associated subdatasheets with thier one-to-many relationships and append them in the second database. So far, all I've figured out is that I can create select queries to obtain the information from the tables, and export that data, but they no longer reside in a relational database format, but instead as a flat-file. I would prefer to maintain the previous one-to-many relational structure in the new database, rather than convert my data to a flat-file approach.

So the simple form of this question is this: Can I export a select portion of table data from one database to another, and maintain the same relational database structure, with the same one-to-many connections, and append to them each month? If so, how?

Kyle

 

[modest]

There is a "Relationships" Table that access uses to handle relationship (MSysRelationships)... I'm not sure, but it could be of some help.

Edit:
Nevermind, I thought you might be able to edit the MSysRelationships table. Regardless, you can set up relationships between tables in the new database and just use Append Queries to copy data from one database to another.

I'm not sure if this was what you're looking for.

 

[KyleB]

I am not sure how this would work though. The child table does not have any sort of time period correlation within it. How would I be able to select from it for a specific month. Would it be a case of selecting based on the Parent Key/Foreign key connection, with both tables in the select query, but going with a right (?) join, and only selecting data from the child table? I suppose that makes sense, I'll give it a try and see if I can get it to work based on that premise.

-Edit
This indeed is the step I was mis-using. By making a select query of my parent table I can then do an update/append query into the other database. Then I repeat this with the child data, where I select all data from the second table where the Parent Key and Foreign key match, based on the month criteria selected from the first table. ie:

Parent Table:

SELECT [Backfill Info].BackfillID, [Backfill Info].Month, [Backfill Info].Mine, [Backfill Info].Zone, [Backfill Info].Level, [Backfill Info].[Heading or Location], [Backfill Info].[Backfill Location], [Backfill Info].[Jam Location], [Backfill Info].[Backfill Tons], [Backfill Info].Sequence
FROM [Backfill Info]
WHERE ((([Backfill Info].Month)=[Forms].[frm Switchboard].[Date Filter Text]))
GROUP BY [Backfill Info].BackfillID, [Backfill Info].Month, [Backfill Info].Mine, [Backfill Info].Zone, [Backfill Info].Level, [Backfill Info].[Heading or Location], [Backfill Info].[Backfill Location], [Backfill Info].[Jam Location], [Backfill Info].[Backfill Tons], [Backfill Info].Sequence;

Child Table:

SELECT tblBackfillDetails.FKBackfillID, tblBackfillDetails.BackfillDetailID, tblBackfillDetails.[Cost Code], tblBackfillDetails.[Panel or Activity Number], tblBackfillDetails.[Fill or Jam], tblBackfillDetails.[Backfill Tons], tblBackfillDetails.Sequence, tblBackfillDetails.[Cemented or Waste Filled], tblBackfillDetails.Notes, tblBackfillDetails.LocationDef, tblBackfillDetails.[Material Type], tblBackfillDetails.Trucked
FROM [Backfill Info] LEFT JOIN tblBackfillDetails ON [Backfill Info].BackfillID = tblBackfillDetails.FKBackfillID
WHERE ((([Backfill Info].Month)=[Forms].[frm Switchboard].[Date Filter Text]))
GROUP BY tblBackfillDetails.FKBackfillID, tblBackfillDetails.BackfillDetailID, tblBackfillDetails.[Cost Code], tblBackfillDetails.[Panel or Activity Number], tblBackfillDetails.[Fill or Jam], tblBackfillDetails.[Backfill Tons], tblBackfillDetails.Sequence, tblBackfillDetails.[Cemented or Waste Filled], tblBackfillDetails.Notes, tblBackfillDetails.LocationDef, tblBackfillDetails.[Material Type], tblBackfillDetails.Trucked;

Thank you for confirming, and pointing me in the right direction yet again Pat.

Kyle