how do I make a table of a current query with many child tables

[Guest]

Basically I have build a query with child tables. All containing current data.

I need to maintain a history of that data including the past changes in the
child data.

So is there any way of simply copying the current query to a "flat file"
table to build up that history?

 

[Guest]

Yes. Make the query a MakeTable query. I would suggest adding a field with
the date of the transaction by putting the following in the Field column --
TransactDate: Date()

After runing the query the first time change it to an append query.

 

[Ed Robichaud]

Your question suggests a real misunderstanding of relational database
structure, and like building a house, it's always better to start with a
solid foundation before continuing to build.

Databases start with table design. A query is a filtered and/or sorted
display of records from one or more of those tables. Forms are used for
data entry and display of records from a table/query; reports are used to
group and list records from those table/queries.

As an example of how to store historical info, let's use a club membership
application. Below is the basic table structure and relationships needed to
record your info.

tblMember
MemberID -primary key (autonumber recommmended)
LastName -text
FirstName
Address
etc.

tblDues
DuesID -primary key
MemberID -foreign key -linked to tblMembers
DatePayed - date/time
Amount -currency
etc.

Just those two tables will record an near-infinate list of members and a
near-infinate list of payments for any one member.

A typical data entry form would have the main form based on a query of
tblMember (sorted by name) and a linked subform based on a query of tblDues
(sorted by MemebrID and DatePayed).

Even though you have time invested in your current structure, now is the
best time to fix it and/or start over.

-Ed