Counting Unique UID

Aug 16, 2006

Hi,

I have been trying for the last couple of week or so to get a query working.

Basically the query is used to show Hours worked by Post Code. Everything works fine and the query returns 'Hours Worked By Postcode' and Number of Records that the data was created from. (See Report in Attached DB)

However I have now been asked also to show the number of unique Members who by PCode make up the records.

So the report would look like:
By Post Code (See Report)
Number Of Members - Number Of Records - TotalTime

I am having problems returning the number of Unique Members who make up the data, in the query you will see Test and Test1 where I have tried to implement a unique count with no success.

Any help would be appreciated.

Thanks

Daz......

View Replies


ADVERTISEMENT

Counting Unique ID's Without Duplicates

Jun 29, 2006

Hi,

I have a problem with getting the right result with a query.

To this topic I attached a part of my database including 2 queries. The queries are almost the same, except the first field. In query 1 is the total 'Group By' and in query 2 'Count'.The other fields are parameters, which are the same in both queries.

My problem:
If I run query 1 then the result is 31 rows with unique ID's. When I run query 2 the result is 35, because the query counts 4 duplicates.

The correct result I want is 31. That means count the unique ID's. I am able to show the ID's with query 1, but I am not able to count them correctly. I tried to add 'DISTINCT' in SQL view but that didn't help.
Also other solutions written in this forum didn't work.

Thank you if someone (with more query/SQL experience) is able to help me... :o

View 2 Replies View Related

Counting Unique Records In A Query

Jun 1, 2006

I'm having trouble getting a query to return a simple count of unique lot numbers for a given ProductID. The data is stored in a large table where each test result of a stability program is stored. Each result has an associated lot number, product id and several other data fields. I've managed to get a combination querries to return the count, but if the lot has both real time and accelerated data then the counts are added and reported as double for each type. The current SQL is as follows.

SELECT tblProducts.ProdName, Count(qryAccelerated.Lot) AS AccelCount, Count(qryRealTime.Lot) AS RTCount
FROM qryRealTime RIGHT JOIN (qryAccelerated RIGHT JOIN tblProducts ON qryAccelerated.ProductID=tblProducts.ProdID) ON qryRealTime.ProductID=tblProducts.ProdID
GROUP BY tblProducts.ProdName
ORDER BY Count(qryAccelerated.Lot) DESC , Count(qryRealTime.Lot) DESC;

qryAccelerated and qryRealTime are simple SELECT DISTINCT querries returning the product id and a list of unique lot numbers for that ID.

(e.g. SELECT DISTINCT tblResults.ProductID, tblResults.Lot FROM tblResults WHERE (((tblResults.TypeID)=3));)

Currently the top query returns 4 in the both the AccelCount and RTCount columns when there are only 2 unique lots for the product. Other products without both real time and accelerated lots count correctly.

Any help is greatly appreciated. Thanks.

View 4 Replies View Related

Reports :: Counting Unique Field In A Report

Jul 19, 2014

I have a report which is based on a query. The query combines information from TBLDwgReg and TBLDwgRegDtls. The unique field linking these two tables is DrawingNo. The query - QRYDtldDwgReg - is showing 99% of the information I want to see.

My problem is this - one drawing can be revised several times. I have my report grouped by IssuedBy and then grouped by DrawingNo. In the group footer for IssuedBy I want to count the number of drawings issued by a particular consultant. For example...the structural engineer has issued 17 drawings for a particular project but when I do a count it is returning a total 27 because some of those drawings have been revised.

I tried grouping in the query and counting the DrawingNo field there but that's not working either.

View 14 Replies View Related

Counting Of Cases And Counting No Records

Jan 26, 2007

I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.

View 1 Replies View Related

Queries :: Find A Way For Access To Find Unique Dates And Unique Names?

Aug 1, 2014

I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:

Start Date/Time
End Date/Time
Employee

At the moment this is what the format of my report looks like (I removed other unnecessary fields):

StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe

I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?

View 2 Replies View Related

Unique ID

