Help with a form/table

[Guest]

Hi.
I'm trying to create a pricesheet in a database that has individual prices for different products with the same name in different sizes. I can pull up all of the information because I have it in tables as
Product, size, and price but also with separate product ids, identifying the combination of product and size.
My problem is that I want to create a matrix looking form. Something like
size 1 size 2 size
product 1 price price pric
product 2 price price pric
product 3 price price pric

It's probably easier than I'm imagining. Is it?
The prices change with customers so I'll need to run something that can be regenerated and not just created once

Thanks

My other question is, in an invoice form, I have a contiuous form running a query with product and price. I put in the quantity and multiply that by price to get an extended total. That works fine, but oooops, the quantity goes down to every quantity field on the page. That's wrong. Trouble with the grand total too.
Also, is it possible to list one product at a time and have it add a new line if it's desired? Like a table would do when you're adding records

I'm just a beginner, can you tell. Better with java/html
Thank you

 

[Allen Browne]

Issue 1 - Multiple prices
==================
Presumably your Price table has fields:
ProductID foreign key to product.ProductID
Size the size of the product
PriceEach price of this product at this size.

If so, you can use a crosstab query to get the matrix you want:
1. Create a query into your Price table, and drag the 3 fields into the
grid.

2. Change it to a crosstab query (Crosstab on Query menu).

3. Under ProductID, choose Row Heading.

4. Under Size, choose Column Heading.

5. Under PriceEach, chose Value in the Crosstab row, and First in the Group
By row.

This returns the first price it finds for the combination of product (on the
left) and size (on the top).

Issue 2 - Continuous invoice
=====================
The answer to this question involves creating the right data structure.
Because one invoice can have many rows on it, you need two tables.

The main Invoice table has fields:
InvoiceID AutoNumber primary key
ClientID foreign key to tblClient.ClientID
InvoiceDate Date/Time

The InvoiceDetail table has fields:
InvoiceDetailID AutoNumber primary key
InvoiceID Number (Long) foreign key to Invoice.InvoiceID
ProductID foreign key to Product.ProductID
Quantity Number how many on this row.
PriceEach Currency how much for each one.

If that sounds confusing, open the Northwind sample database that installs
with Access. From the Tools menu, choose Relationships. Look at the Orders
and Order Details table. They do essentially that.

You will then need to create a query that includes a calculated field to get
the result of Quantity times PriceEach. The example query in Northwind is
called "Order Details Extended". For more help with creating calculated
fields, see:
http://allenbrowne.com/casu-14.html

You can then create a form for entering invoices, with a subform for
entering the line items. The Northwind forms are called "Orders" and "Orders
Subform". The subform is based on the query you just created, and that
enables you to sum the PriceExtended field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to create a pricesheet in a database that has individual prices

for different products with the same name in different sizes. I can pull up
all of the information because I have it in tables as

Product, size, and price but also with separate product ids, identifying

the combination of product and size.

My problem is that I want to create a matrix looking form. Something like:
size 1 size 2 size 3
product 1 price price price
product 2 price price price
product 3 price price price

It's probably easier than I'm imagining. Is it?
The prices change with customers so I'll need to run something that can be

regenerated and not just created once.

Thanks!

My other question is, in an invoice form, I have a contiuous form running

a query with product and price. I put in the quantity and multiply that by
price to get an extended total. That works fine, but oooops, the quantity
goes down to every quantity field on the page. That's wrong. Trouble with
the grand total too.

Also, is it possible to list one product at a time and have it add a new

line if it's desired? Like a table would do when you're adding records?

 

[Guest]

Thanks so far this helps, but I can't get this to display it properly. The other thing is that there are several price tiers.
Any ideas? I don't want to have to manually type the form.

 

[Allen Browne]

No more ideas, without knowing you have the correct data structure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks so far this helps, but I can't get this to display it properly. The

other thing is that there are several price tiers.