field that checks if the primary key value appears in a related ta

[KarenF]

Hi,

I have a form, and on that form I would like a control that tells me if the
record I'm looking at has a related record in a related table. I would like
another control that can tell me how many related records there are in a
related table.

I've been thinking about using DLookup and DCount for this, based on advice
from another question I posted. The fields I want to look at are ID fields,
and I need to check them from one main table (on which my form is based),
against two related tables. The tables are related to the main table on a
1:1 basis.

If Dlookup and Dcount are my options, then if anyone could advise me further
about these, then I'd be grateful.

Many thanks again.

Karen

 

[Jeff Boyce]

Karen

A point of clarification ...

If your tables are related "1:1", you couldn't have "many related records...
in a related table" for any particular row in your 'main' table.

First, having a 1:1 relationship is a bit uncommon (not uncalled for,
sometimes necessary, but uncommon, all the same). If you'll provide a
description (or example) of the kinds of data your main and related tables
hold, the folks here in the newsgroup may be able to offer more specifc
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KarenF]

Thanks Jeff.

Yes, I understand that with a 1:1 there will only be at the most 1 related
record in the related table. However, having re-read my question, I can see
that I've missed a bit out. I was interrupted while writing it, sorry.

The database belongs to a colleague who has asked me for help with it.

There is one main table with a list of personal details for different
associates of an organisation. Some of these associates appear on a mailing
list (and their id from the personal details table links to their id in the
mailing list table along with details such as their date of application,
interests etc). Some of the associates have membership of the organisation,
and this membership can be of different types, and often joint membership.
The personal details id from the personal details table is linked to the
personal id in the membership table, along with application dates, accepted
dates, expiry dates, membership type, if a joint member, the details of the
joint membership etc. The members may not be on the mailing list, and the
mailing list contains individuals who are not members, but everyone appears
in the personal details table. In each table the id is unique, hence a 1:1
relationship. It is for this detail that we wish the form to tell us if an
individual's id appears in the membership or mailing list tables. This is
where I was thinking of possibly using a dlookup formula.

Now this is the bit where I was interrupted. We also have a box office
table. Members, mailing list, and individuals who appear in neither table,
but who do appear on the personal details table can appear in the box office
table - many times (hence the 1:many relationship between the personal
details and the box office tables). We have a box office subform on the
personal details form, and having just thought of this, we could put a
calculated control in the subform's footer to count up the instances of
bookings under that id, and even to sum up how many of each type of ticket
has been purchased so far.

However, my colleague would like to be able to count how many box office
instances of an individual's id there are and place the result on a form
(which does not display the box office subform). This is the same form on
which the other controls I have mentioned are required.

Thanks Jeff for highlighting the need for me to clarify. I'm sorry I was
only giving half of the story.

I hope that extra info helps.

Take care,

Karen

 

[Jeff Boyce]

Karen

Sorry for the delay, I've been away...

See comments in-line below:

Thanks Jeff.

Yes, I understand that with a 1:1 there will only be at the most 1 related
record in the related table. However, having re-read my question, I can
see
that I've missed a bit out. I was interrupted while writing it, sorry.

The database belongs to a colleague who has asked me for help with it.

There is one main table with a list of personal details for different
associates of an organisation. Some of these associates appear on a
mailing
list (and their id from the personal details table links to their id in
the
mailing list table along with details such as their date of application,
interests etc).

So you are saying you have tables like:
tblPerson
PersonID
LastName
FirstName
DOB
Address
...

tblMailingList
MailingListID
PersonID
DateOfApplication
Interest1
Interest2
Interest3
...

(if you do have a table like this latter one, consider spending time
normalizing your data structure. "Repeating fields" like this make it
harder to use Access' relationally-oriented features and functions.)

Some of the associates have membership of the organisation,
and this membership can be of different types, and often joint membership.
The personal details id from the personal details table is linked to the
personal id in the membership table, along with application dates,
accepted
dates, expiry dates, membership type, if a joint member, the details of
the
joint membership etc.

So you also have a:
tblMembership
MembershipID
PersonID
ApplicationDate
AcceptanceDate
ExpiryDate1
ExpiryDate2
...
MembershipType
MembershipDetails (?text info or ?)

Again, as I've interpreted your description, you have something more like a
spreadsheet and less like a normalized relational table. Repeating "date"
fields could be "pulled out" into another table.

The members may not be on the mailing list, and the
mailing list contains individuals who are not members, but everyone
appears
in the personal details table. In each table the id is unique, hence a
1:1
relationship. It is for this detail that we wish the form to tell us if
an
individual's id appears in the membership or mailing list tables.

?Or in both?

This is
where I was thinking of possibly using a dlookup formula.

Another mechanism for showing membership/mailing list would be to use two
subforms, one for each table. These subforms would display the person's
info, if any, from the membership and mailing list tables.

Now this is the bit where I was interrupted. We also have a box office
table. Members, mailing list, and individuals who appear in neither
table,
but who do appear on the personal details table can appear in the box
office
table - many times (hence the 1:many relationship between the personal
details and the box office tables).

So folks in the tblPerson can have their ID show up in the tblBoxOffice,
something like:

trelBoxOffice
BoxOfficeID
PersonID
TicketType
PurchaseDate
Amount
... (other ticket-purchase-specific info)

We have a box office subform on the
personal details form, and having just thought of this, we could put a
calculated control in the subform's footer to count up the instances of
bookings under that id, and even to sum up how many of each type of ticket
has been purchased so far.

However, my colleague would like to be able to count how many box office
instances of an individual's id there are and place the result on a form
(which does not display the box office subform).

If your colleague wishes to see the total number of tickets purchased by the
individual, why not show that number on the main form displaying the
Person-related info? You wouldn't have to place it in the subform. What is
the business need for NOT showing the subform? If it is already showing,
would you have to take it away?!

This is the same form on
which the other controls I have mentioned are required.

Thanks Jeff for highlighting the need for me to clarify. I'm sorry I was
only giving half of the story.

I hope that extra info helps.

Take care,

Karen

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP