Counting Unique ID's within sub form.

Ade F

Avid Listener
Local time
Today, 13:54
Joined
Jun 12, 2003
Messages
97
Counting same IDS

Here is a question for all you query experts,

Just say I have two tables

Job number ID (Pri Key)
Job Description etc

Customer ID (Pri Key) (IS a sub of job number continues form)
Name etc

How would I count how many instances of customer id's occur under the specific job number?

Basically I would like for my report to show how many customers have been logged to the specific job number. At the moment I have a unique ID box showing the customer id.

Yes I may be being a little vague but I'm sure you know what I mean.

A job number can have many customers. The customer has a unique ID but I do not wish to know the unique ID but how many unique ids starting from 1 there are within that specific job.

Any ideas?
 
When you are counting things in the "many" side table, you may have to use two queries. The first groups the "many" rows and the second counts the grouped rows.
 
Thanks Pat

Cheers Pat but I wondered if there was a little example you or someone else could post. Queries and expressions are certainly not my strong point.

Many thanks

Adrian
 
Hi,

I don't know how you save the information about which customers a specific job has, there could be the third table in your case, which has two primary keys, job number ID and customer ID. Pls check the sample I posted.
 

Attachments

Thanks but...

Yes its something what I would like here is what I'm after. I may have over simplified the example

I have the tables

JobID

--

Contract creation sheetID (sub of JobID)

--

Contract update sheetID (can be many within contract creaation sheet).

When I create a contract creation sheet a Contract update sheetID is created. within the sub of the Contract creation sheetID. There can be many jobs and only one contract creation sheet.

I would like to count how many Contract update sheetID's have been linked to a specific JobID.

Thus within my contract update sheet report I would like to have

JobID / Number of Contract update sheetID's

At the moment I just have

JobID / Contract update sheetID

Yes the above gives me a unique ref. to the doc. but It does not look nice to staff using and printing the documents.

Have I over complicated things now?

Cheers

Adrian
 
Hi,

I am not sure the design of your tables, is "JobID" a field (OR ONE OF PRI KEYS) in the table "Contract creation sheetID", or in the table "Contract update sheetID"? what are the relations between these tables?

About "staff using and printing the documents", do you need a report about the account? I hope can do something to help you.
 
When creating a project for a job

You can only have one job ID (sequencial pri key) say (1781)
You can only have one Contract creation sheetID say (12) (sequencial pri key) within (1781)

You can have many Contract update sheetID say (7) within (12)

So basically instead of the >report reading

1781 / 7

If their were 4 contract update sheets created I would like

1781 / 4 (4 being the count of how many contract update sheets have been made within the contract creation sheet)

Clear as mud or a little better?

Regards

Ade
 
Hi,

I tried to creat another sample according to your explanation, it is similar as the earlier one. actually it not a good sample, Because I don't know what real case you faced, there are a few design flaws.

you did not answer the questions I asked. if you are able to post or email a sample, I think it may be better.

regards
 

Attachments

Thanks Jason

That was enough to enlighten my cabbage of a brain as to how’s of counting in queries.

Many thanks I can now apply it in the real world.

Cheers

Ade
 

Users who are viewing this thread

Back
Top Bottom