SQL syntax for subforms of subforms that must relate.

[Cynde via AccessMonster.com]

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I
have had to add levels so to speak with more agenda tables. I have 5 levels
total so far. Each of the agenda level tables are set up the
same as the original agenda table but with identifying level names.

tblAgendaItems
AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaItem
AgendaItemName
AgendaItemMinutes

tblAgendaItemsLevel2
AgendaIDLevel2 (foreign key to AgendaID)
MtgID (Number)
AgendaItemLevel2
AgendaItemNameLevel2
AgendaItemMinLevel2

each of the tables after that up to level 4 are set up the same way.
In the relationships, I have the tblAgendaItems related to the
tblMeetingTable in a one-many via MtgID. I have the tblAgendaItems related
via the AgendaID in one-one with tblAgendaItemsLevel2 and then
tbleAgendaItemsLevel2 as a one-one with the level 3 table and level 3 table
as a one-one with level 4 table. I have tried several different
relationships as well as different syntaxes for the SQL and I can't get it to
work. I'm sure my syntax is incorrect but I can't figure out how to get to
the subsubform of level 2 (haven't got beyond level 2 yet). Each subform of
agendaitems (level 2, 3, 4) are subforms of the level above and I have them
set as continuous forms (although I did try single form and that didn't work
either). Below is my current syntax for the level 2 sql which I have edited
many times over in the last few days with no luck. I have also included my
syntax (drawn from yours) that I used to connect the meeting names with
agendas (which works very well). I am trying to get the AgendaIDs to flow
down to level 2 as well as the AgendaItem (text as number). I've tried
putting the sql into different forms and into different events and still am
without a clue as to what I'm doing incorrectly. Perhaps I'm tackling this
all wrong. Any suggestions or direction would be greatly appreciated.

This one directly (my guesstimate) below doesn't work. This is to a subform
(level2) of the
subform (AgendaItems) of the mainform (Meetings).

INSERT INTO AgendaItemsLevel2 ( AgendaItemLevel2, AgendaIDLevel2 )
SELECT AgendaItems.AgendaItem, AgendaItems.AgendaID
FROM AgendaItems INNER JOIN AgendaItemsLevel2 ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2
WHERE (((AgendaItems.MtgID)=[AgendaItemsLevel2].[MtgID]));

Below is the one adapted from your example and it works. This is for a direct
subform to the meeting form.

INSERT INTO AgendaItems ( MtgID, AgendaItem, AgendaItemName )
SELECT Forms!MeetingForm!MtgID, AgendaItems.AgendaItem, AgendaItems.
AgendaItemName
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

Is there a way to write a syntax that will flow down each level of agenda?
Thanks very much.
Cynde

 

[Daryl S]

Cynde -

Each table should have its own primary key, and for any tables representing
a 'child' to a 'parent' table, it needs a foreign key that is the primary key
for the 'parent' table. I think you need to change your tblAgendaItemsLevel2
table to be like this (as well as the other levels):

AgendaItemIDLevel2 (autonumber, primary key)
AgendaID (foreign key to AgendaID)
AgendaItemNameLevel2
AgendaItemMinLevel2

I am not sure what the AgendaItemLevel2 is, so I am not sure if you need
that or not.

Next, how do you determine what level 2 agenda items to add? For the first
level of agenda items, you used the name of the meeting to look up a
'default' or 'template' mtgID to get the agenda items. Are you going to
build a similar lookup table for the other levels, or will you want to use
that same 'default' MtgID as a template to determine which level 2 agenda
items to add?

If you want the same mtgID to determine the level 2 agenda items, then you
need to join the MeetingNamesTable to help you select the right records.
This will populate all the level 2 agenda items for each 'level 1' agenda
item at once. Here is how you do that (this is not tested, so I could have a
typo or something):

INSERT INTO AgendaItemsLevel2 (AgendaID, AgendaItemNameLevel2)
SELECT AgendaItems.AgendaID, AgendaItemsLevel2.AgendaItemNameLevel2
FROM (AgendaItemsLevel2 INNER JOIN AgendaItems ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2) INNER JOIN MeetingNamesTable ON
MeetingNamesTable.MtgID=AgendaItems.MtgID
WHERE (MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName);
--
Daryl S

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I
have had to add levels so to speak with more agenda tables. I have 5 levels
total so far. Each of the agenda level tables are set up the
same as the original agenda table but with identifying level names.

tblAgendaItems
AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaItem
AgendaItemName
AgendaItemMinutes

tblAgendaItemsLevel2
AgendaIDLevel2 (foreign key to AgendaID)
MtgID (Number)
AgendaItemLevel2
AgendaItemNameLevel2
AgendaItemMinLevel2

each of the tables after that up to level 4 are set up the same way.
In the relationships, I have the tblAgendaItems related to the
tblMeetingTable in a one-many via MtgID. I have the tblAgendaItems related
via the AgendaID in one-one with tblAgendaItemsLevel2 and then
tbleAgendaItemsLevel2 as a one-one with the level 3 table and level 3 table
as a one-one with level 4 table. I have tried several different
relationships as well as different syntaxes for the SQL and I can't get it to
work. I'm sure my syntax is incorrect but I can't figure out how to get to
the subsubform of level 2 (haven't got beyond level 2 yet). Each subform of
agendaitems (level 2, 3, 4) are subforms of the level above and I have them
set as continuous forms (although I did try single form and that didn't work
either). Below is my current syntax for the level 2 sql which I have edited
many times over in the last few days with no luck. I have also included my
syntax (drawn from yours) that I used to connect the meeting names with
agendas (which works very well). I am trying to get the AgendaIDs to flow
down to level 2 as well as the AgendaItem (text as number). I've tried
putting the sql into different forms and into different events and still am
without a clue as to what I'm doing incorrectly. Perhaps I'm tackling this
all wrong. Any suggestions or direction would be greatly appreciated.

This one directly (my guesstimate) below doesn't work. This is to a subform
(level2) of the
subform (AgendaItems) of the mainform (Meetings).

INSERT INTO AgendaItemsLevel2 ( AgendaItemLevel2, AgendaIDLevel2 )
SELECT AgendaItems.AgendaItem, AgendaItems.AgendaID
FROM AgendaItems INNER JOIN AgendaItemsLevel2 ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2
WHERE (((AgendaItems.MtgID)=[AgendaItemsLevel2].[MtgID]));

Below is the one adapted from your example and it works. This is for a direct
subform to the meeting form.

INSERT INTO AgendaItems ( MtgID, AgendaItem, AgendaItemName )
SELECT Forms!MeetingForm!MtgID, AgendaItems.AgendaItem, AgendaItems.
AgendaItemName
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

Is there a way to write a syntax that will flow down each level of agenda?
Thanks very much.
Cynde

 

[Cynde via AccessMonster.com]

Daryl, The AgendaItemLevel2 is a number that correlates to the agenda item
on the very first agenda form. For instance if AgendaItem1 has sub items
under it such as Level1, Level2, etc., I was using that number to attempt to
make sure that the levels stuck with the correct item and in order because
the way I was doing it was causing item 3 to go on all items as sub levels.
Possibly I won't need it but I haven't tried your syntax yet because I have a
couple questions. First, I am assuming from what I am reading that this
syntax is completely separate from the first syntax you provided in that I
will need both sql queries correct (and then another for each level)? If so,
can you tell me in what event they should be placed and on what form? I know
the first syntax is in the afterupdate event of the MeetingName field;
however, since the AgendaForm's MeetingName is an unbound control that
updates from the first SQL I'm not sure if I can put an event there, or
should I have this SQL in the button that will open the agenda subform
subform (likewise an SQL such as this on each subform button level down) or
should it (they) be somewhere in the main form where the original syntax is
(I don't know if it's possible to "nest"/"stack" SQL like IIF statements, etc.
, and put them all in one place) or is it actually possible to put an event
action in an unbound control? Being really new to SQL, I keep rereading
your syntax and trying to wrap my brain around it's logic and I'm afraid the
lightbulb isn't running on full power just yet. Thank you for all of your
help and patience, Daryl.

Cynde -

Each table should have its own primary key, and for any tables representing
a 'child' to a 'parent' table, it needs a foreign key that is the primary key
for the 'parent' table. I think you need to change your tblAgendaItemsLevel2
table to be like this (as well as the other levels):

AgendaItemIDLevel2 (autonumber, primary key)
AgendaID (foreign key to AgendaID)
AgendaItemNameLevel2
AgendaItemMinLevel2

I am not sure what the AgendaItemLevel2 is, so I am not sure if you need
that or not.

Next, how do you determine what level 2 agenda items to add? For the first
level of agenda items, you used the name of the meeting to look up a
'default' or 'template' mtgID to get the agenda items. Are you going to
build a similar lookup table for the other levels, or will you want to use
that same 'default' MtgID as a template to determine which level 2 agenda
items to add?

If you want the same mtgID to determine the level 2 agenda items, then you
need to join the MeetingNamesTable to help you select the right records.
This will populate all the level 2 agenda items for each 'level 1' agenda
item at once. Here is how you do that (this is not tested, so I could have a
typo or something):

INSERT INTO AgendaItemsLevel2 (AgendaID, AgendaItemNameLevel2)
SELECT AgendaItems.AgendaID, AgendaItemsLevel2.AgendaItemNameLevel2
FROM (AgendaItemsLevel2 INNER JOIN AgendaItems ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2) INNER JOIN MeetingNamesTable ON
MeetingNamesTable.MtgID=AgendaItems.MtgID
WHERE (MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName);

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I

[quoted text clipped - 59 lines]

Thanks very much.
Cynde

 

[Daryl S]

Cynde -

I think there may be a better way to do this. Instead of one table for each
level, you can add the 'level' and 'parentID' fields to the Agenda table, and
all levels can be stored in one table. The new structure should be something
like this:

AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaLevel (datatype integer)
AgendaParentID (datatype long integer)
AgendaItem
AgendaItemName
AgendaItemMinutes
AgendaMtgItemsSort (text - will make display on forms and reports much
easier)

The AgendaLevel field can be set to one for your first level, and will
increment the lower down you go. For the first level records, the
AgendaParentID can be blank, since we have the MtgID indicating the 'parent'.
For the lower levels of agenda items, the AgendaParentID will contain the
AgendaID of the 'parent' or next-higher level. While you don't technically
need the AgendaLevel field, I expect it will be easier for you to view the
agenda items with that in place. The AgendaMtgItemsSort field will contain
the 'path' of meetingID through AgendaIds at each level. This will help by
giving you a sort field for your reports and queries without having to
calculate that through recursion.

That said, since we have changed the structure of the table, the original
SQL needs to be adjusted to take into account the new fields. This now
includes the AgendaLevel, and it is set to one (1) for all new agenda items
at the first level. We will not populate the AgendaParentID for the first
level, so it is not included in the new SQL. The first AgendaMtgItemsSort
will be the mtgID-AgendaItem Here is what the first SQL looks like now (this
adds top-level agenda items for new meetings):

INSERT INTO AgendaItems ( MtgID, AgendaLevel, AgendaItem, AgendaItemName,
AgendaMtgItemsSort )
SELECT Forms!MeetingForm!MtgID, 1, AgendaItems.AgendaItem,
AgendaItems.AgendaItemName, Forms!MeetingForm!MtgID & "-" &
Format(Str(AgendaItems.AgendaItem),"00")
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

At this point, I would suggest saving a copy of your database as it is now
(before you make these changes). Then add in these changes and get the first
level working.

Adding the lower-level agenda items will be tricky, but once it is coded, it
will handle all levels of agenda items, so you woulnd't need to worry about a
request to add 'just one more level'.

--
Daryl S

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I
have had to add levels so to speak with more agenda tables. I have 5 levels
total so far. Each of the agenda level tables are set up the
same as the original agenda table but with identifying level names.

tblAgendaItems
AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaItem
AgendaItemName
AgendaItemMinutes

tblAgendaItemsLevel2
AgendaIDLevel2 (foreign key to AgendaID)
MtgID (Number)
AgendaItemLevel2
AgendaItemNameLevel2
AgendaItemMinLevel2

each of the tables after that up to level 4 are set up the same way.
In the relationships, I have the tblAgendaItems related to the
tblMeetingTable in a one-many via MtgID. I have the tblAgendaItems related
via the AgendaID in one-one with tblAgendaItemsLevel2 and then
tbleAgendaItemsLevel2 as a one-one with the level 3 table and level 3 table
as a one-one with level 4 table. I have tried several different
relationships as well as different syntaxes for the SQL and I can't get it to
work. I'm sure my syntax is incorrect but I can't figure out how to get to
the subsubform of level 2 (haven't got beyond level 2 yet). Each subform of
agendaitems (level 2, 3, 4) are subforms of the level above and I have them
set as continuous forms (although I did try single form and that didn't work
either). Below is my current syntax for the level 2 sql which I have edited
many times over in the last few days with no luck. I have also included my
syntax (drawn from yours) that I used to connect the meeting names with
agendas (which works very well). I am trying to get the AgendaIDs to flow
down to level 2 as well as the AgendaItem (text as number). I've tried
putting the sql into different forms and into different events and still am
without a clue as to what I'm doing incorrectly. Perhaps I'm tackling this
all wrong. Any suggestions or direction would be greatly appreciated.

This one directly (my guesstimate) below doesn't work. This is to a subform
(level2) of the
subform (AgendaItems) of the mainform (Meetings).

INSERT INTO AgendaItemsLevel2 ( AgendaItemLevel2, AgendaIDLevel2 )
SELECT AgendaItems.AgendaItem, AgendaItems.AgendaID
FROM AgendaItems INNER JOIN AgendaItemsLevel2 ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2
WHERE (((AgendaItems.MtgID)=[AgendaItemsLevel2].[MtgID]));

Below is the one adapted from your example and it works. This is for a direct
subform to the meeting form.

INSERT INTO AgendaItems ( MtgID, AgendaItem, AgendaItemName )
SELECT Forms!MeetingForm!MtgID, AgendaItems.AgendaItem, AgendaItems.
AgendaItemName
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

Is there a way to write a syntax that will flow down each level of agenda?
Thanks very much.
Cynde

 

[Cynde via AccessMonster.com]

Daryl,
If I'm understanding correctly, this new way would completely replace the
very first sql you showed me correct? I will try to accomplish what you have
suggested but I may (will likely) have to consult you again please. Thank
you for all of your time and effort because this dbase, although totally
confuses me, is giving me a great opportunity for learning. Thanks so much!

Cynde -

I think there may be a better way to do this. Instead of one table for each
level, you can add the 'level' and 'parentID' fields to the Agenda table, and
all levels can be stored in one table. The new structure should be something
like this:

AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaLevel (datatype integer)
AgendaParentID (datatype long integer)
AgendaItem
AgendaItemName
AgendaItemMinutes
AgendaMtgItemsSort (text - will make display on forms and reports much
easier)

The AgendaLevel field can be set to one for your first level, and will
increment the lower down you go. For the first level records, the
AgendaParentID can be blank, since we have the MtgID indicating the 'parent'.
For the lower levels of agenda items, the AgendaParentID will contain the
AgendaID of the 'parent' or next-higher level. While you don't technically
need the AgendaLevel field, I expect it will be easier for you to view the
agenda items with that in place. The AgendaMtgItemsSort field will contain
the 'path' of meetingID through AgendaIds at each level. This will help by
giving you a sort field for your reports and queries without having to
calculate that through recursion.

That said, since we have changed the structure of the table, the original
SQL needs to be adjusted to take into account the new fields. This now
includes the AgendaLevel, and it is set to one (1) for all new agenda items
at the first level. We will not populate the AgendaParentID for the first
level, so it is not included in the new SQL. The first AgendaMtgItemsSort
will be the mtgID-AgendaItem Here is what the first SQL looks like now (this
adds top-level agenda items for new meetings):

INSERT INTO AgendaItems ( MtgID, AgendaLevel, AgendaItem, AgendaItemName,
AgendaMtgItemsSort )
SELECT Forms!MeetingForm!MtgID, 1, AgendaItems.AgendaItem,
AgendaItems.AgendaItemName, Forms!MeetingForm!MtgID & "-" &
Format(Str(AgendaItems.AgendaItem),"00")
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

At this point, I would suggest saving a copy of your database as it is now
(before you make these changes). Then add in these changes and get the first
level working.

Adding the lower-level agenda items will be tricky, but once it is coded, it
will handle all levels of agenda items, so you woulnd't need to worry about a
request to add 'just one more level'.

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I

[quoted text clipped - 59 lines]

Thanks very much.
Cynde

 

[Cynde via AccessMonster.com]

Daryl, I have just changed the AgendaItems table but before I go any further,
I wanted to mention that I also have an Action Items table that will directly
relate to the meetings. Currently, I have it separate and haven't done much
with it because of the struggle with the agendas to date. After reading your
suggestion, I am thinking it might be advisable to also include the action
items in the agenda table but that might not be possible because the action
items quite frequently roll from one meeting to the next until they are
completed. Am I correct in thinking that I should keep the Action Items table
separate from the Agenda Items and attack that issue after I get the Agendas
working? Thank you.

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I
have had to add levels so to speak with more agenda tables. I have 5 levels
total so far. Each of the agenda level tables are set up the
same as the original agenda table but with identifying level names.

tblAgendaItems
AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaItem
AgendaItemName
AgendaItemMinutes

tblAgendaItemsLevel2
AgendaIDLevel2 (foreign key to AgendaID)
MtgID (Number)
AgendaItemLevel2
AgendaItemNameLevel2
AgendaItemMinLevel2

each of the tables after that up to level 4 are set up the same way.
In the relationships, I have the tblAgendaItems related to the
tblMeetingTable in a one-many via MtgID. I have the tblAgendaItems related
via the AgendaID in one-one with tblAgendaItemsLevel2 and then
tbleAgendaItemsLevel2 as a one-one with the level 3 table and level 3 table
as a one-one with level 4 table. I have tried several different
relationships as well as different syntaxes for the SQL and I can't get it to
work. I'm sure my syntax is incorrect but I can't figure out how to get to
the subsubform of level 2 (haven't got beyond level 2 yet). Each subform of
agendaitems (level 2, 3, 4) are subforms of the level above and I have them
set as continuous forms (although I did try single form and that didn't work
either). Below is my current syntax for the level 2 sql which I have edited
many times over in the last few days with no luck. I have also included my
syntax (drawn from yours) that I used to connect the meeting names with
agendas (which works very well). I am trying to get the AgendaIDs to flow
down to level 2 as well as the AgendaItem (text as number). I've tried
putting the sql into different forms and into different events and still am
without a clue as to what I'm doing incorrectly. Perhaps I'm tackling this
all wrong. Any suggestions or direction would be greatly appreciated.

This one directly (my guesstimate) below doesn't work. This is to a subform
(level2) of the
subform (AgendaItems) of the mainform (Meetings).

INSERT INTO AgendaItemsLevel2 ( AgendaItemLevel2, AgendaIDLevel2 )
SELECT AgendaItems.AgendaItem, AgendaItems.AgendaID
FROM AgendaItems INNER JOIN AgendaItemsLevel2 ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2
WHERE (((AgendaItems.MtgID)=[AgendaItemsLevel2].[MtgID]));

Below is the one adapted from your example and it works. This is for a direct
subform to the meeting form.

INSERT INTO AgendaItems ( MtgID, AgendaItem, AgendaItemName )
SELECT Forms!MeetingForm!MtgID, AgendaItems.AgendaItem, AgendaItems.
AgendaItemName
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

Is there a way to write a syntax that will flow down each level of agenda?
Thanks very much.
Cynde

 

[Daryl S]

Cynde -

I would get the agenda items done first before you tackle the action items.

In order to design the action items table(s), you need to know how they
relate to meetings or agenda items. Can there be many action items
associated with a meeting (my guess is yes). Can there be many meetings
associated with one action item (it sounds like yes). Is there any
association between action items and agenda items, or just to the meeting?
When you understand the fields you need and the relationships, then post a
new item in the forum asking for help with that.

--
Daryl S

Daryl, I have just changed the AgendaItems table but before I go any further,
I wanted to mention that I also have an Action Items table that will directly
relate to the meetings. Currently, I have it separate and haven't done much
with it because of the struggle with the agendas to date. After reading your
suggestion, I am thinking it might be advisable to also include the action
items in the agenda table but that might not be possible because the action
items quite frequently roll from one meeting to the next until they are
completed. Am I correct in thinking that I should keep the Action Items table
separate from the Agenda Items and attack that issue after I get the Agendas
working? Thank you.

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I
have had to add levels so to speak with more agenda tables. I have 5 levels
total so far. Each of the agenda level tables are set up the
same as the original agenda table but with identifying level names.

tblAgendaItems
AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaItem
AgendaItemName
AgendaItemMinutes

tblAgendaItemsLevel2
AgendaIDLevel2 (foreign key to AgendaID)
MtgID (Number)
AgendaItemLevel2
AgendaItemNameLevel2
AgendaItemMinLevel2

each of the tables after that up to level 4 are set up the same way.
In the relationships, I have the tblAgendaItems related to the
tblMeetingTable in a one-many via MtgID. I have the tblAgendaItems related
via the AgendaID in one-one with tblAgendaItemsLevel2 and then
tbleAgendaItemsLevel2 as a one-one with the level 3 table and level 3 table
as a one-one with level 4 table. I have tried several different
relationships as well as different syntaxes for the SQL and I can't get it to
work. I'm sure my syntax is incorrect but I can't figure out how to get to
the subsubform of level 2 (haven't got beyond level 2 yet). Each subform of
agendaitems (level 2, 3, 4) are subforms of the level above and I have them
set as continuous forms (although I did try single form and that didn't work
either). Below is my current syntax for the level 2 sql which I have edited
many times over in the last few days with no luck. I have also included my
syntax (drawn from yours) that I used to connect the meeting names with
agendas (which works very well). I am trying to get the AgendaIDs to flow
down to level 2 as well as the AgendaItem (text as number). I've tried
putting the sql into different forms and into different events and still am
without a clue as to what I'm doing incorrectly. Perhaps I'm tackling this
all wrong. Any suggestions or direction would be greatly appreciated.

This one directly (my guesstimate) below doesn't work. This is to a subform
(level2) of the
subform (AgendaItems) of the mainform (Meetings).

INSERT INTO AgendaItemsLevel2 ( AgendaItemLevel2, AgendaIDLevel2 )
SELECT AgendaItems.AgendaItem, AgendaItems.AgendaID
FROM AgendaItems INNER JOIN AgendaItemsLevel2 ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2
WHERE (((AgendaItems.MtgID)=[AgendaItemsLevel2].[MtgID]));

Below is the one adapted from your example and it works. This is for a direct
subform to the meeting form.

INSERT INTO AgendaItems ( MtgID, AgendaItem, AgendaItemName )
SELECT Forms!MeetingForm!MtgID, AgendaItems.AgendaItem, AgendaItems.
AgendaItemName
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

Is there a way to write a syntax that will flow down each level of agenda?
Thanks very much.
Cynde

--



.

 

[Daryl S]

Cynde -

Yes, the SQL in my last posting will completely replace the first SQL.
Since the table structure changed, we needed to add the new fields to the
original SQL. I did not give you the second SQL yet which will add the
Agenda Items, as that will need some recursive code around it. I'll play
with it as I have time and add it in here.

--
Daryl S

Daryl,
If I'm understanding correctly, this new way would completely replace the
very first sql you showed me correct? I will try to accomplish what you have
suggested but I may (will likely) have to consult you again please. Thank
you for all of your time and effort because this dbase, although totally
confuses me, is giving me a great opportunity for learning. Thanks so much!

Cynde -

I think there may be a better way to do this. Instead of one table for each
level, you can add the 'level' and 'parentID' fields to the Agenda table, and
all levels can be stored in one table. The new structure should be something
like this:

AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaLevel (datatype integer)
AgendaParentID (datatype long integer)
AgendaItem
AgendaItemName
AgendaItemMinutes
AgendaMtgItemsSort (text - will make display on forms and reports much
easier)

The AgendaLevel field can be set to one for your first level, and will
increment the lower down you go. For the first level records, the
AgendaParentID can be blank, since we have the MtgID indicating the 'parent'.
For the lower levels of agenda items, the AgendaParentID will contain the
AgendaID of the 'parent' or next-higher level. While you don't technically
need the AgendaLevel field, I expect it will be easier for you to view the
agenda items with that in place. The AgendaMtgItemsSort field will contain
the 'path' of meetingID through AgendaIds at each level. This will help by
giving you a sort field for your reports and queries without having to
calculate that through recursion.

That said, since we have changed the structure of the table, the original
SQL needs to be adjusted to take into account the new fields. This now
includes the AgendaLevel, and it is set to one (1) for all new agenda items
at the first level. We will not populate the AgendaParentID for the first
level, so it is not included in the new SQL. The first AgendaMtgItemsSort
will be the mtgID-AgendaItem Here is what the first SQL looks like now (this
adds top-level agenda items for new meetings):

INSERT INTO AgendaItems ( MtgID, AgendaLevel, AgendaItem, AgendaItemName,
AgendaMtgItemsSort )
SELECT Forms!MeetingForm!MtgID, 1, AgendaItems.AgendaItem,
AgendaItems.AgendaItemName, Forms!MeetingForm!MtgID & "-" &
Format(Str(AgendaItems.AgendaItem),"00")
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

At this point, I would suggest saving a copy of your database as it is now
(before you make these changes). Then add in these changes and get the first
level working.

Adding the lower-level agenda items will be tricky, but once it is coded, it
will handle all levels of agenda items, so you woulnd't need to worry about a
request to add 'just one more level'.

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I

[quoted text clipped - 59 lines]

Thanks very much.
Cynde

 

[Cynde via AccessMonster.com]

Thanks Daryl. I got level 1 working just fine and attempted to make the
other levels work with no real luck. My abilities with SQL are extremely
limited and I've never heard of recursive code so I'm lost. I looked through
my books yesterday trying to find something to assist but I just became more
confused. I tried to use a setvalue but couldn't get it to work if I had more
than one level under another level plus it didn't work if I deleted something
because it was a rather elementary setvalue to begin with. I don't
understand what the AgendaMtgItemsSort field is in regard to containing a
'path' of meetingID through agendaIds at each level so I don't know what to
do with that. I think perhaps I need to step away from this project for a
day or two and regroup my brain cells. I'll keep an eye here for any new
postings from you as well. Have a great day!

Cynde -

Yes, the SQL in my last posting will completely replace the first SQL.
Since the table structure changed, we needed to add the new fields to the
original SQL. I did not give you the second SQL yet which will add the
Agenda Items, as that will need some recursive code around it. I'll play
with it as I have time and add it in here.

Daryl,
If I'm understanding correctly, this new way would completely replace the

[quoted text clipped - 61 lines]

 

[Daryl S]

Cynde -

Based on the tables we talked about, here are the two SQL statements to add
agenda items and lower-level agenda items based on the 'master' meeting in
the MeetingNamesTable.


' First SQL - add first level of Agenda Items based on MtgID.
strSQL = "INSERT INTO tblAgendaItems ( MtgID, AgendaLevel, AgendaItem,
AgendaItemName, " & _
" AgendaMtgItemsSort ) " & _
" SELECT " & Forms!MeetingForm!MtgID & ", 1, tblAgendaItems.AgendaItem, " & _
" tblAgendaItems.AgendaItemName, '" & Forms!MeetingForm!MtgID & "-'" & _
" & Format(Str(tblAgendaItems.AgendaItem),'00') " & _
" FROM MeetingNamesTable INNER JOIN tblAgendaItems ON
MeetingNamesTable.MtgID = tblAgendaItems.MtgID " & _
" WHERE MeetingNamesTable.MeetingName = '" & Forms!MeetingForm!MeetingName &
"'" & _
" AND tblAgendaItems.AgendaLevel = 1;"


' Second SQL - append all the other levels of agenda items based on the
MtgID.
strSQL = "INSERT INTO tblAgendaItems ( MtgID, AgendaItem, AgendaItemName,
AgendaLevel, AgendaMtgItemsSort )" & _
" SELECT " & [Forms]![MeetingForm]![MtgID] & ",
tblAgendaItems_Master.AgendaItem, tblAgendaItems_Master.AgendaItemName," & _
" tblAgendaItems_Master.AgendaLevel, " & _
[Forms]![MeetingForm]![MtgID] & " &
Right([AgendaMtgItemsSort],Len([AgendaMtgItemsSort])-Len([tblAgendaItems_Master]![MtgID]))" & _
" FROM MeetingNamesTable INNER JOIN tblAgendaItems AS tblAgendaItems_Master
ON MeetingNamesTable.MtgID = tblAgendaItems_Master.MtgID " & _
" WHERE (((tblAgendaItems_Master.AgendaLevel)>1) AND
((MeetingNamesTable.MeetingName)='" & _
[Forms]![MeetingForm]![MeetingName] & "'));"

Remember after running this code to do a requery to see the results:
Me.Requery

For displaying the agenda items (since they are all in one table now), you
may want to add another calculated field that will prepend spaces to the
agenda names so they look indented. Something like this:
=Space(3*([AgendaLevel]-1)) & [AgendaItemName]
This can be in the query for the recordsource of the subform. The records
should be sorted by AgendaMtgItemsSort for any Meeting.

Good luck!

--
Daryl S

Daryl, I have just changed the AgendaItems table but before I go any further,
I wanted to mention that I also have an Action Items table that will directly
relate to the meetings. Currently, I have it separate and haven't done much
with it because of the struggle with the agendas to date. After reading your
suggestion, I am thinking it might be advisable to also include the action
items in the agenda table but that might not be possible because the action
items quite frequently roll from one meeting to the next until they are
completed. Am I correct in thinking that I should keep the Action Items table
separate from the Agenda Items and attack that issue after I get the Agendas
working? Thank you.

Hello. I am working on a meeting dbase and I have got to this point with help
from this site but am stuck here. Due to the intricacy of some agendas, I
have had to add levels so to speak with more agenda tables. I have 5 levels
total so far. Each of the agenda level tables are set up the
same as the original agenda table but with identifying level names.

tblAgendaItems
AgendaID (autonumber, primary key)
MtgID (foreign key)
AgendaItem
AgendaItemName
AgendaItemMinutes

tblAgendaItemsLevel2
AgendaIDLevel2 (foreign key to AgendaID)
MtgID (Number)
AgendaItemLevel2
AgendaItemNameLevel2
AgendaItemMinLevel2

each of the tables after that up to level 4 are set up the same way.
In the relationships, I have the tblAgendaItems related to the
tblMeetingTable in a one-many via MtgID. I have the tblAgendaItems related
via the AgendaID in one-one with tblAgendaItemsLevel2 and then
tbleAgendaItemsLevel2 as a one-one with the level 3 table and level 3 table
as a one-one with level 4 table. I have tried several different
relationships as well as different syntaxes for the SQL and I can't get it to
work. I'm sure my syntax is incorrect but I can't figure out how to get to
the subsubform of level 2 (haven't got beyond level 2 yet). Each subform of
agendaitems (level 2, 3, 4) are subforms of the level above and I have them
set as continuous forms (although I did try single form and that didn't work
either). Below is my current syntax for the level 2 sql which I have edited
many times over in the last few days with no luck. I have also included my
syntax (drawn from yours) that I used to connect the meeting names with
agendas (which works very well). I am trying to get the AgendaIDs to flow
down to level 2 as well as the AgendaItem (text as number). I've tried
putting the sql into different forms and into different events and still am
without a clue as to what I'm doing incorrectly. Perhaps I'm tackling this
all wrong. Any suggestions or direction would be greatly appreciated.

This one directly (my guesstimate) below doesn't work. This is to a subform
(level2) of the
subform (AgendaItems) of the mainform (Meetings).

INSERT INTO AgendaItemsLevel2 ( AgendaItemLevel2, AgendaIDLevel2 )
SELECT AgendaItems.AgendaItem, AgendaItems.AgendaID
FROM AgendaItems INNER JOIN AgendaItemsLevel2 ON AgendaItems.AgendaID =
AgendaItemsLevel2.AgendaIDLevel2
WHERE (((AgendaItems.MtgID)=[AgendaItemsLevel2].[MtgID]));

Below is the one adapted from your example and it works. This is for a direct
subform to the meeting form.

INSERT INTO AgendaItems ( MtgID, AgendaItem, AgendaItemName )
SELECT Forms!MeetingForm!MtgID, AgendaItems.AgendaItem, AgendaItems.
AgendaItemName
FROM MeetingNamesTable INNER JOIN AgendaItems ON MeetingNamesTable.
MtgID=AgendaItems.MtgID
WHERE MeetingNamesTable.MeetingName=Forms!MeetingForm!MeetingName;

Is there a way to write a syntax that will flow down each level of agenda?
Thanks very much.
Cynde

--



.

 

[Daryl S]

Cynde -

Based on the tables we talked about, here are the two SQL statements to add
agenda items and lower-level agenda items based on the 'master' meeting in
the MeetingNamesTable. The good news is you don't need to do it recursively
with this SQL. You will understand the AgendaItemSort field once you see it
built - it will cause the items to be sorted with lower-level agenda items
directlyl below their higher-level agenda items. It uses your AgendaItem
field to help with this. Anyway, here are the two SQL statements:


' First SQL - add first level of Agenda Items based on MtgID.
strSQL = "INSERT INTO tblAgendaItems ( MtgID, AgendaLevel, AgendaItem,
AgendaItemName, " & _
" AgendaMtgItemsSort ) " & _
" SELECT " & Forms!MeetingForm!MtgID & ", 1, tblAgendaItems.AgendaItem, " & _
" tblAgendaItems.AgendaItemName, '" & Forms!MeetingForm!MtgID & "-'" & _
" & Format(Str(tblAgendaItems.AgendaItem),'00') " & _
" FROM MeetingNamesTable INNER JOIN tblAgendaItems ON
MeetingNamesTable.MtgID = tblAgendaItems.MtgID " & _
" WHERE MeetingNamesTable.MeetingName = '" & Forms!MeetingForm!MeetingName &
"'" & _
" AND tblAgendaItems.AgendaLevel = 1;"


' Second SQL - append all the other levels of agenda items based on the
MtgID.
strSQL = "INSERT INTO tblAgendaItems ( MtgID, AgendaItem, AgendaItemName,
AgendaLevel, AgendaMtgItemsSort )" & _
" SELECT " & [Forms]![MeetingForm]![MtgID] & ",
tblAgendaItems_Master.AgendaItem, tblAgendaItems_Master.AgendaItemName," & _
" tblAgendaItems_Master.AgendaLevel, " & _
[Forms]![MeetingForm]![MtgID] & " &
Right([AgendaMtgItemsSort],Len([AgendaMtgItemsSort])-Len([tblAgendaItems_Master]![MtgID]))" & _
" FROM MeetingNamesTable INNER JOIN tblAgendaItems AS tblAgendaItems_Master
ON MeetingNamesTable.MtgID = tblAgendaItems_Master.MtgID " & _
" WHERE (((tblAgendaItems_Master.AgendaLevel)>1) AND
((MeetingNamesTable.MeetingName)='" & _
[Forms]![MeetingForm]![MeetingName] & "'));"

Remember after running this code to do a requery to see the results:
Me.Requery

For displaying the agenda items (since they are all in one table now), you
may want to add another calculated field that will prepend spaces to the
agenda names so they look indented. Something like this:
=Space(3*([AgendaLevel]-1)) & [AgendaItemName]
This can be in the query for the recordsource of the subform. The records
should be sorted by AgendaMtgItemsSort for any Meeting.

Good luck!

--
Daryl S

Thanks Daryl. I got level 1 working just fine and attempted to make the
other levels work with no real luck. My abilities with SQL are extremely
limited and I've never heard of recursive code so I'm lost. I looked through
my books yesterday trying to find something to assist but I just became more
confused. I tried to use a setvalue but couldn't get it to work if I had more
than one level under another level plus it didn't work if I deleted something
because it was a rather elementary setvalue to begin with. I don't
understand what the AgendaMtgItemsSort field is in regard to containing a
'path' of meetingID through agendaIds at each level so I don't know what to
do with that. I think perhaps I need to step away from this project for a
day or two and regroup my brain cells. I'll keep an eye here for any new
postings from you as well. Have a great day!

Cynde -

Yes, the SQL in my last posting will completely replace the first SQL.
Since the table structure changed, we needed to add the new fields to the
original SQL. I did not give you the second SQL yet which will add the
Agenda Items, as that will need some recursive code around it. I'll play
with it as I have time and add it in here.

Daryl,
If I'm understanding correctly, this new way would completely replace the

[quoted text clipped - 61 lines]

Thanks very much.
Cynde

--



.

 

[Cynde via AccessMonster.com]

Thanks Daryl. I've been working on it this afternoon and will see how I do.
Thanks again and have a great weekend!

Cynde -

Based on the tables we talked about, here are the two SQL statements to add
agenda items and lower-level agenda items based on the 'master' meeting in
the MeetingNamesTable.

' First SQL - add first level of Agenda Items based on MtgID.
strSQL = "INSERT INTO tblAgendaItems ( MtgID, AgendaLevel, AgendaItem,
AgendaItemName, " & _
" AgendaMtgItemsSort ) " & _
" SELECT " & Forms!MeetingForm!MtgID & ", 1, tblAgendaItems.AgendaItem, " & _
" tblAgendaItems.AgendaItemName, '" & Forms!MeetingForm!MtgID & "-'" & _
" & Format(Str(tblAgendaItems.AgendaItem),'00') " & _
" FROM MeetingNamesTable INNER JOIN tblAgendaItems ON
MeetingNamesTable.MtgID = tblAgendaItems.MtgID " & _
" WHERE MeetingNamesTable.MeetingName = '" & Forms!MeetingForm!MeetingName &
"'" & _
" AND tblAgendaItems.AgendaLevel = 1;"

' Second SQL - append all the other levels of agenda items based on the
MtgID.
strSQL = "INSERT INTO tblAgendaItems ( MtgID, AgendaItem, AgendaItemName,
AgendaLevel, AgendaMtgItemsSort )" & _
" SELECT " & [Forms]![MeetingForm]![MtgID] & ",
tblAgendaItems_Master.AgendaItem, tblAgendaItems_Master.AgendaItemName," & _
" tblAgendaItems_Master.AgendaLevel, " & _
[Forms]![MeetingForm]![MtgID] & " &
Right([AgendaMtgItemsSort],Len([AgendaMtgItemsSort])-Len([tblAgendaItems_Master]![MtgID]))" & _
" FROM MeetingNamesTable INNER JOIN tblAgendaItems AS tblAgendaItems_Master
ON MeetingNamesTable.MtgID = tblAgendaItems_Master.MtgID " & _
" WHERE (((tblAgendaItems_Master.AgendaLevel)>1) AND
((MeetingNamesTable.MeetingName)='" & _
[Forms]![MeetingForm]![MeetingName] & "'));"

Remember after running this code to do a requery to see the results:
Me.Requery

For displaying the agenda items (since they are all in one table now), you
may want to add another calculated field that will prepend spaces to the
agenda names so they look indented. Something like this:
=Space(3*([AgendaLevel]-1)) & [AgendaItemName]
This can be in the query for the recordsource of the subform. The records
should be sorted by AgendaMtgItemsSort for any Meeting.

Good luck!

Daryl, I have just changed the AgendaItems table but before I go any further,
I wanted to mention that I also have an Action Items table that will directly

[quoted text clipped - 70 lines]