Summary:
1. I want to define a column in anMS Access table to be lookups on other
tables.
2. The table that is the data source (e.g the "parent" table) has a
composite
primary key.
3. When the "child" table does the lookup, it should pass all the columns
necessary
to properly restrict the data returned to the litbso for he lookup.
4. How do I accomplish this in a lookup?
Please, no suggestions to avoid lookups, etc. This is for a very quick and
dirty
"spreadshee t replacement" for a one time data mapping exercise I do
not have the time to write an app for this. I need to avoid code and just
use what I have
defined in the table structures and foreign keys.
The following example is fabricated, but it shows the idea. I know that the
keys in the example aren't ideal,
but it makes it easy to read the example.
ParentTable (Key is composite of (State, CityName):
[State], [CityName], [Population]
AZ Phoenix 1,000,000
GA Phoenix 8,000
AZ Tucson 500,000
ChildTable (Key is SalesmanID. State and CityName are part of a composite
foreign key that referes to the ParentTable.
[SalesmanID], [State], [CityName], [DateAssigned]
1 AZ Phoenix 1/15/2006
1 AZ Tucson 3/01/2006
3 GA Phoenix 1/15/2006
I want ChildTable.City Name to be a lookup possible values from ParentTable,
but I want the values shown in the lookup
to be restricted based on the State entered in the column ChildTable.Stat e.
For example,
if you were in either of the first two rows of ChildTable, and hit the drop
down, you should see
'Phoenix' and 'Tucson'. If you were in the third row, you should only see
'Phoenix', but it would
be the Phoenix with a population of just 8,000 people.
If I define the column ChildTable.City Name as a lookup, I always see the
values Phoenix, Phoenix, Tucson, regardless
of which row I am on in Childtable.
How do I define the lookup so that the value from ChildTable is part of the
WHERE clause in the query that gets data
from ParentTable to populate the listbox? In other words, I want the query
to say WHERE State = 'AZ' when someone
invokes the lookup from one of the first two rows in ChildTable.
Thanks,
Mac 6 5412
Access != Excel.
use joins in a query.
I should not have even mentioned the "spreadshee t replacement". I am
well aware of the differences between databases and spreadsheets, and
I'm very comfortable with the concept of relational joins. I was just
trying to say that I want to define the lookup in the Access table
definition based on a composite key. Is this possible?
Thanks,
Mac pi********@hotm ail.com wrote:
Access != Excel.
use joins in a query.
Regardless of how "rushed" this may be, you should never be working directly
with tables. Creating a simple form, and then have a proper combobox do the
lookup for you doesn't take that much time.
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(no e-mails, please!)
"Mac" <bm******@att.n etwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .
>I should not have even mentioned the "spreadshee t replacement". I am
well aware of the differences between databases and spreadsheets, and
I'm very comfortable with the concept of relational joins. I was just
trying to say that I want to define the lookup in the Access table
definition based on a composite key. Is this possible?
Thanks,
Mac pi********@hotm ail.com wrote:
>Access != Excel.
use joins in a query.
Hi Doug,
Thanks for the info. However, my question is still unanswered. Can I
define a lookup in a table to work on a composite key?
Thanks,
Mac
Douglas J. Steele wrote:
Regardless of how "rushed" this may be, you should never be working directly
with tables. Creating a simple form, and then have a proper combobox do the
lookup for you doesn't take that much time.
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(no e-mails, please!)
"Mac" <bm******@att.n etwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .
I should not have even mentioned the "spreadshee t replacement". I am
well aware of the differences between databases and spreadsheets, and
I'm very comfortable with the concept of relational joins. I was just
trying to say that I want to define the lookup in the Access table
definition based on a composite key. Is this possible?
Thanks,
Mac pi********@hotm ail.com wrote:
Access != Excel.
use joins in a query.
"Mac" <bm******@att.n etwrote in message
<11************ **********@b28g 2000cwb.googleg roups.com>:
Hi Doug,
Thanks for the info. However, my question is still unanswered. Can I
define a lookup in a table to work on a composite key?
Thanks,
Mac
I think you can assume that the answer to that question, is the same
as the answer to the question - can the controlsource of a combo be
more than one field.
--
Roy-Vidar
Not as far as I know.
However, I will admit that my knowledge in this area is limited because a)
lookup fields are an abomination that should never have been introduced into
Access (see http://www.mvps.org/access/lookupfields.htm at "The Access Web"
for some of the reasons why) and b) you should never be working directly
with tables.
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(no e-mails, please!)
"Mac" <bm******@att.n etwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .
Hi Doug,
Thanks for the info. However, my question is still unanswered. Can I
define a lookup in a table to work on a composite key?
Thanks,
Mac
Douglas J. Steele wrote:
>Regardless of how "rushed" this may be, you should never be working directly with tables. Creating a simple form, and then have a proper combobox do the lookup for you doesn't take that much time.
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
"Mac" <bm******@att.n etwrote in message news:11******* *************** @i42g2000cwa.go oglegroups.com. ..
>I should not have even mentioned the "spreadshee t replacement". I am
well aware of the differences between databases and spreadsheets, and
I'm very comfortable with the concept of relational joins. I was just
trying to say that I want to define the lookup in the Access table
definition based on a composite key. Is this possible?
Thanks,
Mac pi********@hotm ail.com wrote: Access != Excel.
use joins in a query. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Girish Agarwal |
last post by:
--0-474210375-1058976151=:31789
Content-Type: text/plain; charset=us-ascii
Content-Id:
Content-Disposition: inline
Note: forwarded message attached.
__________________________________
|
by: John |
last post by:
Specifically for joint tables...
tblStudents
tblClasses
tblClasses_Students
Is it be good programming to use a composite primary key in
tblClasses_Students (where the key is ClassID and StudentID)? I
enventually will convert the Access db to an SQL db. I've always used
single primary keys based on long integers, usually autonumbered.
|
by: Thomas A. Anderson |
last post by:
I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?
Thank you in advance!
Still a newbie,
|
by: Ismail Rajput |
last post by:
Is there any option we can use Composite DataKeyField in the DataList and
DataGrid?
|
by: bobby_b_ |
last post by:
I have a table where fields 1 and 2 make up the primary key.
Because of this, I have a unique composite index on fields 1 and 2 (as
required by DB2). Now my question is: Fields 1 and 2 are also
separate foreign keys to two other tables. I have read that it is
always a good idea to create indexes on foreign keys. Should I create
single indexes on each of these fields? Or is that not necessary since
they are already part of a composite...
| |
by: Ronald S. Cook |
last post by:
My client manager likes concatenated/composite primary keys. I don't.
Can anyone forward any arguments pro or con?
Thanks,
Ron
|
by: Wolfgang Keller |
last post by:
Hello,
so far it seems to me as if the only ORM module for Python which
supports composite primary/foreign keys was SQLAlchemy. Which looks a
little bit "overbloated" for my needs: I "just" need to be able to
define a "logical model" (à la UML) in Python and have the ORM connect
to a database (running on PostgreSQL in my case) which uses a
corresponding (pre-defined) "physical model" as its schema.
Modeling really does look exactly...
|
by: lfhenry |
last post by:
Hi All,
I am about to make some changes to a process we have. The new process
will have a new table who's job it is to store temporarily data such
as customer, clerkno,productno, storeno , amount, time.
On a new request from a front-end i will select against the primary
keys to ensure no other request for the same data has been made. This
is then sent to a backend system asynchronously if there is no match.
I will otherwise send a...
|
by: csolomon |
last post by:
Hello:
I was wondering if I could get some input on how to address a design
issue, involving my composite table.
I have one portion of my project complete. The following forms and
reports I will add, piggyback off of my existing design.
The part I have already completed allows my users to create a design
sample; this is made up of several materials to create one sample. I
have accomplished this using this design:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |