Prev: lookup
Next: Basic Form Examples in Access.
From: janelgirl on 20 Apr 2010 17:02
In a database, there are several items in a single row that may or may not be
listed as yes/no in a checkbox format. When I transfer the information from
said database into a report, I filter the results to list only the items that
were checked "yes". Is it possible that the items that were checked "yes"
could return a value from a query on the report? For example, in the
database, I checked "flowers", "card", and "candies" for John Doe. A
seperate query has the individual costs that it takes for "flowers" or "card"
or "candies" . Those queries have their own totals (which may change as I
alter the info to reflect changing costs for materials). When I select "yes"
for "flowers" in the database, how do I get the report to show the total from
the query? Is this possible? I am new at access 2007 (and in general), so
please use basic language. Thanks in advance for any help that you may
provide.
From: John W. Vinson on 20 Apr 2010 19:06
On Tue, 20 Apr 2010 14:02:01 -0700, janelgirl
<janelgirl(a)discussions.microsoft.com> wrote:

>In a database, there are several items in a single row that may or may not be
>listed as yes/no in a checkbox format. When I transfer the information from
>said database into a report, I filter the results to list only the items that
>were checked "yes". Is it possible that the items that were checked "yes"
>could return a value from a query on the report? For example, in the
>database, I checked "flowers", "card", and "candies" for John Doe. A
>seperate query has the individual costs that it takes for "flowers" or "card"
>or "candies" . Those queries have their own totals (which may change as I
>alter the info to reflect changing costs for materials). When I select "yes"
>for "flowers" in the database, how do I get the report to show the total from
>the query? Is this possible? I am new at access 2007 (and in general), so
>please use basic language. Thanks in advance for any help that you may
>provide.

Your table design is incorrect.

What will you do when you add another possible item? Redesign your table,
rewrite all your queries, redesign your forms, recreate all your reports?
OUCH!!!

If each person may get many different items, and each item may be taken by
many different people, a proper table structure uses *three tables*:
Individuals; Items; ItemsTaken. The third table would have links to the
primary key of the first two tables.

you might want to look at some of the tutorials here; Crystal's "Normalization
101" would speak to this issue:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
From: KARL DEWEY on 20 Apr 2010 19:25
A check box will return a value of -1 (minus one) or 0 (zero) as it is
checked or not checked.
Your design is wrong. Based on what you posted you need three tables.
tblOrder --
OrderID - Autonumber - primary key
OrderDate - DateTime
OrderFor -
DeliverDate
OrderedBy

tblItems --
ItemID - Autonumber - primary key
ItemName
Size
Color
Price -

tblOrderItem --
OrderItemID - Autonumber - primary key
OrderID - number - foreign key
ItemID - number - foreign key
QTY

Use a form/subform for Order/Items with Master/Child links set on OrderID.
Use a combo to select items to be on the subform.

--
Build a little, test a little.


"janelgirl" wrote:

> In a database, there are several items in a single row that may or may not be
> listed as yes/no in a checkbox format. When I transfer the information from
> said database into a report, I filter the results to list only the items that
> were checked "yes". Is it possible that the items that were checked "yes"
> could return a value from a query on the report? For example, in the
> database, I checked "flowers", "card", and "candies" for John Doe. A
> seperate query has the individual costs that it takes for "flowers" or "card"
> or "candies" . Those queries have their own totals (which may change as I
> alter the info to reflect changing costs for materials). When I select "yes"
> for "flowers" in the database, how do I get the report to show the total from
> the query? Is this possible? I am new at access 2007 (and in general), so
> please use basic language. Thanks in advance for any help that you may
> provide.
From: janelgirl on 28 Apr 2010 16:28
Thanks - this helps!

"KARL DEWEY" wrote:

> A check box will return a value of -1 (minus one) or 0 (zero) as it is
> checked or not checked.
> Your design is wrong. Based on what you posted you need three tables.
> tblOrder --
> OrderID - Autonumber - primary key
> OrderDate - DateTime
> OrderFor -
> DeliverDate
> OrderedBy
>
> tblItems --
> ItemID - Autonumber - primary key
> ItemName
> Size
> Color
> Price -
>
> tblOrderItem --
> OrderItemID - Autonumber - primary key
> OrderID - number - foreign key
> ItemID - number - foreign key
> QTY
>
> Use a form/subform for Order/Items with Master/Child links set on OrderID.
> Use a combo to select items to be on the subform.
>
> --
> Build a little, test a little.
>
>
> "janelgirl" wrote:
>
> > In a database, there are several items in a single row that may or may not be
> > listed as yes/no in a checkbox format. When I transfer the information from
> > said database into a report, I filter the results to list only the items that
> > were checked "yes". Is it possible that the items that were checked "yes"
> > could return a value from a query on the report? For example, in the
> > database, I checked "flowers", "card", and "candies" for John Doe. A
> > seperate query has the individual costs that it takes for "flowers" or "card"
> > or "candies" . Those queries have their own totals (which may change as I
> > alter the info to reflect changing costs for materials). When I select "yes"
> > for "flowers" in the database, how do I get the report to show the total from
> > the query? Is this possible? I am new at access 2007 (and in general), so
> > please use basic language. Thanks in advance for any help that you may
> > provide.
 | 
Pages: 1
Prev: lookup
Next: Basic Form Examples in Access.