Shop values form based on a query

[Phudsen]

Hi all,

I have a table called MovieMaster for videos and shops, example:

Some of the fields are:
CassetteID
Type
Shop1 (This field will hold quantity of shop1)
Pricy (Price per Unit)

CassetteID Type Shop1 Price
----------- ---- ----- -----
1 DVD 23 20
2 DVD 12 20
3 VCD 08 10
4 Video 09 05
5 Video 07 05
6 VCD 10 10
7 DVD 08 20
8 Video 22 05


How to make a form based on a query to display:

Type Qty Total
----------------------
DVD 43 860
VCD 18 180
Video 38 190
----------------------
Total Value in
Shop1 1230


It is easy to find how many of each type in a shop
but the problem is how to calculate the total then
the value of the items in a shop.

Thanks a lot
Paulin

 

[TheAceMan1]

How are ya Phudsen . . . .

Not the greatest amount to go on here . . .

Is [blue]CassetteID[/blue] the PK?

Is there more than 1 Shop (Shop1, Shop2, . . . ect)?

Perhaps you can post all the fields? Certainly appears as if this table is not normalized.

The following is a sample SQL statement. Open a query in design view. Select SQL view, paste the SQL and run it:

[blue]SELECT MovieMaster.Type, Count(MovieMaster.Shop1) AS CountOfShop1, Sum(MovieMaster.Price) AS SumOfPrice
FROM MovieMaster
GROUP BY MovieMaster.Type;[/blue]

See Ya! . . . . . .

 

[Phudsen]

Hi TheAceMan1,

I am fine AceMan, thank you.

To answer your questions:

1- CassetteID is the PK
2- Yes he has 4 shop. Each shop is a field in the table like Shop1, Shop2, Shop3,Shop4. Each has only the quantity of the movie. I thought of making 4 queries for that? Can it be in one?

In your SQL, you are counting Shop1. Shop one has quantities, isn't it better to use Sum. Example:

CassetteID Shop1 Type
E5000 3 Video
E4000 5 DVD
E3800 1 Video
E5000 5 DVD

If we count we will have
Shop1
2 Video
2 DVD

But if we sum we will have
4 Video
10 DVD

He want to find how many Videos (copies) are ther. When we say E3800 is one ID but has 3 Videos.

We might have Video type as records 1700, but we might have 3900 video tapes in those records.

So, what do you think?

Thanks AceMan for your help.

Paulin

 

[PHV]

Something like this ?
[tt]SELECT Type,Sum(Shop1) As Qty,Sum(Price*Shop1) As Total
FROM MovieMaster
GROUP BY Type;[/tt]
For the 4 shops together you can try this:
[tt]SELECT Type,Sum(Shop1) As Qty1,Sum(Price*Shop1) As Total1
,Sum(Shop2) As Qty2,Sum(Price*Shop2) As Total2
,Sum(Shop3) As Qty3,Sum(Price*Shop3) As Total3
,Sum(Shop4) As Qty4,Sum(Price*Shop4) As Total4
,Sum(Shop1+Shop2+Shop3+Shop4) As Qty
,Sum(Price*(Shop1+Shop2+Shop3+Shop4)) As Total
FROM MovieMaster
GROUP BY Type;[/tt]
You may consider the NZ function if some ShopX fields are null.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Phudsen]

Hi,

Thank you AceMan
Thank you PHV

The solutions are working perfectly.

Thanks
Paulin