How do I create table data based on a query?

  • Thread starter Thread starter Jamie Risk
  • Start date Start date
J

Jamie Risk

Hello,

I've got 3 tables with a desire to create a 4th.

For the sake of design question, table 1 is called "Males", Table 2 is
called "Females" and Table 3 is called "Location".

Tables 1 & 2 refer to Table 3 in a lookup - this is so that I may produce
query showing all the "Males" and "Females" with matching locations. There
is only one "Female" per "Location". And, as it turns out, only one "Male"
per "location", although their could be several "Males" per "Location" in
future.

So far so good.

My question then is this:
******************
I'd like to use the information in the query mentioned to schedule a meeting
time, recorded in a table or something just as useful - I don't know how.
I'd strongly prefer to reference (via a lookup) the tables "Male" and
"Female".

The fourth table would be tied to the "Male" and "Female" tables by field
lookups in "Location". For each "Location", there would be always be one
"Female" for any matching "Male" (one or more) with a specified meeting
time.

The end result is that for every Male, a meeting would be scheduled with a
Female, where one or more Males would attend that meeting provided they have
the same location.

Does anyone have any suggestions?

I've tried using Forms but I can't figure out how to do anything with a form
other than look at existing data (I can't add anything new). I've also tried
using tables to no affect, and the "Autolookup" feature looked interesting
but I couldn't figure out where that was supposed to work either (in a form,
or in a table?).

Help!!!!

- Jamie
 
Hi Jamie,
For the sake of design question, table 1 is called "Males", Table 2
is called "Females" and Table 3 is called "Location".
Click to expand...

Not a good design. You should have similar data (people) in one table. You can add a field that
indicates sex.

There is only one "Female" per "Location". And, as it turns out, only
one "Male" per "location", although their could be several "Males" per
"Location" in future.
Click to expand...
So far so good.
Click to expand...

What's good about *that*, from a male's perspective! <smile> ??? Sheeze. I'm glad I'm not one
of the many at one of your locations!!! Note: I'm happily married, so it wouldn't matter
anyways!

I'd strongly prefer to reference (via a lookup) the tables "Male"
and "Female".
Click to expand...

If you are talking about lookups defined at the table level, please be aware that "Lookups are
the creation of the Evil One". See the 2nd commandment at:
http://www.mvps.org/access/tencommandments.htm

For each "Location", there would be always be one "Female" for
any matching "Male" (one or more) with a specified meeting time.
Click to expand...

I'd prefer the idea of at least (5) females for every male instead!


Tom
_________________________________


Hello,

I've got 3 tables with a desire to create a 4th.

For the sake of design question, table 1 is called "Males", Table 2 is
called "Females" and Table 3 is called "Location".

Tables 1 & 2 refer to Table 3 in a lookup - this is so that I may produce
query showing all the "Males" and "Females" with matching locations. There
is only one "Female" per "Location". And, as it turns out, only one "Male"
per "location", although their could be several "Males" per "Location" in
future.

So far so good.

My question then is this:
******************
I'd like to use the information in the query mentioned to schedule a meeting
time, recorded in a table or something just as useful - I don't know how.
I'd strongly prefer to reference (via a lookup) the tables "Male" and
"Female".

The fourth table would be tied to the "Male" and "Female" tables by field
lookups in "Location". For each "Location", there would be always be one
"Female" for any matching "Male" (one or more) with a specified meeting
time.

The end result is that for every Male, a meeting would be scheduled with a
Female, where one or more Males would attend that meeting provided they have
the same location.

Does anyone have any suggestions?

I've tried using Forms but I can't figure out how to do anything with a form
other than look at existing data (I can't add anything new). I've also tried
using tables to no affect, and the "Autolookup" feature looked interesting
but I couldn't figure out where that was supposed to work either (in a form,
or in a table?).

Help!!!!

- Jamie
 
Jamie said:
The end result is that for every Male, a meeting would be scheduled with a
Female, where one or more Males would attend that meeting provided they have
the same location.
Click to expand...

I think you are going to just love this article:

http://www.intelligententerprise.com/010101/celko.jhtml

Quote: "'Playboys' is a proper table, without duplicated pairs, but it
also enforces the condition that I get to play around with one or more
ladies: CREATE TABLE Playboys ..."

Then again, it may not be to your tastes <g>.

Jamie.

--
 
The tables I gave were an example. They could just as easily be "Apples"
"Oranges" and "Grocery Store", or "Democrats", "Republicans" and "Senate
District" or ... use your imagination.

