Use one form for two tables

  • Thread starter Thread starter tina
  • Start date Start date
T

tina

you could do this by using an unbound form, and opening a Recordset to
update the chosen table. but may i ask why you have two identical tables for
different models? suggest you try using 1 table, with a field for Model, so
in each record you can enter the appropriate model.

hth
 
Help...

I want to use one form to enter data into 2 tables.

One table called Car1
One called car 2

All fields within the tables are the same except the model of the car. This
has a drop down field with 2 cars a and b. I want the information I enter
into the form to be sent to the correct table depending on weather u choose
car a or car b.

TIA
 
Im creating a databse for car service for 2 cars in the family.I could
create 2 forms but I would rather the field in one form dictate where the
infomation is stored.
 
PHroGman said:
Im creating a databse for car service for 2 cars in the family.I could
create 2 forms but I would rather the field in one form dictate where
the infomation is stored.
Click to expand...

So use one form, with one table and add a field to designate which car.
 
what happens when you trade in one of your cars? or buy a third vehicle?
building a separate table for each vehicle violates data normalization
rules, and will create more work for you in the long run. if you're going to
use Access to track the service done on your cars, you might as well build
the database correctly and make your life easier - and the aggregate data
more useful to you. recommend three tables, as

tblVehicles
VehicleID (primary key field)
Make
Model
ModelYear
DatePurchased
DateSold

tblVehicleServices
ServiceID (primary key, Autonumber)
fkVehicleID (foreign key from tblVehicles)
VehicleType (car, truck, van, boat, quad, etc)
ServiceDate
PlaceOfService
ServiceCost

tblServiceDetail
DetailID (primary key, Autonumber)
fkServiceID (foreign key from tblVehicleServices)
ServiceDescription (oil change, rotate tires, smog check, etc)

for a professional database, i would recommend more tables - but for your
personal use, the above keeps it nice and simple. create a form bound to
tblVehicles. after adding the vehicles you currently own, you'll only enter
data in this form when you initally get a vehicle. create a mainform/subform
setup (main form bound to tblVehicleServices, subform bound to
tblServiceDetails). in the main form, use a combo box control to enter the
fkVehicleID. base the RowSource of the combo box on tblVehicles, so you can
choose the vehicle for each service record.

setting up the tables correctly means you'll be able to query the database
to find out: how much you're spending on each car per month or year, and
how much total; how often you're having different kinds of service done; how
much the total cost of service on each car increases (or decreases) from
year to year, etc, etc. in short, you can slice 'n dice the data to tell you
just about anything you want to know about how much your cars are costing
you to maintain.

hth
 
Great Idea....Thanks...

tina said:
what happens when you trade in one of your cars? or buy a third vehicle?
building a separate table for each vehicle violates data normalization
rules, and will create more work for you in the long run. if you're going to
use Access to track the service done on your cars, you might as well build
the database correctly and make your life easier - and the aggregate data
more useful to you. recommend three tables, as

tblVehicles
VehicleID (primary key field)
Make
Model
ModelYear
DatePurchased
DateSold

tblVehicleServices
ServiceID (primary key, Autonumber)
fkVehicleID (foreign key from tblVehicles)
VehicleType (car, truck, van, boat, quad, etc)
ServiceDate
PlaceOfService
ServiceCost

tblServiceDetail
DetailID (primary key, Autonumber)
fkServiceID (foreign key from tblVehicleServices)
ServiceDescription (oil change, rotate tires, smog check, etc)

for a professional database, i would recommend more tables - but for your
personal use, the above keeps it nice and simple. create a form bound to
tblVehicles. after adding the vehicles you currently own, you'll only enter
data in this form when you initally get a vehicle. create a mainform/subform
setup (main form bound to tblVehicleServices, subform bound to
tblServiceDetails). in the main form, use a combo box control to enter the
fkVehicleID. base the RowSource of the combo box on tblVehicles, so you can
choose the vehicle for each service record.

setting up the tables correctly means you'll be able to query the database
to find out: how much you're spending on each car per month or year, and
how much total; how often you're having different kinds of service done; how
much the total cost of service on each car increases (or decreases) from
year to year, etc, etc. in short, you can slice 'n dice the data to tell you
just about anything you want to know about how much your cars are costing
you to maintain.

hth
Click to expand...
 
oops, i blew it.
the field

VehicleType (car, truck, van, boat, quad, etc)

should be in tblVehicles, *not* in tblVehicleServices. after all, the
vehicle type of a specific vehicle is not going to change from one instance
of service to another! <g>
sorry 'bout that.
 
Hi Tina,

I have two tables that I would like to update with one form. The form has 8
fields. All 8 are needed for a record insert into Tbl1 but only fields #1,5,7
are needed for an insert into Tbl2. Can you please recommend a few URL's on
how to best accomplish this?

Thanks,
--Paul
 
it sounds like you're duplicating data in your database, Paul. that violates
data normalization rules, and is rarely necessary in "real world"
situations. suggest you read up on data normalization principles and then
review the structure of your tables and their relationships - you may well
find that you don't need to capture your form data in two tables, after all.
see the links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
(watch for line-wrap on the above link).
if you have questions after doing that, suggest you start a new thread in
the appropriate Access newsgroup. recommend you don't post back to this
thread - it's so old that the newsgroup community is not going to be
watching it anymore. the only reason i saw it, is because i'm really behind
on cleaning out the "watched" threads in my newsreader. :)

hth
 
Hi Tina,

You are right. I am duplicating data. One table is historical and the second
table is dimensional. At this point I am experimenting and am interested in
learning how to do something to build my abilities. Although this may not be
a correct use of this functionality, I believe it would be worthwhile to know
nonetheless. To your second point. I will start a new thread. Please accept
my sincere thank you for both pieces of advice.

Thanks,
--Paul
 
Back
Top