History table

I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each building
has a unique ID and the BUILDING table has a one to many relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table when
the FORM is updated?

Jeff

tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another (fsubHistory)
based on tblHistory. You may want to set the default view of fsubHistory to
Continuous so that you can see several history items at once. With
frmBuilding open in design view, drag the icon for fsubHistory onto it.
This creates a form/subform based on the related tables. Add building
information via the main form, and history information via the subform as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each building
has a unique ID and the BUILDING table has a one to many relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table when
the FORM is updated?

Jeff

OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I update
its record using the main FORM. My goal is to have a HISTORY table that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the HISTORY
table will have all of the prior transactions. My guess is that certain
fields would have to be copied to the HISTORY table upon an update but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another (fsubHistory)
based on tblHistory. You may want to set the default view of fsubHistory to
Continuous so that you can see several history items at once. With
frmBuilding open in design view, drag the icon for fsubHistory onto it.
This creates a form/subform based on the related tables. Add building
information via the main form, and history information via the subform as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each building
has a unique ID and the BUILDING table has a one to many relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table when
the FORM is updated?

Jeff

Since the subform rather than the main form uses tblHistory as its record
source, updating tblHistory is best done through the subform. I don't know
what you meant about tblHistory automatically updating when you update its
record using the main form, since you wouldn't update a record in tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In the case of
tblBuilding, that information may include address, number of rooms, date
built, and so forth. It would not include information about transactions.
These belong in another related table.
What do you plan to do when you need to add something to the History? Are
you planning to move data from one table to another to make room for new
data?
If you want you can show just the first record (the most recent transaction)
in a subform, then click a button to show the rest of the history. Or you
can have the subform open to the most recent History item. Or maybe you
could make two subforms bound to tblHistory: one for the most recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I update
its record using the main FORM. My goal is to have a HISTORY table that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the HISTORY
table will have all of the prior transactions. My guess is that certain
fields would have to be copied to the HISTORY table upon an update but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of fsubHistory
to
Continuous so that you can see several history items at once. With
frmBuilding open in design view, drag the icon for fsubHistory onto it.
This creates a form/subform based on the related tables. Add building
information via the main form, and history information via the subform as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each building
has a unique ID and the BUILDING table has a one to many relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table when
the FORM is updated?

Jeff

Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old data
to automatically be copied to tblHISTORY. tblHISTORY and tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old data and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as its record
source, updating tblHistory is best done through the subform. I don't know
what you meant about tblHistory automatically updating when you update its
record using the main form, since you wouldn't update a record in tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In the case of
tblBuilding, that information may include address, number of rooms, date
built, and so forth. It would not include information about transactions.
These belong in another related table.
What do you plan to do when you need to add something to the History? Are
you planning to move data from one table to another to make room for new
data?
If you want you can show just the first record (the most recent transaction)
in a subform, then click a button to show the rest of the history. Or you
can have the subform open to the most recent History item. Or maybe you
could make two subforms bound to tblHistory: one for the most recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I update
its record using the main FORM. My goal is to have a HISTORY table that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the HISTORY
table will have all of the prior transactions. My guess is that certain
fields would have to be copied to the HISTORY table upon an update but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of fsubHistory
to
Continuous so that you can see several history items at once. With
frmBuilding open in design view, drag the icon for fsubHistory onto it.
This creates a form/subform based on the related tables. Add building
information via the main form, and history information via the subform as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each building
has a unique ID and the BUILDING table has a one to many relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table when
the FORM is updated?

Jeff

Rather than calling it tblHistory, think of it as tblTransaction. Design
the two tables with the one-to-many relationship I originally suggested
(between the two BuildingID fields). Now build the form/subform as I
suggested. You may look at the latest transaction, or the history of all
transactions. There is no need to move the Transaction history data to
another table. Instead you design the form so that only the current
transaction is shown.

Try building it as I suggested. You will have all of the transactions at
hand, but we can take care of how the information is displayed. I believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old data
to automatically be copied to tblHISTORY. tblHISTORY and tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old data and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as its record
source, updating tblHistory is best done through the subform. I don't
know
what you meant about tblHistory automatically updating when you update
its
record using the main form, since you wouldn't update a record in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In the case of
tblBuilding, that information may include address, number of rooms, date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the History?
Are
you planning to move data from one table to another to make room for new
data?
If you want you can show just the first record (the most recent
transaction)
in a subform, then click a button to show the rest of the history. Or
you
can have the subform open to the most recent History item. Or maybe you
could make two subforms bound to tblHistory: one for the most recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I update
its record using the main FORM. My goal is to have a HISTORY table that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the HISTORY
table will have all of the prior transactions. My guess is that certain
fields would have to be copied to the HISTORY table upon an update but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of
fsubHistory
to
Continuous so that you can see several history items at once. With
frmBuilding open in design view, drag the icon for fsubHistory onto
it.
This creates a form/subform based on the related tables. Add building
information via the main form, and history information via the subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table
when
the FORM is updated?

Jeff

OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building data
AND transaction data to tblBUILDING, do I have to separate the data or
can I have the main form send some fields to tblBUILDING and other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as tblTransaction. Design
the two tables with the one-to-many relationship I originally suggested
(between the two BuildingID fields). Now build the form/subform as I
suggested. You may look at the latest transaction, or the history of all
transactions. There is no need to move the Transaction history data to
another table. Instead you design the form so that only the current
transaction is shown.

Try building it as I suggested. You will have all of the transactions at
hand, but we can take care of how the information is displayed. I believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old data
to automatically be copied to tblHISTORY. tblHISTORY and tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old data and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as its record
source, updating tblHistory is best done through the subform. I don't
know
what you meant about tblHistory automatically updating when you update
its
record using the main form, since you wouldn't update a record in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In the case of
tblBuilding, that information may include address, number of rooms, date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the History?
Are
you planning to move data from one table to another to make room for new
data?
If you want you can show just the first record (the most recent
transaction)
in a subform, then click a button to show the rest of the history. Or
you
can have the subform open to the most recent History item. Or maybe you
could make two subforms bound to tblHistory: one for the most recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I update
its record using the main FORM. My goal is to have a HISTORY table that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the HISTORY
table will have all of the prior transactions. My guess is that certain
fields would have to be copied to the HISTORY table upon an update but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of
fsubHistory
to
Continuous so that you can see several history items at once. With
frmBuilding open in design view, drag the icon for fsubHistory onto
it.
This creates a form/subform based on the related tables. Add building
information via the main form, and history information via the subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table
when
the FORM is updated?

Jeff

I believe there are ways of using code to write to several different tables,
but that isn't the way to go here. First of all, make a copy of your
database. After that, make a query based on tblBuilding, containing only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer, Seller) and
BuildingID. It will be simplest if tblHistory uses the same field names.
Make the query an Append query (Help has more information about that) to
append the data into tblHistory. With the relationship I suggested between
tblBuilding and tblHistory, and the form/subform as I described, this new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can confirm with
the form/subform that the tblHistory records contain the information they
should. Once you have verified the data you can delete the fields from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones already in
tblBuilding (Bank information, maybe?) or new ones (Comments, perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo into
tblHistory. Don't worry about HistoryID. Since it is autonumber it will be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design view, but
that the foreign key (FK) field comes into being because of its relationship
with the PK field. You don't define the FK in the same way as you do the
PK. Also, note that the FK field is the same data type as the PK field to
which it is related, unless the PK field is Autonumber, in which case the FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID fields) as
described previously. For the buyer and seller fields in tblHistory, create
combo boxes on the subform. You will store BuyerSellerID, but display the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as needed. The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building data
AND transaction data to tblBUILDING, do I have to separate the data or
can I have the main form send some fields to tblBUILDING and other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as tblTransaction. Design
the two tables with the one-to-many relationship I originally suggested
(between the two BuildingID fields). Now build the form/subform as I
suggested. You may look at the latest transaction, or the history of all
transactions. There is no need to move the Transaction history data to
another table. Instead you design the form so that only the current
transaction is shown.

