Problems with the count function on a form

[drisconsult]

I have a query with 2 fields.

The first is called:TypeOfMedia: Left([MediaID],1)
Grouped BY
Result
C
D
G

The second is called: CountOfProducts: Count(*)
Expression
Result:
1
15
1

This query has counted the number of CDs, DVDs and Games entered. The first letter of the MediaID fields indicates what type of product it is. A typical DVD entry would be: D2387

I now need to implement the totals on a form with other fields such as the name of the DVD.

The other fields are:

Ptitle: text
Type: text
Cost: currency
Penalty: currency

The trouble I am having is to successfully implement a count procedure where I can have three fields each showing how many of the various products there are, open at the same time.

Would any of my esteemed colleagues out there have a solution to this problem?

Regards
Terence

 

[aqif]

Hi,

I have not understood ur problem properly but do you know how to use DCount. You can try to use dcount or please try to explain a bit more what u want.

 

[drisconsult]

I am trying to place three fields on a form that will give me a running total of the three type of product, namely:

D3456
C6457
G2345

Where D stands for DVD; C stands for CD and G stands for Game. I can get the correct count using only the two fields I have mentioned before, but when I add other fields I am unable to show a count of the three individual totals.

 

[aqif]

Hi

Can ypu paste your formula/expression that you are using at the moment.

 

[drisconsult]

Hello aqif

Very kind of you to take the trouble to sort me out. At the moment I have a query with ther following two fields. The first field is:

TypeOfMedia: Left([MediaID],1)
Groupd By

The second field is:
CountOfmediaid: Count(*)
Expression

This works OK and give me the following result:

C 24
D 156
G 32

Where C equals the number of CDs
Where D equals the number of DVDs
Where G equals the number of games

I now wish to include these result in a form that displays the type of product eg. Film, Music or Game. This means adding four more fields. The moment I do this, the count of all the products becomes 1 for each product.

How do I count all the DVDs, Music Videos amd Games on the form.It would look like this:

MediaID: D3426

Title: Aviator

Type: Biography

Rental Cost: Khs 300 (this is Kenya currency)

Number of DVDs: XXXXX
Number of Music Videos: XXXXX
Number of Games: XXXXX

Does this help?

Regards
Terence

 

[aqif]

Dear Terrnece

You wrote:
----------------------------------------------
This works OK and give me the following result:

C 24
D 156
G 32
-----------------------------------------------

The way I understand is that you just want to display above results on the form. If that's so then first save this query as something like "QryProductCounts" and then just create 3 text boxes and write following expressions

1. =DLookup("CountOfMediaID","QryProductCounts","TypeOfMedia Like 'C'")
2. =DLookup("CountOfMediaID","QryProductCounts","TypeOfMedia Like 'D'")
3. =DLookup("CountOfMediaID","QryProductCounts","TypeOfMedia Like 'G'")

Does that answer your question?

In the Dlookup expression you can add AND criteria as well e.g

=DLookup("CountOfMediaID","QryProductCounts","TypeOfMedia Like 'C' And....") etc

See more help for Domain aggregate functions like DLookup, Dcount, DMax, DMin etc...

Regards
Aqif

 

[drisconsult]

Hello Aqif

I have included a jpeg attachment of the form that includes your solution. This is from a previous attempt and doesn't include all the products.

Regards
Terence