I was trying to keep the example simple to illustrate what I'd like to
accomplish. Sexism aside ... help would be appreciated.

-Jamie
 
Thanks ... Tom. Please ignore the names of the tables and understand they
were used only to demonstrate the requirement. The fact is the tables are
not of my creation and come from two seperate sources, which happn to have
one field useful field in common.
 
Jamie said:
The tables I gave were an example. They could just as easily be "Apples"
"Oranges" and "Grocery Store", or "Democrats", "Republicans" and "Senate
District" or ... use your imagination.

I was trying to keep the example simple to illustrate what I'd like to
accomplish. Sexism aside ...
Click to expand...

Did you also manage to look past the humour when you read the article?
There are some design ideas in there which should be thought provoking
for you. I thought the example of a teacher's schedule was particularly
pertinent.

Notice how the author uses CREATE TABLE DDL and INSERT INTO DML to make
it easy for the reader to follow along at home? If you did the same,
using your real schema and some representative data, people would find
it easier to help you.

Personally, aside you urging you to critique your existing schema, I
cannot help further until you make you aims clearer. I not even sure
what your desired 'end result' actually is: my first thought was you
are designing a relationship (junction, lookup, etc) table structure
with appropriate constraints, but it could be that you want to create a
VIEW i.e. a results set based on data in existing tables with perhaps a
new Meetings table. Again, you could clarify this by posting some
sample data you expect to see from the finished result.

Jamie.

--
 
Hello,

I've got 3 tables with a desire to create a 4th.
Click to expand...

Storing data redundantly is essentially NEVER either a good idea or
necessary.
For the sake of design question, table 1 is called "Males", Table 2 is
called "Females" and Table 3 is called "Location".
Click to expand...

Having read the rest of the thread said:
Tables 1 & 2 refer to Table 3 in a lookup - this is so that I may produce
query showing all the "Males" and "Females" with matching locations. There
is only one "Female" per "Location". And, as it turns out, only one "Male"
per "location", although their could be several "Males" per "Location" in
future.
Click to expand...

Again... Lookup fields accomplish only one thing: they conceal the
actual contents of your table and make it harder to understand and
harder to use correctly.

It is NOT necessary, or even a good way to do it, to use a Lookup
field to create a query. *Just create a Query*. If you want to find
which males and females are at the same location, create a new Query;
add the Males table; add the Females table; join the two tables by
Location. Select whichever fields you want to see from the two tables.
No lookup is involved but you get exactly what you want.
So far so good.

My question then is this:
******************
I'd like to use the information in the query mentioned to schedule a meeting
time, recorded in a table or something just as useful - I don't know how.
I'd strongly prefer to reference (via a lookup) the tables "Male" and
"Female".
Click to expand...

If you need a fourth table, related one-to-many to each of these three
tables, which will (eventually) have multiple meeting times, then you
can change the above query into a MakeTable query. It should contain
the primary key of the Males table, the primary key of the Females
table, the Location, and a date/time field for the meeting time.

You may be better off creating this Meetings table separately and
using an Append query instead of a MakeTable query. By doing so you
can use the Relationships window to link the fields in this table to
Males, Females, and Locations; this will help when you later create
queries ("Lookups") or forms/reports based on all four tables.
The fourth table would be tied to the "Male" and "Female" tables by field
lookups in "Location". For each "Location", there would be always be one
"Female" for any matching "Male" (one or more) with a specified meeting
time.
Click to expand...

You cannot create a Lookup field in a maketable query but, as noted,
you probably shouldn't be doing so anyway.
The end result is that for every Male, a meeting would be scheduled with a
Female, where one or more Males would attend that meeting provided they have
the same location.

Does anyone have any suggestions?

I've tried using Forms but I can't figure out how to do anything with a form
other than look at existing data (I can't add anything new). I've also tried
using tables to no affect, and the "Autolookup" feature looked interesting
but I couldn't figure out where that was supposed to work either (in a form,
or in a table?).
Click to expand...

Go back to Forms. Try again! I *NEVER* use table datasheets for data
entry, only Forms! Of *course* you can add new data in forms; that's
exactly what they are designed to do. You may be making the very
common mistake of creating a big multitable complex query "to do
everything" and then basing a Form on this query. Such big complex
queries will probably not be updateable. Instead, use a Form with
controls such as combo boxes and subforms to pull together data from
different tables.


John W. Vinson[MVP]
 
