Complexed Search Form - Stumped Novice User

  • Thread starter Thread starter Walt
  • Start date Start date
W

Walt

Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
 
Walt said:
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...

You've left us guessing at what these things mean, and that makes it
hard to offer concrete advice. Initial suggestions:

When you select a Report/Plan, you could use an event procedure to
filter the records to include only those related to the selected
Report/Plan. The AfterUpdate event could be the one to use, as it
triggers when an item in the combo is selected.

Then you have two optional independent keyword (or "string") search
conditions. The OnClick event procedure could construct a further
filter condition from the contents of the search boxes (text boxes)
depending on the states of the check boxes, then run the report using
that filter. I _think_ you can set a filter using the Report/Plan combo
box and refine that further by running DoCmd.OpenReport and specifying a
Where condition but I'd want to test that! (Otherwise you'd need to
combine everything into a single filter.)

However, I rather think your data model needs work. If there could be
more than one recommendation or Accomplishment for a given Report/Plan
then you should have them in separate related tables. Rule 1 is get
your table design right before doing anything else. You're rather vague
about what these things are, so I suspect you haven't pinned down what
is to go into your table(s) and fields. This free video sample training
module may help:
http://www.lynda.com/home/Player.aspx?lpk4=31001

For the report, assuming that you do end up with separate tables for
Reports/Plans, Recommendations and Accomplishments I'd be thinking in
terms of a main report with two embedded subreports, made visible or
invisible depending on the check boxes.

Phil, London
 
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

Walt said:
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...
 
Ken said:
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

Walt said:
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...
Click to expand...

That's a great solution from Ken (as usual). Makes me realise I still
talk SQL with a heavy "procedural code" accent! I might be losing my
reluctance to express an opinion but I learn as much as anyone on this
group!

:-)

Phil
 
Steve said:
The simple solution to creating a search form that is harder than what you
know how to do is to get someone to create the form for you. I can build
your search form for you. I have been providing this kind of help with
Access applications for over ten years. My fee would be very modest! The
first step would be to make sure your database meets Phil's Rule 1 which
is get your table design right before doing anything else. I would check
the design of your tables and work with you if modifications need to be
made. Once Phil's Rule 1 is satisfied then we would work closely together
to create your search form. If you would like to work together, contact
me.

Steve
Click to expand...



These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
John... Visio MVP said:
These newsgroups are provided by Microsoft for FREE peer to peer
support. There are many highly qualified individuals who gladly help for
free. Stevie is not one of them, but he is the only one who just does
not get the idea of "FREE" support. He offers questionable results at
unreasonable prices. If he was any good, the "thousands" of people he
claims to have helped would be flooding him with work, but there appears
to be a continuous drought and he needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to
create the OP an Access database)



Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
Click to expand...


To the OP (Original Poster) - just see if you can find a useful
contribution Steve has ever made to this group. Google for MVP, while
you're at it.

Phil
 
Ken, Philip, John, and Gina - thank you all. I truly apprecaite the help
this forum and class act people like youselves provide; especially to those
of us still learning access but being asked to develop databases that go
beyond our capabilities.

Based on the comments, I broke the tables out. Here's what I did and I hope
my explanations make more sense. I now have 10 tables. Here's the names and
columns:

1)Plan1: Plan1ID, Description
2)Plan2: Plan2ID, Description
3)Plan3: Plan3ID, Description
4)Sources: SourceID, Description
5)Recommendations: RecID, Description
6)Accomplishments: AccID, Description, Impact, Obstacles, Next Steps
7)POC: POCID, 1stName, LastName, Title, Office, Phone, Email

8)Junction_Master:Plan1ID, Plan2ID, Plan3ID, SourceID, RecID,AccID, POCID
(My thinking was I need a table that relates all of the IDs together to make
querying easier - is that necessary?)

9)Junction_Plans: Plan1ID, Plan2ID, Plan3ID (this table is a cross reference
of the plans. For example: Plan1ID01 is the same as Plan2ID01,02,03 and
Plan3ID01 - 10
I created this table to try to ref the ID numbers in the master table so I
can query and pull the description of all plans that relate to a given
recommendation and/or accomplishment

10)Junction_SRA:SourceID,RecID, AccID (Not sure I need this table)

