What type of query?

M

mikebo

I am working on a little project in Access and I am stumped. I am not a
frequent user of Access, so please excuse me if this is the wrong forum. Here
is what I want:

For purchases we typically need 2 signatures on the Purchase order. Each
person has a "purchasing level". I need to keep track how much was purchased
on a monthly basis at each level.
I have 2 tables: "Purchases", which has a descrition, date, etc, plus two
signature fields. Another table, "Authorities" has the employees and their
purchasing level. The signature fields in table 1 are populated with the
employeeID from table 2. Now, in order to do what I want to do, I need to get
the purchasing level for the two signatures in table one and find the higher
level of the two. If I had only one signature, the solution would be easy:
Set up a relationship and design a query that shows the purchasing level for
the signature field. However, I have two signature fields, which can have
different purchasing levels. How do I deal with that?

Thanks.

mike
 
J

Jeff Boyce

I'm not completely clear (in my mind) on your table structure, so the
following may not fit...

If you create a query from your PurchaseOrder table to the Person table for
the first signature (to get the level), then add another copy of the Person
table, joined to the second signature (to get the second level), you will
have both levels in your query. You can add another field to your query
that determines the "greater" level.

What will you do with them once you have them?
 
M

Marshall Barton

mikebo said:
I am working on a little project in Access and I am stumped. I am not a
frequent user of Access, so please excuse me if this is the wrong forum. Here
is what I want:

For purchases we typically need 2 signatures on the Purchase order. Each
person has a "purchasing level". I need to keep track how much was purchased
on a monthly basis at each level.
I have 2 tables: "Purchases", which has a descrition, date, etc, plus two
signature fields. Another table, "Authorities" has the employees and their
purchasing level. The signature fields in table 1 are populated with the
employeeID from table 2. Now, in order to do what I want to do, I need to get
the purchasing level for the two signatures in table one and find the higher
level of the two. If I had only one signature, the solution would be easy:
Set up a relationship and design a query that shows the purchasing level for
the signature field. However, I have two signature fields, which can have
different purchasing levels.
Click to expand...


I think all you need to do is add the Employee table to the
to the query a second time. The connecting line from the
first sig goes to one copy and the other line goes from the
second sig to the other copy.
 
Top