Thanks for your constructive reply. It illuminates that I'm in over my head
and lack the vernacular to visualize or explain my problem.

I tried my best to make my problem accesible by using the trite example of a
dating database. The link provided by "onedaywhen"
<[email protected]> made me think of this entirely appropriate
example.

Table 1 "Districts" - List of Constituency Districts
Table 2 "Senators" - US Senators, each entry has a reference to Table 1's
Primary ID
Table 3 "Members" - Membership List for some Interest Group, again each
entry has a field referencing Table 1's primary ID

Query 1 - Match up of Senators with their constituents (by matching
Constituency Districts)

Some basic assumptions:
There is only one Senator per district.
Senators can change.
Districts do not change.
There could be 0, 1, or many Members enteries for every District entry

I'd like to schedule a meeting or all those mentioned in Query 1.

I know little about SQL, less about Access. My fault of using lookups in my
tables is symptomatic of my desire to use whats 'easy' (wizards).

- Jamie
 
onedaywhen said:
Did you also manage to look past the humour when you read the article?
Click to expand...


Apparently, no.

I'm not sure I know how to peel off the veneer of Access's GUI to get to an
SQL interface. Would that I could do that, it would be of little use as I'm
not comfortable with SQL.

I can fathom that the article in some context of SQL literacy is entirely
appropriate, sadly, out of my reach.
 
Jamie said:
I'm in over my head
and lack the vernacular to visualize or explain my problem.
Click to expand...

For database schema, the vernacular is DDL: CREATE TABLE, CREATE VIEW,
etc. With due respect, perhaps you should learn some SQL. There are
some free, elementary, interactive online courses:
http://www.sqlcourse.com/ should get you up and running within half an
hour or at the very least be a confidence boost. You could then
progress to http://sqlzoo.net/, the self-styled 'gentle introduction to
SQL: interactive SQL tutorial using [among others] Access'.

Pretty much anything you need to do with a database requires SQL, even
when it's performed under the hood by a wizard or GUI tool. And anyone
who's ever recorded a macro in Word or Excel knows a wizard doesn't
write code efficiently.
From a newsgroup perspective, it's mush easier and exacting to describe
Click to expand...
a table using CREATE TABLE .. rather than with, 'I have this table with
columns that ... ' or even, 'Go to this screen, drop that menu, type
this into the textbox with that caption, uncheck this ...' Bottom line:
I give my answers in SQL DDL and DML and it's your loss if you can't
understand them. I don't mean anything 'aggressive' by that; I simply
mean, you seem to know you are missing out, so why not do something
about it?
this entirely appropriate
example.
Click to expand...

Hmm, a bit US-centric for me. I don't have such a great understanding
of the archaic political system of my own country, let alone anyone
else's.
Query 1 - Match up of Senators with their constituents (by matching
Constituency Districts)

Some basic assumptions:
There is only one Senator per district.
Senators can change.
Districts do not change.
There could be 0, 1, or many Members enteries for every District
Click to expand...
entry

Try Googling the exact phrase "Current US Congressional Districts" (my
emphasis on the word 'current'). One phrase that gets picked up is,
'The districts are redrawn every 10 years, following the completion of
the national census.' Looking elsewhere suggests data is generally
keyed on a district code and zip code - it's always best to use the
industry standard codes where available - and ready-rolled databases
are commercially available, including regular updates. For practical
application, it may be more useful to match Senators with their
constituents by matching zip codes rather than Constituency Districts.
Whatever your field of interest *really* is, you need to do some
research to ensure you do not incorporate flawed assumptions into your
data model.
I'd like to schedule a meeting or all those mentioned in Query 1.
Click to expand...

Too vague. What are the restrictions e.g. venue, capacity,
availability, ...?

Jamie.

--
 
Thanks for your constructive reply. It illuminates that I'm in over my head
and lack the vernacular to visualize or explain my problem.
Click to expand...

I'm leaving town for a couple of days and won't have a chance to
continue this discussion. I'll ask some other MVP's to chime in if
they can. Onedaywhen is providing good advice, but from a SQL/Server
perspective and on a pretty advanced level - it'll be worth some
effort to study his posts, but if someone else can chime in to
interpret it might help!


John W. Vinson[MVP]
 
John indicated that you might desire some additional assistance.

For the example that you posted, a query that will match constituents to a
specific Senator would be something like this:

SELECT Members.*, Senators.SenatorName, Districts.DistrictName
FROM (Members INNER JOIN Districts
ON Members.DistrictID = Districts.DistrictID)
INNER JOIN Senators
ON Districts.DistrictID = Senators.DistrictID;