Try building it as I suggested. You will have all of the transactions at
hand, but we can take care of how the information is displayed. I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old data
to automatically be copied to tblHISTORY. tblHISTORY and tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old data and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as its
record
source, updating tblHistory is best done through the subform. I don't
know
what you meant about tblHistory automatically updating when you update
its
record using the main form, since you wouldn't update a record in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In the case
of
tblBuilding, that information may include address, number of rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the History?
Are
you planning to move data from one table to another to make room for
new
data?
If you want you can show just the first record (the most recent
transaction)
in a subform, then click a button to show the rest of the history. Or
you
can have the subform open to the most recent History item. Or maybe
you
could make two subforms bound to tblHistory: one for the most recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I update
its record using the main FORM. My goal is to have a HISTORY table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the HISTORY
table will have all of the prior transactions. My guess is that
certain
fields would have to be copied to the HISTORY table upon an update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of
fsubHistory
to
Continuous so that you can see several history items at once. With
frmBuilding open in design view, drag the icon for fsubHistory onto
it.
This creates a form/subform based on the related tables. Add
building
information via the main form, and history information via the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table
when
the FORM is updated?

Jeff

OK
I have the tables and forms set up per your suggestion and everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform bound
to tblHISTORY. Each form has its own navigation bar which I think is a
bit confusing. Can I hide the navigation bar to the subform and make it
available on demand?
2. I have the subform setup to show only the most recent entry. What is
a simple way to allow access to earlier entries ie. the tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several different tables,
but that isn't the way to go here. First of all, make a copy of your
database. After that, make a query based on tblBuilding, containing only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer, Seller) and
BuildingID. It will be simplest if tblHistory uses the same field names.
Make the query an Append query (Help has more information about that) to
append the data into tblHistory. With the relationship I suggested between
tblBuilding and tblHistory, and the form/subform as I described, this new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can confirm with
the form/subform that the tblHistory records contain the information they
should. Once you have verified the data you can delete the fields from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones already in
tblBuilding (Bank information, maybe?) or new ones (Comments, perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo into
tblHistory. Don't worry about HistoryID. Since it is autonumber it will be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design view, but
that the foreign key (FK) field comes into being because of its relationship
with the PK field. You don't define the FK in the same way as you do the
PK. Also, note that the FK field is the same data type as the PK field to
which it is related, unless the PK field is Autonumber, in which case the FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID fields) as
described previously. For the buyer and seller fields in tblHistory, create
combo boxes on the subform. You will store BuyerSellerID, but display the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as needed. The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building data
AND transaction data to tblBUILDING, do I have to separate the data or
can I have the main form send some fields to tblBUILDING and other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as tblTransaction. Design
the two tables with the one-to-many relationship I originally suggested
(between the two BuildingID fields). Now build the form/subform as I
suggested. You may look at the latest transaction, or the history of all
transactions. There is no need to move the Transaction history data to
another table. Instead you design the form so that only the current
transaction is shown.

Try building it as I suggested. You will have all of the transactions at
hand, but we can take care of how the information is displayed. I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old data
to automatically be copied to tblHISTORY. tblHISTORY and tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old data and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as its
record
source, updating tblHistory is best done through the subform. I don't
know
what you meant about tblHistory automatically updating when you update
its
record using the main form, since you wouldn't update a record in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In the case
of
tblBuilding, that information may include address, number of rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the History?
Are
you planning to move data from one table to another to make room for
new
data?
If you want you can show just the first record (the most recent
transaction)
in a subform, then click a button to show the rest of the history. Or
you
can have the subform open to the most recent History item. Or maybe
you
could make two subforms bound to tblHistory: one for the most recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I update
its record using the main FORM. My goal is to have a HISTORY table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the HISTORY
table will have all of the prior transactions. My guess is that
certain
fields would have to be copied to the HISTORY table upon an update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of
fsubHistory
to
Continuous so that you can see several history items at once. With
frmBuilding open in design view, drag the icon for fsubHistory onto
it.
This creates a form/subform based on the related tables. Add
building
information via the main form, and history information via the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY table
when
the FORM is updated?