Here's what I'm trying to do. My database needs to allow a POC to input a
recommedation or accomplishment or both. The POC should be able to tell me
what plan the rec / acc ties into and based on their selection I should be
able to run a report that automatically pulls the other plans that coincide.
A recommendation will not always have an accomplishment and vice versus.
Also, not all rec/acc will have POCs which I think stops me from being able
to use autonumbers so I'm using text boxes for the IDs. I have set-up any of
the data entry forms yet, thinking that since i have a lot of information
already it would be better to get the search function working so I can pull
reports to give to my bosses now...and buy me some time.

Thank you all again for all the help because I truly need it.


Ken Sheridan said:
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

Walt said:
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...
Click to expand...
 
Walt said:
Ken, Philip, John, and Gina - thank you all. I truly apprecaite the help
this forum and class act people like youselves provide; especially to those
of us still learning access but being asked to develop databases that go
beyond our capabilities.

Based on the comments, I broke the tables out. Here's what I did and I hope
my explanations make more sense. I now have 10 tables. Here's the names and
columns:

1)Plan1: Plan1ID, Description
2)Plan2: Plan2ID, Description
3)Plan3: Plan3ID, Description
4)Sources: SourceID, Description
5)Recommendations: RecID, Description
6)Accomplishments: AccID, Description, Impact, Obstacles, Next Steps
7)POC: POCID, 1stName, LastName, Title, Office, Phone, Email

8)Junction_Master:Plan1ID, Plan2ID, Plan3ID, SourceID, RecID,AccID, POCID
(My thinking was I need a table that relates all of the IDs together to make
querying easier - is that necessary?)

9)Junction_Plans: Plan1ID, Plan2ID, Plan3ID (this table is a cross reference
of the plans. For example: Plan1ID01 is the same as Plan2ID01,02,03 and
Plan3ID01 - 10
I created this table to try to ref the ID numbers in the master table so I
can query and pull the description of all plans that relate to a given
recommendation and/or accomplishment

10)Junction_SRA:SourceID,RecID, AccID (Not sure I need this table)

Here's what I'm trying to do. My database needs to allow a POC to input a
recommedation or accomplishment or both. The POC should be able to tell me
what plan the rec / acc ties into and based on their selection I should be
able to run a report that automatically pulls the other plans that coincide.
A recommendation will not always have an accomplishment and vice versus.
Also, not all rec/acc will have POCs which I think stops me from being able
to use autonumbers so I'm using text boxes for the IDs. I have set-up any of
the data entry forms yet, thinking that since i have a lot of information
already it would be better to get the search function working so I can pull
reports to give to my bosses now...and buy me some time.

Thank you all again for all the help because I truly need it.


Ken Sheridan said:
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

Walt said:
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...
Click to expand...
Click to expand...

I'm afraid I'm baffled by your terminology, and can't get a handle on
what these things are. A POC is a person, right?

An autonumber field is a convenient way of making records unique, but
the value shouldn't be used any other way than to refer to a record. I
don't think there's any reason not to use one - managing IDs manually is
likely to be error-prone.

I've grave misgivings about the three different "Plan" tables. If the
names are so similar then the "entities" are likely to be similar enough
to be in the same table.

Why not tell us in plain language what these various things are, and
what their relationships are in the real world, and then we'll see what
table structure you need?

Be reassured; this is the hardest bit! Get your tables right and
everything else is downhill. The converse is also true!

Phil
 
Hi Philip,

I'll try to explain. Yes POC (Point of contact) is a person. The Plans are
documents that outline what things our company needs to accomplish, from a
high level plan to what we do everyday.

The plan names are different, but they are all plans which is why I used
Plan1, Plan2, etc. Maybe that was a bad idea. Plan1 is our Mission Plan,
Plan2 is our Goal Plan, and Plan3 is Daily Operations. I'll rename the
tables to better reflect what they are. Plan 1 only has 6 items, while Plan
2 has 16 and Plan 3 has a lot more, which is why I put them in separate
tables. Our mission plan relates to several items in our Goal plan and even
more items in Daily Ops Plan. So I thought I should break them out in
separate tables because when I put them in the same table I have several
duplicates, for example:

MISPlanID01 | GPlanID01 | DOPlanID01
MISPlanID01 | GPlanID01 | DOPlanID02
MISPlanID01 | GPlanID02 | DOPlanID03