Jul 14, 2005

I am just about to start a new DB, but there are a few things I need to sort before i start.

I am working with a quotation system etc which will require the use of unique IDs...

eg 000123 then 000124 etc, this bit is not a problem.
The thing I would like to know how to do is when someone makes an order, I would like the quotation number to be changed slightly to add more detail.
For eg, If the quotation number that is allocated is 00123 and they then order a Conservatory, I would need to have this 00123 become 00123CNS, but this can be in a seperate field, Which I would need anyway.

The question is, How Would I go about this.
I have a table that describrives the product, TBL_PRODUCTS; ID, Product_Type, Product_ABBR

Product_Type = for e.g Conservatory
Product_ABBR = for e.g CNS

I would need the ref number and product abbr combined to create an order number.
[note] There will be a check box to say of ordered ot not, so I assume there will be an If statement somewhere?

View 2 Replies View Related

Counting

May 13, 2005

i am trying to count the number of records based in a query

can some one send me in the right direction

View 2 Replies View Related

Counting

Feb 8, 2006

I have a database where there are numerous fields but they all only have one three values Y, N, N/A.

how do i get something similar to Excels "countif" function to summarise the totals of Y's N's N/A's inach field ?

Thanks

View 1 Replies View Related

Counting

Feb 8, 2006

I have a database where there are numerous fields but they all only have one three values Y, N, N/A.

how do i get something similar to Excels "countif" function to summarise the totals of Y's N's N/A's in each field ?

Thanks

View 4 Replies View Related

Counting

Jan 26, 2007

How can I count the number of times an employee shows up on a report. The final result would be:

Employee 1: Reader1
Employee 1: Reader2
Employee 1: Reader3
Employee 1: Reader4

If an employee shows on a report 4 times, I need it to look like the example above in sequential order, not just a total.

Thanks for your help.

View 1 Replies View Related

Counting Down!

Jan 10, 2006

Hi

I have a form which displays which products are on sale but I want it to count stcok orders down, i.e. if there are 10 stock and someone orders 1 I want the system to automatically recognise that there are only now 9 in stock, any ideas? Help really would be appreciated!

Thanks

B

View 4 Replies View Related

Counting

Apr 18, 2007

Hello,

I have a form called feedback which has three columns and each cell in a column has a combo box that holds (Yes/No/Empty), and I want to count each column's value for example let's supposed that:


feeback number: Column 1 (Are You happy) - Column 2 (Are you sad) - Column 3 (Are you hungry)
1 : Yes - No - Yes
2 : Yes - Yes - Yes
3 : No - No - Yes
4 : empty - No - yes

Counting: the next step is that I want to count:

values in Column 1, where number of Yes =2 , and number of No=1 the stum of Yes + No = 3 and empty = 1
values in Column 2, where number of Yes =1 , and number of No=3 the stum of Yes + No = 4 and empty = 0
values in Column 3, where number of Yes =4 , and number of No=0 the stum of Yes + No = 4 and empty = 0


So, how can I apply this concept in MS ACCESS (2000-2003).

I am waiting for you kindly reply.

View 1 Replies View Related

'Unique' Query

Sep 24, 2005

I've been on other forums with this problem but no one can solve my problem.

I've created a database with 3 tables that are linked:

- tbl_customer
- tbl_rates
- tbl_destination

They have the following fields:

tbl_customer
- cust_id
- customer_code
- first_name
- last_name
- company

tbl_rates
- rates_id
- cust_id
- rates_id
- currency
- pre_rate
- cur_rate
- fut_rate
- comment
- eff_from

tbl_destination
- dest_id
- destination
- destination_code

What I've done is created a form based on the tbl_customer table and used tbl_rates as a sub form. tbl_destination is used to populate a combo box that is situated in the sub form.

Each customer has their own rate sheet. Each rate sheet has different destinations and rates. Rates for the same destination are constantly changing. I need to use this spreadsheet to record the history of every change made to the rates of a particular destination. To do this I've just added the same destination with a new rate.