Jeff

I got very busy today and did not get back to the newsgroup until I was
almost ready to leave for the day. Quick answers for now, with more detail
to follow.

1. Yes, you can hide the navigation arrows, or you can create your own. I
don't have time to go into the details today, but it is not all that
complex.

2. I may be missing something in the question, but you would use the
navigation arrows to move to previous records. However, if you have subform
data Entry property set to Yes I don't think you will see the previous
records. How have you set it up to see just the most recent record?

"Techknownothing" wrote in message
ups.com...
OK
I have the tables and forms set up per your suggestion and everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform bound
to tblHISTORY. Each form has its own navigation bar which I think is a
bit confusing. Can I hide the navigation bar to the subform and make it
available on demand?
2. I have the subform setup to show only the most recent entry. What is
a simple way to allow access to earlier entries ie. the tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several different
tables,
but that isn't the way to go here. First of all, make a copy of your
database. After that, make a query based on tblBuilding, containing only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer, Seller)
and
BuildingID. It will be simplest if tblHistory uses the same field names.
Make the query an Append query (Help has more information about that) to
append the data into tblHistory. With the relationship I suggested
between
tblBuilding and tblHistory, and the form/subform as I described, this new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can confirm
with
the form/subform that the tblHistory records contain the information they
should. Once you have verified the data you can delete the fields from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones already in
tblBuilding (Bank information, maybe?) or new ones (Comments, perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo into
tblHistory. Don't worry about HistoryID. Since it is autonumber it will
be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design view, but
that the foreign key (FK) field comes into being because of its
relationship
with the PK field. You don't define the FK in the same way as you do the
PK. Also, note that the FK field is the same data type as the PK field
to
which it is related, unless the PK field is Autonumber, in which case the
FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and
sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID fields) as
described previously. For the buyer and seller fields in tblHistory,
create
combo boxes on the subform. You will store BuyerSellerID, but display
the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as needed.
The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building data
AND transaction data to tblBUILDING, do I have to separate the data or
can I have the main form send some fields to tblBUILDING and other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as tblTransaction.
Design
the two tables with the one-to-many relationship I originally
suggested
(between the two BuildingID fields). Now build the form/subform as I
suggested. You may look at the latest transaction, or the history of
all
transactions. There is no need to move the Transaction history data
to
another table. Instead you design the form so that only the current
transaction is shown.

Try building it as I suggested. You will have all of the transactions
at
hand, but we can take care of how the information is displayed. I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my
main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a
uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old
data
to automatically be copied to tblHISTORY. tblHISTORY and tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old data
and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as its
record
source, updating tblHistory is best done through the subform. I
don't
know
what you meant about tblHistory automatically updating when you
update
its
record using the main form, since you wouldn't update a record in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In the
case
of
tblBuilding, that information may include address, number of rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the
History?
Are
you planning to move data from one table to another to make room
for
new
data?
If you want you can show just the first record (the most recent
transaction)
in a subform, then click a button to show the rest of the history.
Or
you
can have the subform open to the most recent History item. Or
maybe
you
could make two subforms bound to tblHistory: one for the most
recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I
update
its record using the main FORM. My goal is to have a HISTORY
table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the
HISTORY
table will have all of the prior transactions. My guess is that
certain
fields would have to be copied to the HISTORY table upon an
update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of
fsubHistory
to
Continuous so that you can see several history items at once.
With
frmBuilding open in design view, drag the icon for fsubHistory
onto
it.
This creates a form/subform based on the related tables. Add
building
information via the main form, and history information via the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a
table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY
table
when
the FORM is updated?

Jeff