I thought putting them in separate tables with ID numbers as a field for
each initiative and the description field with the name of each initiative
and then make a table like the example above that related the IDs would make
it simplier to relate the tables and query in a way to pull all the
relationships. So I can see that a recommendation (which comes from a
source) crosses our MISPlan at ID03 and GPlan at IDs 4 -5 for example. I
hope this makes more sense.

Also, something I left out from my other post is that I might have several
accomplishments per recommendation.

Thanks Philip

Philip Herlihy said:
Walt said:
Ken, Philip, John, and Gina - thank you all. I truly apprecaite the help
this forum and class act people like youselves provide; especially to those
of us still learning access but being asked to develop databases that go
beyond our capabilities.

Based on the comments, I broke the tables out. Here's what I did and I hope
my explanations make more sense. I now have 10 tables. Here's the names and
columns:

1)Plan1: Plan1ID, Description
2)Plan2: Plan2ID, Description
3)Plan3: Plan3ID, Description
4)Sources: SourceID, Description
5)Recommendations: RecID, Description
6)Accomplishments: AccID, Description, Impact, Obstacles, Next Steps
7)POC: POCID, 1stName, LastName, Title, Office, Phone, Email

8)Junction_Master:Plan1ID, Plan2ID, Plan3ID, SourceID, RecID,AccID, POCID
(My thinking was I need a table that relates all of the IDs together to make
querying easier - is that necessary?)

9)Junction_Plans: Plan1ID, Plan2ID, Plan3ID (this table is a cross reference
of the plans. For example: Plan1ID01 is the same as Plan2ID01,02,03 and
Plan3ID01 - 10
I created this table to try to ref the ID numbers in the master table so I
can query and pull the description of all plans that relate to a given
recommendation and/or accomplishment

10)Junction_SRA:SourceID,RecID, AccID (Not sure I need this table)

Here's what I'm trying to do. My database needs to allow a POC to input a
recommedation or accomplishment or both. The POC should be able to tell me
what plan the rec / acc ties into and based on their selection I should be
able to run a report that automatically pulls the other plans that coincide.
A recommendation will not always have an accomplishment and vice versus.
Also, not all rec/acc will have POCs which I think stops me from being able
to use autonumbers so I'm using text boxes for the IDs. I have set-up any of
the data entry forms yet, thinking that since i have a lot of information
already it would be better to get the search function working so I can pull
reports to give to my bosses now...and buy me some time.

Thank you all again for all the help because I truly need it.


Ken Sheridan said:
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

:

Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...
Click to expand...

I'm afraid I'm baffled by your terminology, and can't get a handle on
what these things are. A POC is a person, right?

An autonumber field is a convenient way of making records unique, but
the value shouldn't be used any other way than to refer to a record. I
don't think there's any reason not to use one - managing IDs manually is
likely to be error-prone.

I've grave misgivings about the three different "Plan" tables. If the
names are so similar then the "entities" are likely to be similar enough
to be in the same table.

Why not tell us in plain language what these various things are, and
what their relationships are in the real world, and then we'll see what
table structure you need?

Be reassured; this is the hardest bit! Get your tables right and
everything else is downhill. The converse is also true!

Phil
Click to expand...
 
Ken,

I tried what you suggested and I can't get it to work. I have a question.
I created a form with a drop down list and two text boxes. How do I
associate the form and the items on the form with the sql query?

My sources table, Recommendations table, and accomplishments table are all
individual tables now but I created a master junction table that shows how
these tables relate via ID. I'm completely lost at this point.

Ken Sheridan said:
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

Walt said:
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...
Click to expand...
 
Walt said:
Hi Philip,

I'll try to explain. Yes POC (Point of contact) is a person. The Plans are
documents that outline what things our company needs to accomplish, from a
high level plan to what we do everyday.

The plan names are different, but they are all plans which is why I used
Plan1, Plan2, etc. Maybe that was a bad idea. Plan1 is our Mission Plan,
Plan2 is our Goal Plan, and Plan3 is Daily Operations. I'll rename the
tables to better reflect what they are. Plan 1 only has 6 items, while Plan
2 has 16 and Plan 3 has a lot more, which is why I put them in separate
tables. Our mission plan relates to several items in our Goal plan and even
more items in Daily Ops Plan. So I thought I should break them out in
separate tables because when I put them in the same table I have several
duplicates, for example:

MISPlanID01 | GPlanID01 | DOPlanID01
MISPlanID01 | GPlanID01 | DOPlanID02
MISPlanID01 | GPlanID02 | DOPlanID03

I thought putting them in separate tables with ID numbers as a field for
each initiative and the description field with the name of each initiative
and then make a table like the example above that related the IDs would make
it simplier to relate the tables and query in a way to pull all the
relationships. So I can see that a recommendation (which comes from a
source) crosses our MISPlan at ID03 and GPlan at IDs 4 -5 for example. I
hope this makes more sense.

Also, something I left out from my other post is that I might have several
accomplishments per recommendation.

Thanks Philip

Philip Herlihy said:
Walt said:
Ken, Philip, John, and Gina - thank you all. I truly apprecaite the help
this forum and class act people like youselves provide; especially to those
of us still learning access but being asked to develop databases that go
beyond our capabilities.

Based on the comments, I broke the tables out. Here's what I did and I hope
my explanations make more sense. I now have 10 tables. Here's the names and
columns:

1)Plan1: Plan1ID, Description
2)Plan2: Plan2ID, Description
3)Plan3: Plan3ID, Description
4)Sources: SourceID, Description
5)Recommendations: RecID, Description
6)Accomplishments: AccID, Description, Impact, Obstacles, Next Steps
7)POC: POCID, 1stName, LastName, Title, Office, Phone, Email

8)Junction_Master:Plan1ID, Plan2ID, Plan3ID, SourceID, RecID,AccID, POCID
(My thinking was I need a table that relates all of the IDs together to make
querying easier - is that necessary?)

9)Junction_Plans: Plan1ID, Plan2ID, Plan3ID (this table is a cross reference
of the plans. For example: Plan1ID01 is the same as Plan2ID01,02,03 and
Plan3ID01 - 10
I created this table to try to ref the ID numbers in the master table so I
can query and pull the description of all plans that relate to a given
recommendation and/or accomplishment

10)Junction_SRA:SourceID,RecID, AccID (Not sure I need this table)

Here's what I'm trying to do. My database needs to allow a POC to input a
recommedation or accomplishment or both. The POC should be able to tell me
what plan the rec / acc ties into and based on their selection I should be
able to run a report that automatically pulls the other plans that coincide.
A recommendation will not always have an accomplishment and vice versus.
Also, not all rec/acc will have POCs which I think stops me from being able
to use autonumbers so I'm using text boxes for the IDs. I have set-up any of
the data entry forms yet, thinking that since i have a lot of information
already it would be better to get the search function working so I can pull
reports to give to my bosses now...and buy me some time.

Thank you all again for all the help because I truly need it.


:

Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

:

Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...
I'm afraid I'm baffled by your terminology, and can't get a handle on
what these things are. A POC is a person, right?

An autonumber field is a convenient way of making records unique, but
the value shouldn't be used any other way than to refer to a record. I
don't think there's any reason not to use one - managing IDs manually is
likely to be error-prone.

I've grave misgivings about the three different "Plan" tables. If the
names are so similar then the "entities" are likely to be similar enough
to be in the same table.

Why not tell us in plain language what these various things are, and
what their relationships are in the real world, and then we'll see what
table structure you need?

Be reassured; this is the hardest bit! Get your tables right and
everything else is downhill. The converse is also true!

Phil
Click to expand...
Click to expand...