Now, what I want to do is to create a query that will only show the latest entry of a particular entry.

Eg. tbl_rate

Rate_id - Dest_id - currency - pre_rate - cur_rate - comment - eff_from

1 - 1 - £ - 0.01 - 0.03 - increase - 19 Sept 05
2 - 2 - £ - 0.12 - 0.14 - increase - 19 Sept 05
3 - 1 - £ - 0.03 - 0.02 - decrease - 23 Sept 05

I would want the query to show me the following from the above table:

Rate_id - Dest_id - currency - pre_rate - cur_rate - comment - eff_from

2 - 2 - £ - 0.12 - 0.14 - increase - 19 Sept 05
3 - 1 - £ - 0.03 - 0.02 - decrease - 23 Sept 05

Basically, the query should only show the latest entry which involves Dest_id '1' because there were two entries with the same id.

I've been given advice about this issue but none that actually work.

I would be grateful for any help on this matter.

View 1 Replies View Related

Generating A Unique ID

Nov 2, 2004

So I've read a good 100 postings or so so far on the subject of how generating a unique identifier through any other means than an autonumber can be dangerous. But I can't resist the opportunity to simply ask if there is a way to make this possible, if only through a calculated field...

In my single user database, I have an employment table with an autonumber [EmploymentID] field as its primary key. The employment table exists in a one-to-many relationship with it's child, the income table where each form of income is recorded as it is recieved so that one form of employment (or employer) can produce many unique forms of income (like weekly paycheques).

I would like to be able to generate an identifier for the income table that is a combination of the parent [EmploymentID] field and an increasing number to produce something that looks like this:

1006789-001 -|
1006789-002 -|--> Same EmploymentID
1006789-003 -|
1006790-001 ----> New EmploymentID with a reset numeric

Any ideas?
~Chad

View 4 Replies View Related

Help With My Database - Unique Id

Nov 12, 2004

Hello Everyone,

I've data already entered (access/tables) on two different computers, however for the clientid on one computer has a hypen (-) and on another - because of something they couldn't get the hypen to work - so they used the slash (/) my Q is: 1) can I search and convert these to one, 2) is it wise the have / or - in the id?

View 6 Replies View Related

Creating An Unique ID

May 20, 2005

I am very new to db programing so please forgive for such a simple program.

Here is the deal. I have a db for about 500 students.

I would like the table [Students] to create a unique ID "number" for each student. I would like the number to be based on information from the same table. For example if the student's name is John Doe and his number is 555-1212 then I would like to the id to be JD1212. I plan on using more than name and phone number but this was just to show you, what i am trying to get done.

I can do this using a form and a calculated control, but I can't get the table and form to kiss and make up. I was also told you can't save data from a calculated control in a form to a table and it is gernerally not a good idea, so I was told.

Any help would be appreciated.

Sincerely,

Pastor Nick.

View 3 Replies View Related

Unique Index

Jul 6, 2005

I have a beginner Question....How can I set an unique Index on a field in a table if all the four fields are using duplicate values?

View 3 Replies View Related

Unique Combination

Jul 31, 2007

I have thought about this for days now (and nights) and I have simplified my problem below. (I even bought a book)
I have three tables: 1, 2 and 3.
Table 1 = a, b and c
Table 2 = x, y and z
Table 3 combines table 1 and table 2; ax, ay, az, bx, by, bz, etc.
Table 1 and Table 2 are both One to Many with Table 3.
The Table 1 field and Table 2 field are a ‘unique’ index combination.
My Problem:
It would be very handy to produce a list of the ‘unique’ index combinations that are not in Table 3. That is, the ‘unique’ index combinations that aren’t used yet.
You could do this by trial and error, but is there a Query you can run that will give you a result that you could then Append?
An Unmatched Query on the Table 3 / Table 1 contents will point out a complete lack of either a, b or c as will the same Query on Table 2 for missing x, y or z.
A query with the two non-linked tables will give every combination, but is this the best way to do it? It works by default when you use it to append table 3, but is it the best way to do it?

