Counting a number of entries in a query

[CelevGadol]

Hi everyone

Once again I find myself stuck with what is probably a very easy problem for you guys.

I have a table of jobs that I have done. These jobs are done for various other companies. Each job is done for a particular price which is fixed, so I have Price1, Price2 and Price3 (for instance). I can create a query which filters for one company, provides a list of jobs done for Price1. What I would like to do is create a count of the number of jobs done at that price.

If I could do that, I think it would be easy to create a count of jobs done for Price2 and Price3 and therefore automate the invoice.

Now a second question: if I could create a count of jobs at Price1, would it be best to just add a column for Price2 and produce a count for that in the same query or would it be simpler to create a new query for each Price?

 

[Gasman]

Look at DCount()
I hope Price1, Price2, Price3 are not separate fields in one table?

 

[jdraw]

It would be helpful if you posted your table designs and any query SQL you have tried.

 

[bastanu]

Create separate totals queries for each Price in which you Group By Price1 and Count(OrderID) or whatever your unique ID is for the table.

 

[CelevGadol]

Hi Gasman
Thanks for your response. Maybe I should have used a more algebraic method of description. I have a field called Price which contains £xxx, £yyy, £zzz according to the job that was completed. The way I am trying to create the automated invoice is to use a query which filters which company the work was for, so one query per customer, then counts the number of jobs at each price. I'm trying DCount but I don't seem to be making much progress. The arguments seem to not be doing quite what I want. i have entered something like: DCount("Price", "tblSurveys") so far but can't see where a value is returned, if a value has indeed been found.

