|  | Combo Box Form Query - Erases Existing Fields When Formatted | | |
| | | als |  |
|  Posted: Wed Aug 27, 2008 5:05 pm Post subject: Combo Box Form Query - Erases Existing Fields When Formatted |  |
| |  | |
I have a .mdb table (TreeDatabase2008_Points_sp) Linked in to my
access database.
From that linked table I have created a form (with the form wizard)
that will be used for adding and editing values in the table.
By the way, this table has approximately 15 fields and 18000 records
in each field and i am using Access 2007 on a windows xp machine.
For the ease of add/ editing the data, I have created a combo box with
the following query for my first field:
SELECT DISTINCT TreeDatabase2008_Points_sp.Street
FROM TreeDatabase2008_Points_sp
ORDER BY TreeDatabase2008_Points_sp.Street;
This query allows all of the existing records to be in alphabetical
order and show no duplications.
The problem is: Once I run the query, all of the Existing data (18000
records) that was in the field in the form is deleted. It still
appears in the table but it is gone from the form.
I am a new user to Access and I'm sure this is probably an elementary
question but if anyone could help I would really appreciate it.
Thanks,
alison |
| |
| | | Ken Sheridan | |
| | Posted: Wed Aug 27, 2008 9:11 pm Post subject: RE: Combo Box Form Query - Erases Existing Fields When Forma | |
| | | |
Alison:
To be honest I'm not sure what's happening with your form, so I'll start
from square one. I'll come back to the form below (step 7).
1. Using a combo box is the right thing to do, but t should not look up
values from the same table, but from a separate 'referenced' table, often
referred to as a 'look-up' table.
2. The referenced table. lets call it Streets will have one row per street,
but here you come up against a problem, because street names can be
duplicated, so its best to give the Streets table an autonumber column
StreetID as its primary key.
3. Streets are in most databases at the bottom level of a hierarchy of
locations, e.g. streets are in towns which are in counties and so on (or
equivalent geographical units depending on what country you are in). So if
you are recording locations beyond street level the Streets table might
reference a Towns table with a TownID column and the Towns table might have
a CountyID column referencing a Counties table, and so on up the hierarchy if
you are going up to Country level say.
4. The key thing to understand here is that in your main
TreeDatabase2008_Points_sp table does not include any fields for County or
beyond, only the TownID column. If you know the town, you know the county
and so on via the relationships between the tables. This is how relational
databases work; by representing each 'entity type' by a separate table and
relating them so that each 'fact' is only store once in the database, thus
eliminating redundancy and the risk of inconsistent data. The process of
eliminating redundancy by 'decomposing' a table into more than one table is
known as 'normalization'.
5. If you do currently have all the levels in the main table its actually
quite simple to fill the other tables using a series of 'append' and 'update'
queries, e.g. to fill the Streets table (after you've created the empty
table) you'd use:
INSERT INTO Streets (Street)
SELECT DISTINCT Street
FROM TreeDatabase2008_Points_sp;
6. Then you'd add a StreetID column of long integer number data type to the
TreeDatabase2008_Points_sp table and fill it with an 'update' query:
UPDATE TreeDatabase2008_Points_sp INNER JOIN Streets
ON Streets,Street = TreeDatabase2008_Points_sp.Street
SET TreeDatabase2008_Points_sp.StreetID = Streets.StreetID;
7. Back in your form bound to the TreeDatabase2008_Points_sp table you
should have a combo box bound to the StreetID field, set up as follows:
ControlSource: StreetID
RowSource: SELECT StreetID, Street FROM Streets ORDER BY Street;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
8. You can now select a street by name from the combo box by name, but its
underlying value will be the numeric StreetID.
9. The above assumes that all street names are unique, and from what you
say it sounds like this is the case with your current data, so it will work
fine. If you find that you need to record two or more separate streets of
the same name then you'd have separate rows for each in the Streets table,
with different StreetID values. The combo box on your form would not handle
that situation as you would not know from it which identically named street
is the correct one. To deal with that you need to show more than one column
in the combo box's list, e.g. Street and Town, so that you can select the
correct one. Perhaps, to avoid confusing you any further than I've probably
done already, that's best left for later. How the town etc for the selected
street is shown on the form after you've selected the street is also
something we can come back to.
Ken Sheridan
Stafford, England
"als" wrote:
| Quote: | I have a .mdb table (TreeDatabase2008_Points_sp) Linked in to my
access database.
From that linked table I have created a form (with the form wizard)
that will be used for adding and editing values in the table.
By the way, this table has approximately 15 fields and 18000 records
in each field and i am using Access 2007 on a windows xp machine.
For the ease of add/ editing the data, I have created a combo box with
the following query for my first field:
SELECT DISTINCT TreeDatabase2008_Points_sp.Street
FROM TreeDatabase2008_Points_sp
ORDER BY TreeDatabase2008_Points_sp.Street;
This query allows all of the existing records to be in alphabetical
order and show no duplications.
The problem is: Once I run the query, all of the Existing data (18000
records) that was in the field in the form is deleted. It still
appears in the table but it is gone from the form.
I am a new user to Access and I'm sure this is probably an elementary
question but if anyone could help I would really appreciate it.
Thanks,
alison
|
|
| |
|
|
