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 <
[email protected]>
Please feel free to quote anything I say here.
Leslie said:
Hello All
I have a table [meeting items], which contains agenda items for meetings
of a certain 'type'. A yes/no field 'current' denotes which items are
still to be included in forthcoming meetings.
I also have a table [meeting schedule], which is a list of meeting dates
for all 'types' of meetings. This table includes the memo field 'mtg
minutes', into which I would like to be able to add the meeting items
(i.e. the values of the field 'mtg item' in table [meeting items] that
are for that meeting type and which are current. Ideally these 'mtg item'
values would be seperated by some blank lines (= carriage returns?),
because the idea is that the user would then be able to add text under
each 'mtg item' and so produce the minutes of the meeting.
The query I have so far is pasted below. This does return the required
values of 'mtg items', and it would need amending to an update query, but
I can't figure out how to add all the returned values of 'mtg items' to
the field 'mtg minutes'.
I hope someone can help.
Many thanks
Leslie Isaacs
The query so far:
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));
