Yes, unfortunately, I have the names listed in 2 tables. I inherited a
database that includes all data in one large table but should really be
broken down into several smaller tables. I have been trying to figure out
the best way to do this.
I have one large Payroll table but I also have an Employees table and a
recently created Clients table. I know that I need to add PK ID fields to
the Employee and Client tables and use these ID's as FK's in the Payroll
table instead of the actual names but I've been afraid to remove the
duplicate values from the larger table for fear that I will corrupt the data
that is already in the table.
I'm considering whether I should start over and create all of the tables I
need from scratch or to just break down the larger table into smaller tables
of related data (maybe using the Table Analyzer as a starting point). One
fear is that I have a great deal of reports already created using the
database like it is and I am afraid that if I start over that I will not be
able to get the reports designed correctly.
What do you suggest?
--
JWeaver
Klatuu said:
Are you saying you are using names from one table to enter into another table?
If so, you really should not do that. It violates the data redundancy rules
for database normalization.
The correct design is to carry the name fields in only one table. In the
other table, you would use the EmployeeID as a foreign key and theny use a
query when you want to show the name with the other record.
And, it sounds like you are using the combo as a bound control. So, I need
a bit more detail on what it is you are trying to accomplish, please/
--
Dave Hargis, Microsoft Access MVP
JWeaver said:
Thanks, Dave! I tried your suggestion and it works. However, my problem is
that the names that were already in the Last Name field are not updated to
include the First Name also. How do I do this?
Your suggestion works much better than mine since I noticed when I went back
over my data on my Form that even though the First Name or Last Name was
correct, the corresponding other half of the name may not have been if there
were more than one with the same name, i.e., Michael as First Name or Smith
as Last Name. I am afraid that this would mess up my data if I left this so
I turned all of the combo boxes I had back to Text boxes. I don't like the
Text boxes though because my data could become inconsistent which is why I
wanted to use Combo Boxes instead.
--
JWeaver
:
You really proabably don't need two boxes. I would suggest:
SELECT EmID, FirstName & " " & LastName AS Full Name FROM tblEmployee;
That way, both first and last names will be visible. If you are using the
combo for a search, make Column 1 the bound column and set the column widths
so the first column is hidden. For example 0";2.5"
Then the name will show, the ID will be hidden, and you search on the ID.
--
Dave Hargis, Microsoft Access MVP
:
Disregard. I changed the SELECT order and was able to get it to display the
first name in the box.
--
JWeaver
:
I have a Text Box on a Form that I am trying to change to a Combo Box. I
pull the last name and first names from the Employees Table to show in the
combo boxes. Every time I do this, either with the wizard or manually, the
last name still displays in the first name box. I tried telling it that the
bound column is the 3rd one since the Emp ID is also in the combo box
although not displayed but this doesn't work. The properties of the combo
box indicate there are 3 columns with the Row Source being:
SELECT Employees.[Emp ], Employees.[Last Name], Employees.[First Name] FROM
Employees ORDER BY [Last Name], [First Name];
The one I converted for Last Name worked fine when I changed the bound
column to 2 but the one for First Name doesn't work if I change the bound
column to 3. What am I missing?
