Very new to Access - Having trouble with Multivalued field

[Beginning2GetFrustrated]

I am trying to build a somewhat simple database for my sister. I have all of
the tables and forms set up the way she needs them but now when I am trying
to set up the queries and reports she wants I am running in to a wall. I
have a table for products which has the name and ID and a cost associated
with each. I have a table for orders which I have set up to track who sold
it, what they sold, when and who they sold it to. I have the "what they
sold" as a list box which lets the user simply check off which items were
bought. (There are only 5 products that can be sold, so that is why I used
the list box). I am trying to run a query and a report that will give a
summary by month by Sales person with a total $ amt sold. The problem is
that the multivalued box has 5 choices and each of those have a dollar amount
associated with each item (in its respective table) and when I run these
queries and reports it is only taking the cost of one of the items selected.
I am still quite new to the terminology and am somewhat clueless to coding,
is there some brave soul that might be able to point me in the right
direction??

 

[John W. Vinson]

I am trying to build a somewhat simple database for my sister. I have all of
the tables and forms set up the way she needs them but now when I am trying
to set up the queries and reports she wants I am running in to a wall. I
have a table for products which has the name and ID and a cost associated
with each. I have a table for orders which I have set up to track who sold
it, what they sold, when and who they sold it to. I have the "what they
sold" as a list box which lets the user simply check off which items were
bought. (There are only 5 products that can be sold, so that is why I used
the list box). I am trying to run a query and a report that will give a
summary by month by Sales person with a total $ amt sold. The problem is
that the multivalued box has 5 choices and each of those have a dollar amount
associated with each item (in its respective table) and when I run these
queries and reports it is only taking the cost of one of the items selected.
I am still quite new to the terminology and am somewhat clueless to coding,
is there some brave soul that might be able to point me in the right
direction??

Multivalue fields are of VERY limited capability, and are not standard
relational design. A lot of us avoid them entirely!

You need *another table* - actually, a multivalue field is created internally
by creating another (hidden) table. Consider the following three table
solution:

Orders
OrderID <primary key>
SellerID <link to table of sellers>
CustomerID <link to table of customers>
SaleDate
<more info about the order as a thing in itself>

Products
ProductID
ProductName
Price

OrderDetail
OrderDetailID <autonumber primary key>
OrderID <link to Orders>
ProductID <link to Products>
SalePrice


The Price value from the products table should be copied into the SalePrice
field on the OrderDetail table to record the price actually used in this sale
(she might offer a discount, or the stored price might change in the future).

I haven't looked at the A2007 Northwind sample database, so it may use some of
these funky features that Microsoft has introduced, but it's probably worth a
look. Also look at the tutorials and other resources at

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

A free tutorial written by Crystal (MS Access MVP):
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]

I am trying to build a somewhat simple database for my sister. I have all of
the tables and forms set up the way she needs them but now when I am trying
to set up the queries and reports she wants I am running in to a wall. I
have a table for products which has the name and ID and a cost associated
with each. I have a table for orders which I have set up to track who sold
it, what they sold, when and who they sold it to. I have the "what they
sold" as a list box which lets the user simply check off which items were
bought. (There are only 5 products that can be sold, so that is why I used
the list box). I am trying to run a query and a report that will give a
summary by month by Sales person with a total $ amt sold. The problem is
that the multivalued box has 5 choices and each of those have a dollar amount
associated with each item (in its respective table) and when I run these
queries and reports it is only taking the cost of one of the items selected.
I am still quite new to the terminology and am somewhat clueless to coding,
is there some brave soul that might be able to point me in the right
direction??

Multivalue fields are of VERY limited capability, and are not standard
relational design. A lot of us avoid them entirely!

You need *another table* - actually, a multivalue field is created internally
by creating another (hidden) table. Consider the following three table
solution:

Orders
OrderID <primary key>
SellerID <link to table of sellers>
CustomerID <link to table of customers>
SaleDate
<more info about the order as a thing in itself>

Products
ProductID
ProductName
Price

OrderDetail
OrderDetailID <autonumber primary key>
OrderID <link to Orders>
ProductID <link to Products>
SalePrice


The Price value from the products table should be copied into the SalePrice
field on the OrderDetail table to record the price actually used in this sale
(she might offer a discount, or the stored price might change in the future).

I haven't looked at the A2007 Northwind sample database, so it may use some of
these funky features that Microsoft has introduced, but it's probably worth a
look. Also look at the tutorials and other resources at

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

A free tutorial written by Crystal (MS Access MVP):
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