Lookup table and Append query problem

[Amit]

Hi,

I have a table called "Project Partner" with the following
fields: ProjectID and PartnerID.

PartnerID can be either "StaffID" from table "Staff",
or "IndividualID" from table "Individual Partner".

I'm using 2 append queries to get the StaffID and
IndividualID and store it in a lookup table, which will be
the source for PartnerID in "Project Partner" table.

Is there a better/efficient/correct way to implement this?
I added some new staff members, and when I ran the append
query, it appended all the staff IDs. I'd like to append
only the NEW Staff IDs or Individual IDs to the existing
ones in the lookup table.

Thanks for suggestions/help.

-Amit

 

[Graham Mandeno]

Hi Amit

I'm a little confused. How do you know, for a given PartnerID, whether it
is a link to a Staff member or an Individual Partner? What happens if you
have both a Staff member and an Individual Partner with the same ID value?

Perhaps you should have another field (byte) in your ProjectPartner table
named, say, PartnerType, with a value of 1 for a Staff member and 2 for an
Individual Partner.

And I don't understand what you want to do with the lookup table. Is this
just so you can populate a combo box to select the name of a partner? If
so, you don't need a table - just a union query - something like this:

Select 1 as PartnerType, StaffID as PartnerID, StaffName as PartnerName,
"Staff" as PartnerDescr from Staff
union
Select 2 as PartnerType, IndividualID as PartnerID, IndividualName as
PartnerName, "Individual" as PartnerDescr from [IndividualPartner]

This will list all the rows of both tables, with PartnerType showing which
table the record came from.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Amit]

Hi Graham,

Thanks for your reply. I'm still learning Access, and your
response gave me good insights.

The way I'd though to implement this was to have an
autonumber ID in the lookup table, along with the
StaffID/IndPartnerID and Name. That way, even if a StaffID
is the same as IndPartnerID, the value being stored in the
table is the Autonumber ID. Another option I thought was
to prefix the IDs with some alphabets to distinguish which
table they're coming from.

But, your suggestion seems to be a better solution to
implement this. Thanks a lot!

-Amit

-----Original Message-----
Hi Amit

I'm a little confused. How do you know, for a given PartnerID, whether it
is a link to a Staff member or an Individual Partner? What happens if you
have both a Staff member and an Individual Partner with the same ID value?

Perhaps you should have another field (byte) in your ProjectPartner table
named, say, PartnerType, with a value of 1 for a Staff member and 2 for an
Individual Partner.

And I don't understand what you want to do with the lookup table. Is this
just so you can populate a combo box to select the name of a partner? If
so, you don't need a table - just a union query - something like this:

Select 1 as PartnerType, StaffID as PartnerID, StaffName as PartnerName,
"Staff" as PartnerDescr from Staff
union
Select 2 as PartnerType, IndividualID as PartnerID, IndividualName as
PartnerName, "Individual" as PartnerDescr from [IndividualPartner]

This will list all the rows of both tables, with PartnerType showing which
table the record came from.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Hi,

I have a table called "Project Partner" with the following
fields: ProjectID and PartnerID.

PartnerID can be either "StaffID" from table "Staff",
or "IndividualID" from table "Individual Partner".

I'm using 2 append queries to get the StaffID and
IndividualID and store it in a lookup table, which will be
the source for PartnerID in "Project Partner" table.

Is there a better/efficient/correct way to implement this?
I added some new staff members, and when I ran the append
query, it appended all the staff IDs. I'd like to append
only the NEW Staff IDs or Individual IDs to the existing
ones in the lookup table.

Thanks for suggestions/help.

-Amit

.

 

[Amit]

Tim,

Thanks for the response. I thought of the scenario you
suggest, but then, I will need two more tables because
both Staff and Individual have some fields which are
exclusive to them.

Wouldn't that require extra Form programming, where one
form will have a combo box to select "Staff"
or "Individual" (in addition to common fields), and then
display the appropriate subform for fields which are not
common? How difficult is this to program? Right now, I can
keep things simple and have two separate forms - one each
for Staff and Individual.

-Amit

 

[Tim Ferguson]

Wouldn't that require extra Form programming, where one
form will have a combo box to select "Staff"
or "Individual" (in addition to common fields), and then
display the appropriate subform for fields which are not
common? How difficult is this to program?

Actually, I usually use a couple of command buttons, but either way is not
very hard: just call a bit of code like

' see if there is an existing staff record
wRecordIsThere = DCount("*","Staff","PeopleID="&Me!txtPeopleID)

If wRecordIsThere = 0 Then
' make a new one and take the ID number from the current form
currentdb().Execute _
"INSERT INTO Staff (PeopleID) VALUES (" & Me!txtPeopleID & ")", _
dbFailOnError

End If

' now open the table with the correct record showing
' check the parameters and number of commas etc in help
' the idea is to use the Where parameter to limit the form to
' just the one record for the correct staff member
DoCmd.OpenForm "frmStaffDetails", acNormal,,"PeopleID=" & Me!txtPeopleID


BEWARE!! this is "air code" and quite untested, but it should be close
enough to get you started.

Right now, I can
keep things simple and have two separate forms - one each
for Staff and Individual.

Actually this works just as well: base each form on a query that joins
People with the appropriate *Details table. Access will create the Details
record as soon as you start typing into the relevant fields.


Hope that helps


Tim F