Calculating the proper price

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working in Access 97. I have the following data in a table:

Entry Each Price Day Price
1 10 35
1 10 35
1 15 40
1 15 40
2 10 35
2 15 40

The prices for the tickets for Entry #1 would be:

$50 if based on the each price or $40(for all tickets...I want to compare
the each price vs the max day price) if based on the day price.

The prices for the tickets for Entry #2 would be:

$25 if based on the each price or $40(for all tickets...I want to compare
the each prices vs the max day price) if based on the day price.

With the above in mind I want to come up with a query which will determine
the best pricing structure for the entry and ouput the following...

Entry Qty. Description Day Price
1 1 day 40
2 1 each 10
2 1 each 15

Any help would be greatly appreciated.
 
I think this will require several steps:

1. Create a summation query, grouped by Entry number, that shows the
sum of the Each price, and the max of the Day price.

2.Create a new query based on #1 that shows the records where the
Maximum Day price is larger than the sum of Each price.

3. Create a third query, based on the original table, where the Entry
number is NOT mentioned in query #2. SQL is
SELECT * from TableName where Entry NOT IN (SELECT Entry from Query#2)

4. UNION the results of query #2 and #3
SELECT * from Query#2 UNION SELECT * from Query#3

You may also want to create "static" fields in Query#2 and #3 to
supply the descriptions "Day" and "Each" respectively, as well as a
field for the static value (???) of "1" that you showed in your
example. Alternatively, you might want to include a Count field in
Query#1 that shows the actual count of items at the Day price.

Be aware of the fieldname aliases that Access uses when you create a
summation query. You may want to supply your own aliases.

Once the queries are created, you will open just Query#4, with the
others working in the background as "support". This query will be
read-only.

HTH




I am working in Access 97. I have the following data in a table:

Entry Each Price Day Price
1 10 35
1 10 35
1 15 40
1 15 40
2 10 35
2 15 40

The prices for the tickets for Entry #1 would be:

$50 if based on the each price or $40(for all tickets...I want to compare
the each price vs the max day price) if based on the day price.

The prices for the tickets for Entry #2 would be:

$25 if based on the each price or $40(for all tickets...I want to compare
the each prices vs the max day price) if based on the day price.

With the above in mind I want to come up with a query which will determine
the best pricing structure for the entry and ouput the following...

Entry Qty. Description Day Price
1 1 day 40
2 1 each 10
2 1 each 15

Any help would be greatly appreciated.
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
You can probably do it using something like the following UNTESTED SQL statement.

SELECT Entry, [Each Price] as ThePrice, "Each" As TicketType
FROM YourTable As T
WHERE T.Entry In
(SELECT Y.Entry
FROM YourTable As Y
GROUP BY Y.Entry
HAVING Sum([Each Price]) < Max([Day Price]))
UNION ALL
SELECT Entry, [Day Price], "Day"
FROM YourTable as T
WHERE T.Entry IN
(SELECT Y.Entry
FROM YourTable As Y
GROUP BY Y.Entry
HAVING Sum([Each Price]) >= Max([Day Price]))
 
Hi,
Try this out, substituting *your* table and field names:

SELECT Price.Entry, IIF(SUM(Price.EachPrice) > MAX(Price.DayPrice),MAX(Price.DayPrice),NULL) AS SumEachPrice
FROM Price
GROUP BY Entry
HAVING IIF(SUM(Price.EachPrice) > MAX(Price.DayPrice),MAX(Price.DayPrice),NULL) IS NOT NULL
UNION
SELECT Price.Entry, Price.EachPrice
FROM Price WHERE Price.Entry NOT IN (SELECT Price.Entry
FROM Price
GROUP BY Entry
HAVING IIF(SUM(Price.EachPrice) > MAX(Price.DayPrice),MAX(Price.DayPrice),NULL) IS NOT NULL);
 
Back
Top