I can see that there are a lot of possible "rules" here, most of which I
can only guess at. (I went from one of the largest corporations in the
world to a one-man band, so I don't have to do this stuff any more!).

I think the way to decipher this is to figure out whether these things
are in one-to-one, one-to-many or many-to-many relationships.

I'd start by tentatively assuming the various Plans are the same sort of
thing, and that one Plan can be related to more than one other Plan (one
to many, or maybe many to many). I think this is the hardest thing to
get right. Let's say the Plans are a strict hierarchy: the Daily Ops
Plan will be part of one wider Goals Plan, which in turn is part of a
wider Mission Plan. In that case each Plan record would have an
attribute/field/column (pick your favourite word) for "level"
(indicating which of those three levels it is) and an optional reference
to another record in the same table (optional because Mission Plans
don't have a parent).

You may have (or want to anticipate) a situation in which one plan can
be "informed" by more than one parent. That gives you a many-to-many
relationship, and that calls for a junction table (otherwise called an
"associative" table. That means for one particular association between
Plans, you'd have the Key Values (think ID's) of two individual Plans
side by side, perhaps with additional fields, like the date the
association was defined. I'd be inclined to assume this many-to-many
relationship, as it's the most flexible. You could scan through this
associative table for all Plans which are the direct descendent of one
of the Mission Plans, and also scan for their descendents. I've got a
terrible habit of thinking in terms of VBA code for this sort of thing
(not as smart at SQL as I should be) but I believe a query incorporating
a subquery could do this in one operation (would have to go and try
it!). If you're sure you don't need to have Plans with multiple
Parents, it's all simpler, of course.

The rest is a little easier. From what you say (assuming I'm piecing
together everthing from several posts correctly!): a recommendation can
be traced to one (or more?) Plan. The design interpretation is similar
- if it can only be the descendent of one Plan (presumably a Daily Ops
level Plan) then you need only include a (mandatory?) Plan-ID field. If
a recommendation can be traced to more than one plan, then you need an
associative table.

Next, an accomplishment. Just one parent recommendation or several?
Same design consequences. Notice the "many" part of "one-to-many" means
that the same Recommendation-ID could appear in many Accomplishment records.

Finally, I guess a Recommendation will have only one source, so you need
only include a Source-ID field in the Recommendations table to refer to
a record in the Sources table.

How's that? Does that fit? Don't be afraid to say no! (with reasons)

Phil
 
Walt said:
Ken,

I tried what you suggested and I can't get it to work. I have a question.
I created a form with a drop down list and two text boxes. How do I
associate the form and the items on the form with the sql query?

My sources table, Recommendations table, and accomplishments table are all
individual tables now but I created a master junction table that shows how
these tables relate via ID. I'm completely lost at this point.

Ken Sheridan said:
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

Walt said:
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you
Click to expand...
Click to expand...
Click to expand...


Ken's approach is really elegant, but you do need to get your tables
nailed-down first. Come back to this later.

For the record, the query is written to refer to the contents of
controls (boxes, etc) on the form as "parameters" (a value obtained from
elsewhere). Instead of writing:

SELECT Jam, Other-stuff FROM Jam-table
WHERE Jam = "strawberry";

you'd write:

SELECT Jam, Other-stuff FROM Jam-table
WHERE Jam = Forms![MyJamForm].[jam-textbox];

... and the query will use the value currently found in the text-box
named "jam-textbox" on the form named "MyJamForm". If you type
"strawberry" into the textbox, you'll get the same result.

If you're a Yank, think Jello, by the way. Knew an Indian guy called
JamShed once. Thought that was great...

Phil
 
Philip thank you. I think I have many to many relationships that I need to
sort out before I can nail down this search function. I'll play with it for
a bit and then reply to you on Monday. BTW, Jam works fine with me. Have a
good weekend.

Regards,


Philip Herlihy said:
Walt said:
Hi Philip,

I'll try to explain. Yes POC (Point of contact) is a person. The Plans are
documents that outline what things our company needs to accomplish, from a
high level plan to what we do everyday.

The plan names are different, but they are all plans which is why I used
Plan1, Plan2, etc. Maybe that was a bad idea. Plan1 is our Mission Plan,
Plan2 is our Goal Plan, and Plan3 is Daily Operations. I'll rename the
tables to better reflect what they are. Plan 1 only has 6 items, while Plan
2 has 16 and Plan 3 has a lot more, which is why I put them in separate
tables. Our mission plan relates to several items in our Goal plan and even
more items in Daily Ops Plan. So I thought I should break them out in
separate tables because when I put them in the same table I have several
duplicates, for example:

MISPlanID01 | GPlanID01 | DOPlanID01
MISPlanID01 | GPlanID01 | DOPlanID02
MISPlanID01 | GPlanID02 | DOPlanID03

I thought putting them in separate tables with ID numbers as a field for
each initiative and the description field with the name of each initiative
and then make a table like the example above that related the IDs would make
it simplier to relate the tables and query in a way to pull all the
relationships. So I can see that a recommendation (which comes from a
source) crosses our MISPlan at ID03 and GPlan at IDs 4 -5 for example. I
hope this makes more sense.

Also, something I left out from my other post is that I might have several
accomplishments per recommendation.

Thanks Philip

Philip Herlihy said:
Walt wrote:
Ken, Philip, John, and Gina - thank you all. I truly apprecaite the help
this forum and class act people like youselves provide; especially to those
of us still learning access but being asked to develop databases that go
beyond our capabilities.

Based on the comments, I broke the tables out. Here's what I did and I hope
my explanations make more sense. I now have 10 tables. Here's the names and
columns:

1)Plan1: Plan1ID, Description
2)Plan2: Plan2ID, Description
3)Plan3: Plan3ID, Description
4)Sources: SourceID, Description
5)Recommendations: RecID, Description
6)Accomplishments: AccID, Description, Impact, Obstacles, Next Steps
7)POC: POCID, 1stName, LastName, Title, Office, Phone, Email