I have a table of surveys completed (tblSurvey) which contains the Company field (from the Company table, tblCompanies), the site name and number (from the Sites table (tblSites) and the price which is entered via a form. I have dabbled tentatively with VB but if I can do this without it, that would be my preference. I'd like to keep things understandable (to me at least).

I'll try to figure it out but a pointer or two would be greatly appreciated!

 

[CelevGadol]

Create separate totals queries for each Price in which you Group By Price1 and Count(OrderID) or whatever your unique ID is for the table.

OK, thanks Bastanu. So you're saying create a Price1 query, a Price2 query etc. I thought that would be the simplest approach. The final invoice can then take the data required from each separate query in the form of a report.

 

[CelevGadol]

It would be helpful if you posted your table designs and any query SQL you have tried.

That's kind of difficult without breaching NDAs. As for SQL, I haven't really tried any. The process I have used is to Create > Query Design then filling in the table at the bottom with data from the table shown in the upper window. Apologies for the layman's description but I can't remember all the names for the different parts of the dbase!

 

[bastanu]

With your new info from post # 5 I think you need to create a query based on your tblSurvey table and group by the CompanyID and Price fields and count the unique IDs (SurveyID or whatever you use for the primary key).

SELECT tblSurvey.CompanyID, tblSurvey.Price, Count(tblSurvey.SurveyID) AS CountOfSurveyID
FROM tblSurvey
GROUP BY tblSurvey.CompanyID, tblSurvey.Price;

Cheers,

 

[The_Doc_Man]

First, if you have multiple Pricen fields, this table is almost certainly not properly normalized and thus makes you work harder than you should. If you were going to do anything at all, that is really something that should be on your list.

Second, if there is a really serious reason why you can't "fix" this design, there is always the two-layer query in which you re-form the columns using a UNION query, then you can take sums or maximum or minimum or average or counts as needed.

SELECT Price1 as Price,  <<other fields>> FROM TheTable
UNION
SELECT Price2, <<other fields>> FROM TheTable
UNION
SELECT Price3, <<other fields>> FROM TheTable ;

Then you can do aggregate functions of the union query as though all the prices were in a single column, perhaps using a GROUP BY clause that would organize the records of the UNION query into meaningful groups. Like customer ID or invoice number or something like that.

 

[bastanu]

@ The_Doc_Man: I think the OP means different rate values, not fields:

. I have a field called Price which contains £xxx, £yyy, £zzz

 

[CelevGadol]

First, if you have multiple Pricen fields, this table is almost certainly not properly normalized and thus makes you work harder than you should. If you were going to do anything at all, that is really something that should be on your list.

Second, if there is a really serious reason why you can't "fix" this design, there is always the two-layer query in which you re-form the columns using a UNION query, then you can take sums or maximum or minimum or average or counts as needed.

SELECT Price1 as Price,  <<other fields>> FROM TheTable
UNION
SELECT Price2, <<other fields>> FROM TheTable
UNION
SELECT Price3, <<other fields>> FROM TheTable ;

Then you can do aggregate functions of the union query as though all the prices were in a single column, perhaps using a GROUP BY clause that would organize the records of the UNION query into meaningful groups. Like customer ID or invoice number or something like that.

Thanks Doc.
Perhaps my explanation wasn't very clear. My table has a single price field which can contain any value. In my case, the values would be x, y or z. That is to say that each job would have its own price but several jobs would have the same price. The price for Job A might be x, Job B might be y and Job c might be z. The table would look like a list of jobs and a list of prices.

 

[CelevGadol]

I don't know if this helps to clarify my descriptions but here's a screenshot of the survey table and another short description of what values come from where:

The company field is a drop down which references a Company table.
The Site Name and Site Number are also drop downs which reference a Site table.
The Surveyor First and Last names reference an Employees table.
The project name references a Project Name table.
The project number references a Project Number table. The project names and numbers are in separate tables because firstly, one company uses the same project names but changes the project number each month. Secondly, it's possible that two or more companies can use the same project name but have their own numbering system.
The price is manually entered and can be different for each job.
I hope that all makes sense. This is the first dbase that I have designed and hopefully it follows most of the rules of good practice although I reckon it might not!

 

[bastanu]

Replace CompanyId with ID in the query I gave you in post #8 and give it a try.
Cheers,

 

[CelevGadol]

@ The_Doc_Man: I think the OP means different rate values, not fields:

Exactly right. One field, manually input values which can be any number.

 

[CJ_London]

hopefully it follows most of the rules of good practice

Just some observations - they seem like a good idea at the time, but dropdowns in table design are a bad idea. Use them on forms, not tables

The company field is a drop down which references a Company table.
The Site Name and Site Number are also drop downs which reference a Site table.
The Surveyor First and Last names reference an Employees table.
The project name references a Project Name table.
The project number references a Project Number table.

see this link about dropdowns in tables
http://access.mvps.org/access/lookupfields.htm

Further 'The Surveyor First and Last names reference an Employees table.' So you have to visit that table twice? what is to stop a user selecting the first name from one surveyor and the last name from another?

not sure I understand the logic of the second part of this statement

The project names and numbers are in separate tables because firstly, one company uses the same project names but changes the project number each month. Secondly, it's possible that two or more companies can use the same project name but have their own numbering system.

What happens if two companies use the same project name and the same numbering system? just because it hasn't happened yet doesn't mean it wont.

Finally, having a table named tblSurvey2 indicates a potential design issue. It may just be version 2 but if not.....

 

[CelevGadol]

Just some observations - they seem like a good idea at the time, but dropdowns in table design are a bad idea. Use them on forms, not tables

see this link about dropdowns in tables
http://access.mvps.org/access/lookupfields.htm

Further 'The Surveyor First and Last names reference an Employees table.' So you have to visit that table twice? what is to stop a user selecting the first name from one surveyor and the last name from another?

not sure I understand the logic of the second part of this statement

What happens if two companies use the same project name and the same numbering system? just because it hasn't happened yet doesn't mean it wont.

Finally, having a table named tblSurvey2 indicates a potential design issue. It may just be version 2 but if not.....

Thanks for your observations. There are some interesting points here.

The surveyor first and last names is a good one. There is absolutely nothing to stop a user from selecting a first name of one surveyor and a last name from another. Perhaps that needs a rethink. My original thinking was that a table should have the data broken down as much as possible so the table would contain a field for first names and last names rather than both names in one field. This, I guess, is the problem with drop downs or at least part of it.
The same thing goes for the project names and project number. The main thing is that each company requires a list of sites with the corresponding project so that they can cross charge their respective clients and perhaps analyse which projects are profitable and which are not. It doesn't matter whether the companies use the same system or not. It's just a report of which projects each site is chargeable to.

tblSurvey2 was created for the screenshot which doesn't contain any data. It's not in use in the actual database.

I'm going to look at the link that you have included now

 

[CJ_London]

seems to me you have an excel head on when thinking about the tables

my original thinking was that a table should have the data broken down as much as possible so the table would contain a field for first names and last names rather than both names in one field.

You should have a separate table for surveyors to contain as a minimum of PK, firstname and lastname

in your survey table you would just store the PK (as an FK) and on a form a single combo to select the appropriate surveyor.

you can then populate firstname/lastname in a query to be used in a report/whatever.

In excel stored data and outcomes are presented in the same 'view'. In databases, tables are for storing data, outcomes are presented in queries/forms/reports.

 

[CelevGadol]

seems to me you have an excel head on when thinking about the tables


You should have a separate table for surveyors to contain as a minimum of PK, firstname and lastname

in your survey table you would just store the PK (as an FK) and on a form a single combo to select the appropriate surveyor.

you can then populate firstname/lastname in a query to be used in a report/whatever.

In excel stored data and outcomes are presented in the same 'view'. In databases, tables are for storing data, outcomes are presented in queries/forms/reports.

Thanks for your comments/advice CJ. You're right, I do have more of an Excel head. This is my first venture into databases so I havven't developed a dbase head yet. It's quite a back-end type of dbase at the moment but as I'm the only one who uses it, it doesn't matter too much.

I read the list of evils that you posted a link to. There are a few things that it mentions that happen in my dbase like the pop-up boxes asking for an entry that can't be provided. I think I have tended not to use the PK enough (or maybe not at all) which I should rectify. When I try to filter a query by company name for instance, I have to use the PK as the criteria rather than the company name which I guess demonstrates at least one of the points in the evils list.

My dbase works for what I need it to do but I think a seasoned pro would call it clunky and any other user trying to use it would want a front end which it doesn't really have. I'll try to find some time to fix the poor design so keep in touch!

 

[CJ_London]

I'll try to find some time to fix the poor design

the sooner you fix it, the sooner you'll stop wasting time trying to find workarounds

 

[CelevGadol]

the sooner you fix it, the sooner you'll stop wasting time trying to find workarounds

I can't argue with that. I've worked with software engineers who constantly use temporary workarounds which tend to become permanent fixes which breed constant updates in the form of patches which is a pseudonym for workarounds

I have tried using Access 2019 Bible as my guide. I don't really get on with it too well to be honest. I've also tried a couple of online courses which kind of skip things and they seem to be run by people who do things differently. If you gave the same task to half a dozen people you would get half a dozen different solutions and they may all be valid. Can you or anyone else recommend something that I could learn from the ground up about databases? I feel like I'm picking up scraps and trying to piece them all together at the moment.