Find number of records in a subset of large table

[Abinadi8]

I have been trying to create a temp Table of the subset desired; however, I find this does not work well if there are no records. then I get an error that I trap and report back as to what, when and where this happens. I would rather create a specified # records if the COUNT is 0.
I have been told that this possible within a query, is this true and if so could someone email me a quick example of how this is done. klnield@attbi.com
Email Subject should be: Access Query example as I tend to delete emails from people I don't recognize.

 

[JeremyNYC]

Abinadi8,

As I said in the other identical post you put up about this, this is a public group. We work together to answer questions so that everyone will learn. You cannot expect people to send e-mail to you. And to ask someone to use a particular subject is pretty close to rude.

You can do what you want fairly easily. You don't want to use make-table queries, as they will cause lots of file bloat. Look in help under totals queries and you'll find what you need.

Jeremy
=============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.
=============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Abinadi8]

I am new to this Tek-Tips area, I apologize for my rudeness, if rude it was. Even though I have studied the help under totals queries, I am still recieving the message "can't group on fields selected with '*'.

I am afraid that this reply has been no help at all.

 

[JeremyNYC]

Did you add all of the fields from your table to your query using the * at the top of the table? I've never seen that error before, but it looks like that is causing the problem (though I can't be sure). In general, it's best to only add the fields you need to a query. Try doing this with just one field in the query, one you know will have data for every record.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Abinadi8]

I am already doing this: lngEstimateID is the only field that I have the Count function onand am showing. On the stForm, intPage1, intPage2 and lngCustomerID I am using the where function and I am not showing these fields. Sql comes out as follows:
SELECT Count(Estimates.lngEstimateID) AS CountOflngEstimateID, *
FROM Estimates
WHERE ((([Estimates]![stForm])=[Forms]![Tile].[Name]) AND (([Estimates]![intPage1])=[Forms]![Tile]![Entry_way].[PageIndex] Or ([Estimates]![intPage1])=[Forms]![Tile]![Kitchen].[PageIndex] Or ([Estimates]![intPage1])=[Forms]![Tile]![Pantry].[PageIndex] Or ([Estimates]![intPage1])=[Forms]![Tile]![Fireplace].[PageIndex] Or ([Estimates]![intPage1])=[Forms]![Tile]![Master_Bath].[PageIndex] Or ([Estimates]![intPage1])=[Forms]![Tile]![Main_Bath].[PageIndex] Or ([Estimates]![intPage1])=[Forms]![Tile]![Powder_Bath].[PageIndex] Or ([Estimates]![intPage1])=[Forms]![Tile]![Utility_Room].[PageIndex]) AND (([Estimates]![intPage2])=[Forms]![Tile]![Floor].[PageIndex] Or ([Estimates]![intPage2])=[Forms]![Tile]![Tub].[PageIndex] Or ([Estimates]![intPage2])=[Forms]![Tile]![Counter].[PageIndex] Or ([Estimates]![intPage2])=[Forms]![Tile]![Vanity].[PageIndex] Or ([Estimates]![intPage2])=[Forms]![Tile]![Shower].[PageIndex] Or ([Estimates]![intPage2])=[Forms]![Tile]![Wainscot].[PageIndex]) AND ((Estimates.lngCustomerID)=[Forms]![Customers]![lngCustomerID]));

Where does the * come from?

This is why I would like someone to share an example of such a Totals query that works.

 

[JeremyNYC]

delete the ", *" and try again. =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Independent Access consultant.

If a post is helpful, give it a star.