8)Junction_Master:Plan1ID, Plan2ID, Plan3ID, SourceID, RecID,AccID, POCID
(My thinking was I need a table that relates all of the IDs together to make
querying easier - is that necessary?)

9)Junction_Plans: Plan1ID, Plan2ID, Plan3ID (this table is a cross reference
of the plans. For example: Plan1ID01 is the same as Plan2ID01,02,03 and
Plan3ID01 - 10
I created this table to try to ref the ID numbers in the master table so I
can query and pull the description of all plans that relate to a given
recommendation and/or accomplishment

10)Junction_SRA:SourceID,RecID, AccID (Not sure I need this table)

Here's what I'm trying to do. My database needs to allow a POC to input a
recommedation or accomplishment or both. The POC should be able to tell me
what plan the rec / acc ties into and based on their selection I should be
able to run a report that automatically pulls the other plans that coincide.
A recommendation will not always have an accomplishment and vice versus.
Also, not all rec/acc will have POCs which I think stops me from being able
to use autonumbers so I'm using text boxes for the IDs. I have set-up any of
the data entry forms yet, thinking that since i have a lot of information
already it would be better to get the search function working so I can pull
reports to give to my bosses now...and buy me some time.

Thank you all again for all the help because I truly need it.


:

Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

:

Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you

I'm afraid I'm baffled by your terminology, and can't get a handle on
what these things are. A POC is a person, right?

An autonumber field is a convenient way of making records unique, but
the value shouldn't be used any other way than to refer to a record. I
don't think there's any reason not to use one - managing IDs manually is
likely to be error-prone.

I've grave misgivings about the three different "Plan" tables. If the
names are so similar then the "entities" are likely to be similar enough
to be in the same table.

Why not tell us in plain language what these various things are, and
what their relationships are in the real world, and then we'll see what
table structure you need?

Be reassured; this is the hardest bit! Get your tables right and
everything else is downhill. The converse is also true!

Phil
Click to expand...
Click to expand...