--

Ken Snell
<MS ACCESS MVP>
 
Thanks Ken, I managed to attach meaning to that SQL, though syntatically it
would have been a struggle to conjure that on my own.

Having tried it, is their a way to extract a list of District.DistricID from
the query? I'm looking to for a list of Districts where their is match
between Senators and Members.
 
Not sure if this is what you're seeking, but you could include the
Districts.DistrictID value in the query's output:

SELECT Members.MemberName, Senators.SenatorName, Districts.DistrictName,
Districts.DistrictID
FROM (Members INNER JOIN Districts
ON Members.DistrictID = Districts.DistrictID)
INNER JOIN Senators
ON Districts.DistrictID = Senators.DistrictID;


Or if you just want to identify the districts where there is at least one
member and at least one senator, this is one way to get that info

SELECT Districts.DistrictID, Districts.DistrictName
FROM Districts
WHERE DCount("*", "Members", "DistrictID=" &
Districts.DistrictID) > 0 AND
DCount("*", "Senators", "DistrictID=" &
Districts.DistrictID) > 0
GROUP BY Districts.DistrictID, Districts.DistrictName;


There are other ways to get at the info as well, but this may get you
started on the right path.
--

Ken Snell
<MS ACCESS MVP>
 
The criteria you suggested for the optional third parmaeter for the DCount
function doesn't quite work for me.

When I punch in the SQL with the WHERE clause as suggested, and try to look
at the results, a dialogue box pops asking me to define a value for
Districts.DistrictID. (Message is "Enter Parameter Value").

I checked that I'm referencing the table and fields correctly, then I tried
changing the DCount functions to:

DCount("*", "Senators", "DistrictID = " & "Districts.DistrictID") >0

and I've even tried

DCount("*", "Senators", "DistrictID = " & "[Districts].DistrictID") >0

in both cases I get the message "The expression you entered as a query
parmaeter produced this error: 'Microsoft Access can't find the name
'Districts.DistrictID' you entered in the expression'"

In addition to clarifying the syntax I should be using, I'd like to
understand the construction of the criteria argument for the DCount
functions.
DCount("*","Members","DistrictID=" & Districts.DistrictID)

If I can interpret the function this way "Count the number of records in the
[Members] table that have a matching DistrictID field in the the [Districts]
table. If that can be construed as correct then I can't see the query
returning anything other than everything in [Districts] table. My reasoning
is this; for every field in [Members].DistrictID, compare it to every field
in [Districts].DistrictID, because [Members].DistrictID gets its data from
one of the records in [Districts], the DCount function will always return
true.

If I haven't screwed things to this point, it seems (excuse my C thinking
here) that we would need to involve some sort of loop so that the boolean
AND nested between the DCount(..) functions will be referencing the same
data for each function.

- Jamie
 
You must replace Districts.DistrictID in the DCount's third argument with
the correct table and field names. The fact that you're being asked for that
parameter suggests that you didn't change the syntax in the third argument
of the DCount function (the part outside the " characters) to match the
actual names that you're using.

DCount("*", "InsertCorrectMembersTableNameHere",
"InsertCorrectMembersDistrictIDFieldNameHere" &
ActualNameOfDistrictsTable.ActualNameofDistrictsDistrictIDFieldNameHere) > 0

The DCount function that I posted is doing this: for each record in the
Districts table, it is looking to see if there are any records in the
Members table that have the DistrictID value for the Districts record. Thus,
it is searching the Members table one DistrictID value at a time. So if
there are no Members who have DistrictID value of (for example) 5, DCount
will return a value of 0, even though Members table has records where the
DistrictID value is 6.

I am assuming in my example that DistrictID is a numeric value and not a
text value. If it's a text value then the syntax for the DCount function
includes a ' delimiter for the third argument concatentation:

DCount("*", "Senators", "DistrictID = '" & [Districts].DistrictID & "'") >0

It may be helpful if you can post the exact names of the tables and fields
that you're using.

--

Ken Snell
<MS ACCESS MVP>



Jamie Risk said:
The criteria you suggested for the optional third parmaeter for the DCount
function doesn't quite work for me.

When I punch in the SQL with the WHERE clause as suggested, and try to
look at the results, a dialogue box pops asking me to define a value for
Districts.DistrictID. (Message is "Enter Parameter Value").

I checked that I'm referencing the table and fields correctly, then I
tried changing the DCount functions to:

DCount("*", "Senators", "DistrictID = " & "Districts.DistrictID") >0

and I've even tried

DCount("*", "Senators", "DistrictID = " & "[Districts].DistrictID") >0

in both cases I get the message "The expression you entered as a query
parmaeter produced this error: 'Microsoft Access can't find the name
'Districts.DistrictID' you entered in the expression'"

In addition to clarifying the syntax I should be using, I'd like to
understand the construction of the criteria argument for the DCount
functions.
DCount("*","Members","DistrictID=" & Districts.DistrictID)

If I can interpret the function this way "Count the number of records in
the [Members] table that have a matching DistrictID field in the the
[Districts] table. If that can be construed as correct then I can't see
the query returning anything other than everything in [Districts] table.
My reasoning is this; for every field in [Members].DistrictID, compare it
to every field in [Districts].DistrictID, because [Members].DistrictID
gets its data from one of the records in [Districts], the DCount function
will always return true.

If I haven't screwed things to this point, it seems (excuse my C thinking
here) that we would need to involve some sort of loop so that the boolean
AND nested between the DCount(..) functions will be referencing the same
data for each function.

