how do I attach a value to a check box in access 2007?

  • Thread starter Thread starter janelgirl
  • Start date Start date
J

janelgirl

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.
 
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.
Click to expand...

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
 
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.
 
Thanks - this helps!

KARL DEWEY said:
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.
Click to expand...
 
Thanks, this helps!

John W. Vinson said:
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
Click to expand...
 
Back
Top