Although there is no one best way to organize your information, I have
some suggestions and an example that I hope may be useful.
Since you didn't post any example data, I made up some with which to
populate your Tables. Since your agenda items appeared in two places,
in [meeting schedule].[mtg minutes] and in [meeting items].[mtg item], I
concluded that the important one was the one in [meeting items], and
that each record there should have only one agenda item.
[meeting schedule] Table Datasheet View:
meeting mtg minutes mtg type mtg date
schedule_ID
----------- ----------- ---------- ---------
-2038941511 Rien Conspiracy 3/15/2006
-607699373 Nothing Conspiracy 3/28/2006
936153487 Nichts Election 3/24/2006
[meeting items] Table Datasheet View (folded to keep long lines from
wrapping -- there are at least 7 fields here, but no more than 3 on each
line) follows. I added a [meeting items].[Sequence] field to each
record to allow the person entering the data to specify an order to the
items. If you call the first one "10" and the next "20", etc., then you
can later squeeze another one between them by giving it a number of
"14". Or you could number them as "100", "200", &c.
Sequence mtg type meeting schedule_ID
-------- ---------- -------------------
20 Conspiracy -607699373
30 Conspiracy -2038941511
10 Conspiracy -2038941511
10 Election 936153487
mtg item
--------------------------------------
Contact coup organizers
Discuss post-mortem on Caesar
Stabbing planned for 8:30 this morning
Cast ballots
mtg item mtg item mtg item
current entered author
-------- -------- ---------
Yes Yes Rasputin
Yes No Gaius
No No Brutus
Yes No Solon
In your Query, you linked the Tables on the basis of the [mtg type]
field, but unless you had only one example of each [mtg type] in the
[meeting schedule] Table, you might get more records than you intended.
What this Query does is to form a Cartesian product of records in the
two Tables, at least of those whose [mtg type] field matches the value
in your Combo6 control. A Cartesian product gives you EVERY record of
the first Table combined with EVERY record of the second Table. (With 2
Tables of 10 records each, that would be 100 records in the result,
usually not what you really want.)
[Q_SoFar] SQL (pretty much as you had defined it):
SELECT [meeting items].[mtg item id],
[meeting items].[mtg type],
[meeting items].[mtg item],
[meeting items].[mtg item current],
[meeting items].[mtg item entered],
[meeting items].[mtg item author],
[meeting schedule].[mtg minutes]
FROM [meeting schedule]
INNER JOIN [meeting items]
ON [meeting schedule].[mtg type]
= [meeting items].[mtg type]
WHERE ((([meeting items].[mtg type])
=[Forms]![frm mtg schedule]![Combo6])
AND (([meeting items].[mtg item current])=True));
Notice that, in my example, among the "Conspiracy" records, there are 2
meetings, and 2 agenda items, giving a list of 4 records.
[Q_SoFar] Query Datasheet View:
mtg item id mtg type mtg item
----------- ---------- -----------------------------
-641431912 Conspiracy Contact coup organizers
-641431912 Conspiracy Contact coup organizers
-347132331 Conspiracy Discuss post-mortem on Caesar
-347132331 Conspiracy Discuss post-mortem on Caesar
mtg item mtg item mtg item mtg
current entered author minutes
-------- -------- -------- -------
Yes Yes Rasputin Rien
Yes Yes Rasputin Nothing
Yes No Gaius Rien
Yes No Gaius Nothing
The following Query, based on your same Tables and the same Combo Box,
links each meeting in the [meeting schedule] Table with some of the
agenda items in the [meeting items] Table, and for "Conspiracy" only 2
records appear, not 4.
[Q_Agenda] SQL:
SELECT Sched.[mtg type], Sched.[mtg date],
Items.Sequence, Items.[mtg item entered],
Items.[mtg item author],
Items.[mtg item] AS Minutes
FROM [meeting schedule] AS Sched
INNER JOIN [meeting items] AS Items
ON Sched.[meeting schedule_ID]
= Items.[meeting schedule_ID]
WHERE (((Sched.[mtg type])
=[Forms]![frm mtg schedule]![Combo6])
AND ((Items.[mtg item current])=True))
ORDER BY Sched.[mtg date], Items.Sequence;
[Q_Agenda] Query Datasheet View, with "Conspiracy" selected in the Combo
Box:
mtg type mtg date Sequence mtg item
entered
---------- --------- -------- --------
Conspiracy 3/15/2006 30 No
Conspiracy 3/28/2006 20 Yes
mtg item Minutes
author
-------- -----------------------------
Gaius Discuss post-mortem on Caesar
Rasputin Contact coup organizers
[Q_Agenda] Query Datasheet View, with "Election" selected in the Combo Box:
mtg type mtg date Sequence mtg item
entered
-------- --------- -------- --------
Election 3/24/2006 10 No
mtg item Minutes
author
-------- ------------
Solon Cast ballots
Now, I understand that it's quite possible that you don't want the
information organized this way. For example, maybe you want one agenda
item linked with every meeting in which it's been discussed. To do that
(which I did not do in this example), I would set up another Table, in
which each record would represent the appearance of an agenda item at a
meeting; it would include a key linking to [meeting schedule] (for the
meeting) and one to [meeting items] (for the item itself). I might move
the [Sequence] field to this new Table as well, so that you could
specify a different sequence for the item in each meeting in which it is
discussed.
Your suggestion that you'd like to copy records from one Table to
another, so that you'd have the same information actually stored in two
places (instead of just referred to, using key values), is likely to
lead you to trouble. Having to maintain the same information in
multiple places just means that you have to do unnecessary extra work.
There are times when copying stored information might make sense (such
as if you're worried that your computer is running too slowly and you
link to the field through a series of a dozen Queries and you almost
never have to update that field), but that sort of thing should be FAR
from your mind now, when you are trying to model something in the real
world and want the model to be as accurate as possible. If you find
that you are storing information in a field that you have enough
information elsewhere to compute, that's a danger signal that the Table
design needs to be examined, as something is apparently being kept where
it shouldn't be. (Look up "normalization" or "normalize" in Access Help.)
-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.
> Hello All
>
[quoted text clipped - 30 lines]
> WHERE ((([meeting items].[mtg type])=[Forms]![frm mtg schedule]![Combo6])
> AND (([meeting items].[mtg item current])=True));
Leslie Isaacs - 30 Mar 2006 16:37 GMT
John
May thanks for your comprehensive reply.
I will work through it and get back with any further prioblems.
Thanks again
Les
> Although there is no one best way to organize your information, I have
> some suggestions and an example that I hope may be useful.
[quoted text clipped - 203 lines]
>> WHERE ((([meeting items].[mtg type])=[Forms]![frm mtg schedule]![Combo6])
>> AND (([meeting items].[mtg item current])=True));