Table combinations and calculations

[Guest]

I have two tables.
One is a list of products and "base" prices.
The other is a list of allowances which are deducted off the list price
depending on how much product is delivered.
The tables are unrelated as there are no common fields.
How do I make something from the two which shows the following:-

Price for 3t delivered Price for 5t delivered
etc.
Product X
Product Y
Product Z

I've played with queries and crosstab queries and can't just get at what I
want. Any ideas gratefully received, preferably as simple as possible as my
technical knowledge of Access is limited!

 

[kingston via AccessMonster.com]

If I understand correctly, add both tables to a query with no relationships.
Output all fields from your products table. Add two columns (allowance type
and a calculated field for the allowance):

[Allowance], [ProductTable].[BasePrice]-[AllowanceAmount]

You may need to set the query to return Unique Values only. Now try creating
a crosstab query or a pivot table in Excel based on that query. hth

 

[Guest]

My thoughts would be this:

A: Set up your customer table with some kind of unique record. You can do
this on your own or let it auto number them.

B: Set up a query to indicate how many "widgets" the company has ordered.

C: Have this table update to a table to show the number of units sold.

D: Set up a query for the pricing and have it pull the correct price for the
# of unitls sold.

E: Set up a seperate query to multiply off of this calculation.

Here's it writen out:

Customer Table:

Customer A $10 (Base)

Discount Price Table:

1-5 Sold = $1.00 off
5-10 Sold = $2.00 off
10-15 Sold = $3.00 off

Sales Query:

Customer A 10 Widgets Sold = 10 * $10 = $100 Base Price

Discount Query:

10 - 15 Sold = $3.00 off.

Linked Queries:

10 Widgets Sold to Customer A = $100 * Discount ($3.00) =$30 Discount - $100
Total = New Total $70.00.

(e-mail address removed)

 

[Guest]

Thanks for the replies.

Kingston's solution works to a certain degree but is not ideal. I want to
produce price list tables to give to salesmen so I do not want a pivot table
to add the rows and columns up, plus it is extra work to have to import the
data and set up a pivot table everytime a price changes.

Stuart's solution has got me a bit stumped as I don't have a customer table.
The database is for price list maintenance only - so I have a table of
products and base prices then another table for the allowances that are
deducted according to the size of the order.

I could do this exercise in excel, but we want to attach other information
to the products such as supplier contact details, product specifications etc.

 

[krissco]

I have two tables.
One is a list of products and "base" prices.
The other is a list of allowances which are deducted off the list price
depending on how much product is delivered.
The tables are unrelated as there are no common fields.
How do I make something from the two which shows the following:-

Price for 3t delivered Price for 5t delivered
etc.
Product X
Product Y
Product Z

I've played with queries and crosstab queries and can't just get at what I
want. Any ideas gratefully received, preferably as simple as possible as my
technical knowledge of Access is limited!

Ruth,

Let me see if I understand you correctly:

You have a table with items. Something like this:

tblItem{Item [Text], BasePrice [Currency], other fields . . . }
Item BasePrice
Chair 40
Bookcase 100
Lawnmower 79.99

You also have a table with discounts. Something like this:
tblDiscount{DiscCode [Text], DiscAmount [Currency]}
DiscCode DiscAmount
Del15 50
Del3 5
Del5 10

You desire a query with results like this:
Item BasePrice PriceFor3 PriceFor5 PriceFor15
Bookcase 100 95 90 50
Chair 40 35 30 -10
Lawnmower 79.99 74.99 69.99 29.99

Well. If that is the case, here is A solution. I don't know if it a
particularly good one (and it certianly is a nightmare to maintain).

SELECT i.Item, i.BasePrice, i.BasePrice - sum(iif(d.DiscCode = "Del3",
d.DiscAmount, 0)) as PriceFor3, i.BasePrice - sum(iif(d.DiscCode =
"Del5", d.DiscAmount, 0)) as PriceFor5, i.BasePrice -
sum(iif(d.DiscCode = "Del15", d.DiscAmount, 0)) as PriceFor15
FROM tblDiscount as d, tblItem as i
group by item, baseprice


The IIF statement will create a column for each discount code (you
will need to specify them all manually) and the Sum/Group By will
collapse the query into distinct items.

-Kris

 

[Guest]

Thanks Kris, you do understand me correctly in what i am trying to do!

However I do not understand your solution!

I've gone into a new query, and don't really know what I should do next. I
started with putting in columns for Item, Base Price, then I guess I want an
expression with the IIF statement in for the third column? I'm relatively
new to Access so am not that familiar with complex items! Step by step
instructions would be gratefully received!

I have two tables.
One is a list of products and "base" prices.
The other is a list of allowances which are deducted off the list price
depending on how much product is delivered.
The tables are unrelated as there are no common fields.
How do I make something from the two which shows the following:-

Price for 3t delivered Price for 5t delivered
etc.
Product X
Product Y
Product Z

I've played with queries and crosstab queries and can't just get at what I
want. Any ideas gratefully received, preferably as simple as possible as my
technical knowledge of Access is limited!

Ruth,

Let me see if I understand you correctly:

You have a table with items. Something like this:

tblItem{Item [Text], BasePrice [Currency], other fields . . . }
Item BasePrice
Chair 40
Bookcase 100
Lawnmower 79.99

You also have a table with discounts. Something like this:
tblDiscount{DiscCode [Text], DiscAmount [Currency]}
DiscCode DiscAmount
Del15 50
Del3 5
Del5 10

You desire a query with results like this:
Item BasePrice PriceFor3 PriceFor5 PriceFor15
Bookcase 100 95 90 50
Chair 40 35 30 -10
Lawnmower 79.99 74.99 69.99 29.99

Well. If that is the case, here is A solution. I don't know if it a
particularly good one (and it certianly is a nightmare to maintain).

SELECT i.Item, i.BasePrice, i.BasePrice - sum(iif(d.DiscCode = "Del3",
d.DiscAmount, 0)) as PriceFor3, i.BasePrice - sum(iif(d.DiscCode =
"Del5", d.DiscAmount, 0)) as PriceFor5, i.BasePrice -
sum(iif(d.DiscCode = "Del15", d.DiscAmount, 0)) as PriceFor15
FROM tblDiscount as d, tblItem as i
group by item, baseprice


The IIF statement will create a column for each discount code (you
will need to specify them all manually) and the Sum/Group By will
collapse the query into distinct items.

-Kris

 

[krissco]

However I do not understand your solution!

I've gone into a new query, and don't really know what I should do next. I
started with putting in columns for Item, Base Price, then I guess I want an
expression with the IIF statement in for the third column? I'm relatively
new to Access so am not that familiar with complex items! Step by step
instructions would be gratefully received!

Right. Ok.

You are used to building queries graphically. When designing a query,
click View->SQL View. Take the query that was in my last post and
modify it to work with your tables.

You will need to change the table names (e.g. tblDiscount), field
names (e.g. BasePrice), and the comparison part of the IIF statments
(e.g. = "Del3").

Try that. If you get stumped, please post the table names, the field
names, and the different discount codes you use and I will try to help
w/ you query.

-Kris