Stumped on one query for a form Please help

[Curt]

I have two tables that are related by more than one
Primary key from one table to another.
for example

tblCustomer
firstname PK
lastname PK
address PK
phone
etc

tblEmployee
EmployeeID PK
firstlastaddress FK


tblVisit
ID PK
firstlastaddress FK
EmployeeID FK
ReasonforVisit FK
etc
etc

TblCustomer and tblVisit are used for initial sign-in for
each customer and visit. When someone signs in they can
choose the reason for their visit. If they are in the
database already then it adds a visit for the employees to
see and take care of. If they are not in the database
then it adds them to tblCustomer and TblEmployee.
Employees can also be customers which is assigns them as
employees through another form. Once the employee takse
care of them then it shouold assign their ID along with
time, etc in tblVisit for that particular customer.

The problem I have is when when I try to complete a task
on a form and assign the EmployeeID to tblVisit against a
particular customer. For some reason it won't allow me.
Can someone provide a suggestion on how to create a query
that I can use as a recordsource for the form.

I know I should use a CustomerID in tblCustomer, which
would make everything easier. However, I am too far into
the database to start over and it appears to work every
elese so far.

If I have to start over how would I make the changes on
the sign-in form for it to check the info to see if the
person is in the db already?

Thanks for any positive input

 

[MGFoster]

I have two tables that are related by more than one
Primary key from one table to another.
for example

tblCustomer
firstname PK
lastname PK
address PK
phone
etc

tblEmployee
EmployeeID PK
firstlastaddress FK


tblVisit
ID PK
firstlastaddress FK
EmployeeID FK
ReasonforVisit FK
etc
etc

TblCustomer and tblVisit are used for initial sign-in for
each customer and visit. When someone signs in they can
choose the reason for their visit. If they are in the
database already then it adds a visit for the employees to
see and take care of. If they are not in the database
then it adds them to tblCustomer and TblEmployee.
Employees can also be customers which is assigns them as
employees through another form. Once the employee takse
care of them then it shouold assign their ID along with
time, etc in tblVisit for that particular customer.

The problem I have is when when I try to complete a task
on a form and assign the EmployeeID to tblVisit against a
particular customer. For some reason it won't allow me.
Can someone provide a suggestion on how to create a query
that I can use as a recordsource for the form.

I know I should use a CustomerID in tblCustomer, which
would make everything easier. However, I am too far into
the database to start over and it appears to work every
elese so far.

If I have to start over how would I make the changes on
the sign-in form for it to check the info to see if the
person is in the db already?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why not have a Visit subform linked to a Client master form that uses
the "firstlastaddress" value as the linking field? On the Visit subform
have a ComboBox of Employees (EmployeeID, EmployeeName) that stores the
selected EmployeeID in the Visits table.

See the Access Help for info on subforms & combo boxes.

===

In the long run, it will probably be a better idea to use a CustomerID
(AutoNumber - Unique index) rather than the combined firstlastaddress
field. If you're using Access 2000, or greater version, & have the
"Track name AutoCorrect" option turned on, you may be able to change the
name of "firstlastaddress" to "CustomerID" and it will propogate
throughout the application. Then you'll have to change the data type in
the Customers table to AutoNumber & in the other tables to Long integer.
You'll have to break the relationship links between the affected tables
before changing the columns' data types.

Some work, but not insurmountable.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQH2FcoechKqOuFEgEQKZaACfQ6OATH6i5/NORMN8FZa8RYwtzc0AoPsI
3v8UIP4+ndcTfqymCVJ8lZLm
=1Jed
-----END PGP SIGNATURE-----