Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / March 2006

Tip: Looking for answers? Try searching our database.

Adding query results to a table field

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Leslie Isaacs - 27 Mar 2006 13:22 GMT
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));
Vincent Johns - 29 Mar 2006 04:01 GMT
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));
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.