I can see that there are a lot of possible "rules" here, most of which I
can only guess at. (I went from one of the largest corporations in the
world to a one-man band, so I don't have to do this stuff any more!).

I think the way to decipher this is to figure out whether these things
are in one-to-one, one-to-many or many-to-many relationships.

I'd start by tentatively assuming the various Plans are the same sort of
thing, and that one Plan can be related to more than one other Plan (one
to many, or maybe many to many). I think this is the hardest thing to
get right. Let's say the Plans are a strict hierarchy: the Daily Ops
Plan will be part of one wider Goals Plan, which in turn is part of a
wider Mission Plan. In that case each Plan record would have an
attribute/field/column (pick your favourite word) for "level"
(indicating which of those three levels it is) and an optional reference
to another record in the same table (optional because Mission Plans
don't have a parent).

You may have (or want to anticipate) a situation in which one plan can
be "informed" by more than one parent. That gives you a many-to-many
relationship, and that calls for a junction table (otherwise called an
"associative" table. That means for one particular association between
Plans, you'd have the Key Values (think ID's) of two individual Plans
side by side, perhaps with additional fields, like the date the
association was defined. I'd be inclined to assume this many-to-many
relationship, as it's the most flexible. You could scan through this
associative table for all Plans which are the direct descendent of one
of the Mission Plans, and also scan for their descendents. I've got a
terrible habit of thinking in terms of VBA code for this sort of thing
(not as smart at SQL as I should be) but I believe a query incorporating
a subquery could do this in one operation (would have to go and try
it!). If you're sure you don't need to have Plans with multiple
Parents, it's all simpler, of course.

The rest is a little easier. From what you say (assuming I'm piecing
together everthing from several posts correctly!): a recommendation can
be traced to one (or more?) Plan. The design interpretation is similar
- if it can only be the descendent of one Plan (presumably a Daily Ops
level Plan) then you need only include a (mandatory?) Plan-ID field. If
a recommendation can be traced to more than one plan, then you need an
associative table.

Next, an accomplishment. Just one parent recommendation or several?
Same design consequences. Notice the "many" part of "one-to-many" means
that the same Recommendation-ID could appear in many Accomplishment records.

Finally, I guess a Recommendation will have only one source, so you need
only include a Source-ID field in the Recommendations table to refer to
a record in the Sources table.

How's that? Does that fit? Don't be afraid to say no! (with reasons)

Phil
Click to expand...
 
Ken said:
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters.  You can make a parameter
optional by testing for 'OR <parameter> IS NULL' in the query, so your query
would be something like this:
Click to expand...
SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);
Click to expand...
The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.  
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.
Click to expand...
Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values inthese
fields.  If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:
Click to expand...
SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*"  &  Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);
Click to expand...
I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.
Click to expand...
To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:
Click to expand...
Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]
Click to expand...
You'll need to change the table, fields, form and control names in the above
to your real ones of course.
Click to expand...
The 'Go' button on the form simply needs to open the report.
Click to expand...
Take note of what Phil has said about the possible need to decompose your
table into related tables.  It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.
Click to expand...
Ken Sheridan
Stafford, England
Click to expand...
Hi,
I have to create a search form that is harder than what I know how to do.  
The page needs to have the following:
1 - Reports/Plans [drop down list]
2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)
3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)
4 - Results Should inlcude:  
Recommendations [check box]  (if checked rec's show in report)
Accomplishments [check box]  (if checked acc's show in report)
(if both are checked then both show in the report)
Go [button] once clicks returns report based on selections
Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.
I'm hoping this is easier to do than what I'm making it.  I appreciate any
assistance.
Thank you
Click to expand...
Click to expand...

That's a great solution from Ken (as usual).  Makes me realise I still
talk SQL with a heavy "procedural code" accent!  I might be losing my
reluctance to express an opinion but I learn as much as anyone on this
group!

:-)

Phil
Click to expand...

thanks.
 
Hi Walt,

The simple solution to creating a search form that is harder than what you
know how to do is to get someone to create the form for you. I can build
your search form for you. I have been providing this kind of help with
Access applications for over ten years. My fee would be very modest! The
first step would be to make sure your database meets Phil's Rule 1 which is
get your table design right before doing anything else. I would check the
design of your tables and work with you if modifications need to be made.
Once Phil's Rule 1 is satisfied then we would work closely together to
create your search form. If you would like to work together, contact me.

Steve
(e-mail address removed)


Hi,
Click to expand...
I have to create a search form that is harder than what I know how to do.
The page needs to have the following:
Click to expand...
1 - Reports/Plans [drop down list]
Click to expand...
2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)
Click to expand...
3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)
Click to expand...
4 - Results Should inlcude:
Recommendations [check box]  (if checked rec's show in report)
Accomplishments [check box]  (if checked acc's show in report)
(if both are checked then both show in the report)
Click to expand...
Go [button] once clicks returns report based on selections
Click to expand...
Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.
Click to expand...
I'm hoping this is easier to do than what I'm making it.  I appreciate any
assistance.
Click to expand...
Thank you
Click to expand...
Click to expand...

i am satisfied ,i trust myself.thanks for your advise.
 
These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the ideaof
"FREE" support. He offers questionable results at unreasonable prices. Ifhe
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.

Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve

Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve

Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve

One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...

John... Visio MVP
Click to expand...

i am not cheater,this is only my creation.i am not say that i am
totally perfect,i say again i am housewife.
 
Back
Top