junction table needed?

[AngiW]

I have and employee db that contains hiring info, etc. and then an accrual db
which handles payroll info, vac hours, etc. I want the person using the the
accrual db to pull the employees from the other db. I've done that through
lookup table, but it's not working right and i know it has to do with my
relationships. My accrual table does not have a primary key. The employee
table has EmployeeID which is what i used to join them, but obviously, this is
wrong. I keep getting type mismatch errors when i try to update with
conditions relating to the employees table. The accrual person is going to
look up by last name, not ID. What do i need to do?

Thanks!

 

[Tim Ferguson]

(e-mail address removed) (AngiW) wrote in

This is all a bit confusing, but I'll have a go at teasing out the
threads...

I have and employee db that contains hiring info, etc. and then an
accrual db which handles payroll info, vac hours, etc.

I take it you mean tables, not databases. If we are talking about separate
databases, then most of the things that (I think) you want to do can't be
done.

I want the
person using the the accrual db to pull the employees from the other
db.

This is a problem. Users use databases, not tables -- that is to say, what
the users see should be seamless information and there is no reason why
they should be aware of what table structures lie behind the forms.

Still, press on...

I've done that through lookup table, but it's not working right
and i know it has to do with my relationships. My accrual table does
not have a primary key.

In that case it's not a table. A table is _defined_ by its primary key. If
you are not going to use Access as a relational database, then it's
probably not possible to help.

The employee table has EmployeeID which is
what i used to join them, but obviously, this is wrong.

I am still very unclear about what your tables are representing. I know
what an Employee is, and can imagine what sort of fields one might have. I
don't know what an Accrual is, though, or what attributes one might have.
You did hint above at some things that might be entities: Hirings,
PaySlips, Vacations, etc.

Remember that in databases, TABLES represent things, while FORMS represent
processes. I have a feeling that an Accrual might actually be a process,
and therefore ought to have a form attached to it: the tables design really
needs a lot of proper thought first.

Hope that helps



Tim F

 

[AngiW]

Tim,
Thanks for the post and i'm sorry for confusing you. I have two databases
Employee and Accruals. The accruals db has accrual main table and linked
employee main table. You are right about the accrual database running a
process and it does...without error... As long as I don't use the fields from
the employee table. My problem is trying to join the two tables together using
the employee as the join. Like i said, i know it has to do with my
relationships and/or possible a junction table for the two, but I don't know
what it should be. Here are the fields that matter...

Employee table:
EmployeeID (primary key)
FirstName
LastName
Status (this is f/t or p/t)

Accruals table: (no primary key or otherwise)
EmployeeID (which is currently what I have the relationship one-to-many on)
EmpLName
EmpFName
SickAvail

The query I'm trying to run is do the "process" when Status is Full Time. It
doesn't know who to update because I don't have them linked right. I hope this
is clearer...I'm sorry. Mind knows what it needs, just telling everyone else
is the problem.

Thanks for your time and patience!

 

[AngiW]

OK, i see one thing i did wrong...instead of looking up the name, just use the
name from the employee table. i looked at another db and to see if i could
figure out the junction table. I'm halfway there...i have EmployeeID, PageID
(for accrual main table) and the JoinID. I have the relationships set for
pageID going to join's pageID and employeeID going to join's employeeID. After
that, i'm lost. How do I update the accrual main table with what i need based
on the employee status in the Employee main table? Confused yet???

 

[Tim Ferguson]

(e-mail address removed) (AngiW) wrote in

I have two
databases Employee and Accruals. The accruals db has accrual main
table and linked employee main table.

Well, if there are two tables in different databases, then they are not
linked. Full stop.

Accruals table: (no primary key or otherwise)

As I said before, if there's no PK then it's not a table.

There is, of course, no reason to use Access as a relational database, just
as there is no reason to use any of the word processing capabilities of
Word. I have to admit, though, that if you want to use if for anything
elsee then I don't have the knowledge to help you.

Sorry.

Best wishes


Tim F