- Jamie


SELECT Districts.DistrictID, Districts.DistrictName
FROM Districts
WHERE DCount("*", "Members", "DistrictID=" &
Districts.DistrictID) > 0 AND
DCount("*", "Senators", "DistrictID=" &
Districts.DistrictID) > 0
GROUP BY Districts.DistrictID, Districts.DistrictName;


There are other ways to get at the info as well, but this may get you
started on the right path.
Click to expand...
Click to expand...
 
Ken said:
You must replace Districts.DistrictID in the DCount's third argument with
the correct table and field names.
Click to expand...

Of course, the other thing about not posting DDL or sample data means
the reader has to replicate all the work. Are we looking at something
like this?

CREATE TABLE Districts (
DistrictID INT NOT NULL PRIMARY KEY,
DistrictName VARCHAR(100) NOT NULL
)
;
CREATE TABLE Senators (
SenatorID INTEGER NOT NULL PRIMARY KEY,
SenatorName VARCHAR(100) NOT NULL,
DistrictID INTEGER NOT NULL UNIQUE,
CONSTRAINT fk__senators__districts FOREIGN KEY (DistrictID)
REFERENCES Districts (DistrictID)
ON UPDATE CASCADE ON DELETE CASCADE
)
;
CREATE TABLE Members (
MemberID INTEGER NOT NULL PRIMARY KEY,
MemberName VARCHAR(100) NOT NULL,
DistrictID INTEGER NOT NULL,
CONSTRAINT fk__members__districts FOREIGN KEY (DistrictID)
REFERENCES Districts (DistrictID)
ON UPDATE CASCADE ON DELETE CASCADE
)
;

If so, then doesn't the original query have all the required info? e.g.


SELECT DISTINCT Districts.DistrictID, Districts.DistrictName FROM
(Members INNER JOIN Districts ON Members.DistrictID =
Districts.DistrictID) INNER JOIN Senators ON Districts.DistrictID =
Senators.DistrictID;

Jamie.

--
 
onedaywhen said:
Of course, the other thing about not posting DDL or sample data means
the reader has to replicate all the work. Are we looking at something
like this?

CREATE TABLE Districts (
DistrictID INT NOT NULL PRIMARY KEY,
DistrictName VARCHAR(100) NOT NULL
)
;
CREATE TABLE Senators (
SenatorID INTEGER NOT NULL PRIMARY KEY,
SenatorName VARCHAR(100) NOT NULL,
DistrictID INTEGER NOT NULL UNIQUE,
Click to expand...

I'm not sure about the UNIQUE bit, but everything else seems to jive.

Amongst all the things I was looking for help with, the SELECT DISTINCT was
what I needed to accomplish what I was asking for.

Is their a way to have access spit out SQL code (other than in the query
dialogues). I'm sympathetic to your desire for specifics.

- Jamie
 
If so, then doesn't the original query have all the required info? e.g.


SELECT DISTINCT Districts.DistrictID, Districts.DistrictName FROM
(Members INNER JOIN Districts ON Members.DistrictID =
Districts.DistrictID) INNER JOIN Senators ON Districts.DistrictID =
Senators.DistrictID;

Jamie.

--
Click to expand...

Yes, the above query should provide a list of the districts that have at
least one senator and at least one member associated to them. As I noted,
there are different ways to get the result
:-)
 
Back
Top