counts and averages (2 questions)

[maddles]

I'm having trouble with a couple of queries. This database is for an art gallery which records exhibits.

The first query is to search for a specific amount of art (eg 3 or more) done with a particular media (eg charcoal).

The query is made up of the following:

Field: Name
Table: tblArtists
Total: Group By
Criteria:

Field: Media
Table: tblArtworks
Total: Group By
Criteria: [Media:]

Field: ArtworkID
Table: tblArtworks
Total: Count
Criteria: >=3

Now, this works perfectly in retrieving 3+ works made from whatever. My problem is that I can't figure out what to write in that last Criteria field so that, instead of the value being set at 3+, the user can enter the number they want to find instead. I'm sure it's simple but I can't seem to get it working!

The other query is to be able to find the average amount of work exhibited by a certain artist (out of all the listed exhibitions.)

The only table I'm using is this one

tblExhibitionstoArt
ExhibitionID
ArtworkID

(the other tables are pretty standard, eg Artist: ID & Name, as for tblArtworks and tblExhibitions.)

The Artwork ID is partly made up of the artist name, (for instance, Claude Monet = Mocla.) If I want to know how many Monet's have been shown, then when I enter the ID Mocla,
I need it to return the total amount of work listed for exhibition (say 25), and then out of that 25, find the total number of works with 'Mocla' in the ID and calculate the average through those two numbers.

I have no idea how to even begin this! help is greatly appreciated. thanks a bunch.

 

[jmersing]

Let's tackle your first problem. Anytime you want a user to enter in criteria that they are prompted for you use brackets []. What ever you type in the brackets will be the question or statement displayed to the user in the input box.
i.e.

Field: ArtworkID
Table: tblArtworks
Total: Count
Criteria: >=3

select artworkID, count(artworkid) as Cnt
from tblArtworks
where Cnt >= [Type the minimumn number of results you would like to view]
group by arrtworkid

 

[maddles]

typically, I was doing it the other way around. thanks!

please tell me someone can do the 2nd one, not to sound whiney, but I really do need it! I've got them to total, but not average blast....