Autolookup - filtering (1 Viewer)

[DavidHoward]

Hi,

An easy one I'm sure

Two tables - "Advertisments" and "ApplicationSource".

"Advertisements" holds the details of adverts placed against job references. ApplicationSource holds respondant info.

When completing the relevant field in "ApplicationSource", I wish only those records that match a criteria (ie JobRef) to be displayed in the drop-down box.

Advertisements:
Job Ref
Publication
Cost

ApplicationSource:
Job Ref
Source

(Set up like this to avoid many-many relationship)

Any help would be gratefully appreciated.

David Howard

 

[BLeslie88]

Hmmmm.....

Well... At least one of the tables, you would have to set the field so that their was no duplicates allowed. This would make a one to many relationship if you tried to make a relationship.
Set is up in both tables so that "no duplicates allowed" ..... Then you will have one-one.

Hope this helps....

Regards,

 

[Pat Hartman]

(Set up like this to avoid many-many relationship)

If two entities have a many-to-many relationship, why would you not want your table structure to reflect that? You will not be able to model real world situations.

I'm not sure that I understand what your question is. Are you asking about how to link two combos? To help with this explaination, please tell us what the primary keys of the two tables are.

 

[DavidHoward]

Hi,

Sorry to have caused confusion (it's just one of my many skills).

To clarify, I want to avoid many-many 'cos I'm just old fashioned...

I want the listbox on "Applicationsource" to show only those values within "Advertisement" where the two refs are equal.

example:

Advertisment

Ref : S1
Pub: The Times
Cost: £10.50
-----------------
Ref: S1
Pub: The Journal
Cost: £27.50
-----------------
Ref: S2
Pub: The Evening Chronicle
Cost: £150
-----------------


Now when an "Applicationsource" record is input for the ref of "S1", the drop down box for "media" should only pick up "The Times" and "The Journal".

"Ref" is the driving force for the data, one ref = one position.
Many adverts to one ref. Many ApplicationSource to one ref.

When I thought of doing it like this, I'm sure there was a valid reason, but for the life of me I can't think what that was, now

Hope this makes things a bit clearer.

Thanks for showing an interest - any hints greatfully received.

David Howard

 

[Pat Hartman]

Look at the last two posts in the following thread to see how to synchronize two combos.

http://www.access-programmers.co.uk...ighlight=combo1

To clarify, I want to avoid many-many 'cos I'm just old fashioned...

You really need some help understanding relational databases. Do searchs of the Microsoft knowledge base for database design and normalization. The articles will give you a better understanding. There are others available at www.fmsinc.com also.