There may be various ways to do this, but my suggestions follow...
Hi Im new user of access and im having trouble doing somehing that seems
really simple when I picture it, but I cant make it work,
I have two tables, One has the following fields:
ID CarModel CarColor
1 Ford Red
2 Ford Black
3 Fiat Blue
Since you're going to be using "Red" and "Fiat" as lookup values, I
suggest putting them into Tables such as this one:
[CarModel]
Model
-----
Ford
Fiat
.... but you should also include a "primary key" field with a unique
value that Access can use to link Tables. (See examples below.)
Each car needs a manteniance table (table2) that will store hystoric data
For example the Ford-Red on table 1 has;
ID Date Manteniance
1 10/02/05 oil change
2 11/02/05 general inspection
3 12/02/05 other
I want to merge the two tables to show in a form the hystoric data when the
user picks a "CarModel" and "CarColor" fields.
When the user do it, the specific "Date" and "Manteniance" details need to
appear.
I dont know how to make the relationship...Or maybe im wrong with the way I
have formatted the tables.
Any comment will be really appreciated
Thanks!
OK, what I did was to set up the following four Tables (in order from
most general to most specific). Normally, I would use Queries to do the
lookups, but to save time I skipped them here. Each one except the
first contains a foreign key linking it to the previous one; for
example, [CarColor] contains [CarColor].[CarModelID] that matches a
value in [CarModel].[CarModelID]. Each one contains a unique
identifying number (an Autonumber field) as its first field, the Table's
primary key, and I gave it a name the same as the Table name but
followed by "ID".
[CarModel]
CarModelID Model
---------- -----
1574172655 Ford
2143506180 Fiat
[CarColor]
CarColorID Color CarModelID
----------- ----- ----------
-1979122453 Blue 2143506180
-1931191086 Black 1574172655
-1689093195 Red 1574172655
Here, both "Black" and "Red" are linked to the number of the "Ford"
record in [CarModel].
To make these easier to read, I defined lookup values on the foreign key
field. (Normally I'd use a Query to do this, but here I just grabbed a
name from the target Table.)
If you want to do this, open the Table in Table Design View, select the
foreign key ([CarModelID] in this case), and select the Lookup tab.
Change the properties as follows:
Display Control: List Box
Row Source: CarModel
Column Count: 2
Column Widths: 0;1
Switch back to Table Datasheet View, and the list changes to look like this:
[CarColor]
CarColorID Color CarModelID
----------- ----- ----------
-1979122453 Blue Fiat
-1931191086 Black Ford
-1689093195 Red Ford
This Table still contains the same numbers as before in the [CarModelID]
field, they just are displayed using the names taken from the linked
Table; the actual numbers are invisible. You might also want to make
the primary key of this Table, [CarColorID], invisible by right-clicking
on the column title and selecting "Hide Columns". What's left would
then look like this, but the Table would still contain all the necessary
key values:
[CarColor]
Color CarModelID
----- ----------
Blue Fiat
Black Ford
Red Ford
The other Tables that I set up look like these:
[Cars]
CarsID Owner CarColorID
----------- ----- ----------
-614625794 Vince Black
982488912 snake Red
1768381532 snake1 Blue
The [Cars].[Owner] field I added as a way to distinguish among red Ford
cars, in case you encounter more than one.
[Maintenance]
MaintenanceID Date Maintenance CarsID
------------- --------- ------------- ------
-1859159791 10/2/2005 oil change snake
-1501288025 11/2/2005 general insp. Vince
-1356471251 12/2/2005 other snake1
Here, using a lookup Query for [CarsID] would have been far preferable
to using [Cars].[Owner] for lookup as I did here, since the owner's name
might not tell much about the car. Also, if one person owned two cars,
there'd be no way to distinguish the cars. A more meaningful name might
be "snake Red Ford" or "sna/Red/Fo", and you could easily do that using
a Query.
Having defined the Tables, you can link them into a series of
subdatasheets to do what you suggested (letting the user pick
[CarModel], etc., and seeing the maintenance records). To do this, open
[CarModel] in Table Datasheet View, select Insert --> Subdatasheet, and
specify the [CarColor] Table. (Access will guess which fields need to
be linked, so you don't have to specify them.) If you are asked if you
want to create a Relationship between the Tables, answer yes. Do the
same thing with [CarColor] and [Cars]. At this point, you'll have a
rudimentary way of entering and editing data in the Tables according to
what you said you wanted to do.
Although what I suggested here is a quick-and-dirty way to get started,
you'll most likely want to define some data-entry Forms to present the
proper choices and allow values to be changed, and you'll want to define
Queries to display values in linked Tables, sort them as you'd like,
etc. Once the Tables are working properly, you'll probably also want to
define Reports to allow you to print out or display the contents in a
usable format.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