View 14 Replies View Related

How To Set Up A Unique Number?

Sep 30, 2007

Hi There,

I am setting up a basic Data Licence database. For each new record I enter I want a unique number to appear at the top of my form that can not be changed. I would like the number to be the date it is entered plus the auto number so, YYMMDD-(auto number) eg 071001-1. I'm sure it's something simple but I am only new to access.

Also while I'm here, on my form I have a Product field, how can I make it so I can enter in more then one product?

I would really appreciate any help!

Thank you,
Dane

View 8 Replies View Related

Unique Field

Apr 14, 2008

Is there a way to format the random auto number to just be 4 digits long? Or does anyone know how I can programatically create an auto unique field? This number has to be stamped on a key so it can't be longer than 4 characters but it has to be unique. Any help would be greatly appreciated.

View 8 Replies View Related

Unique ID Field

Apr 18, 2008

Hi all
I have been discussing DB table design with a colleague and we have a difference of opinion with regards to primary key ID fields .

My argument is that they should not be actual data but separate, that is an unique record identifier typically an autonumber type. I'm sure I picked this up from reading Access manuals.

My colleague's argument is: if the data contains a unique field, e.g. a unique project number in a table of projects (no project should be listed twice) then use the project number as the primary key. Her second argument is the byte size of an autonumber is 8 bytes whereas you can use a smaller data type to hold the project number thus saving space.

My opinion is that her first reason is flawed as it assumes no change to how projects are identified and that her second argument is irrelevant in today's Gb and Tb systems.

Without trawling through my Access books and the internet for opinions, I would like to know what your opinions are on this subject. I would appreciate good reasons which ever side of the fence you are on. I'm quite willing to be proved wrong and change my ways.
Cheers
Imperator

View 12 Replies View Related

Unique Record

Aug 3, 2006

I have a query which displays two fields: client ID and order type. I want to be able to display the client only once per order type. I tried to use GROUP BY order type but that gives me a missing expression message. I also thought of using UNIQUE as YES in qry properties but I'm not sure as to which row that property applies (all of them?). What is the best way to display a unique client in one row and order type in the other? So for example for order type "CASH" I wil then have a list of unique individualIDs.

thanks,
M

View 3 Replies View Related

Unique Values

Mar 1, 2007

I have a query that has an Item, ItemPrice, Allocation, and DatePurchased ordered by DatePurchased with only unique values.

I'm using it for a PO Database, when entering items purchased, I have the Item field populated with a items previously purchased, when the item is selected the Unit Price and Allocation Fields are autofilled in with the most recent values from the PO details table.

My problem is if I purchase 10 of the same Items on 10 different dates then the combo box will show 10 of the same Items, because they are not unique values because of the DatePurchased.

What I want is a query to show one unique Item entry with the most recent Purchase Price. I can get a list of unique Items, by only making a query of those and showing only unique values, but as soon as the date is introduced it no longer works, for obvious reasons.

Any help appreciated,
JOe K.

View 1 Replies View Related

Count Unique (yep, Again)

Sep 20, 2007

Hello,

I noticed my subject has been discussed numerous times in this forum. I must have read nearly all topics, but I simply can't get it to work.

I have a table listing: country | category | article | turnover

I wish to make a query which gives me:

country | sum of turnover | count of (unique) categories used by this country | count of (unique) articles sold by this country.

This way I can evaluate with how many different categories a certain country achieves a certain turnover...

It look so simple but I can't figure it out. I hope I'm clear in my question.

HEEELP!

Cheers,
Stefan

View 2 Replies View Related

Unique Values ??

Jan 15, 2008

I have a database to track client activity per session.
Each client has a unique client number but multiple activities.
Therefore, if I have 20 clients in the database and each has 3 activities, I have 60 client numbers, 3 *20 = 60. ( Duplicated)
My elementary problem is how do I display the client activity unduplicated so that my query returns only 1 unique client number per session.
I tried the unique records/values in the property box but still.....
Thanks for your help.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved