I could use a little help getting a mod going. I have a table that in imported from another database, the table has many duplicate records excluding one field so for example i have 5 records that have 52121 as the number key and each field has a different hours in the hours field. What i'm trying to do is go through the table and count all the records that have 52121 and add up all the hours and divdide by the number of records counted. I could use some help in setting up the loops and counts,any help would be awesome. If i was unclear about anything let me know and i will try to clear it up.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Count Records
- Thread starter ryan1
- Start date
Here is the SQL for a query that will give you the results you are asking for:
SELECT A.Numb_Key, Count(1) AS Numb_Records, Sum(A![Hours]) AS Total_Hours, Sum(A![Hours])/Count(1) AS Avg_Hours
FROM YourTableName A
GROUP BY A.Numb_Key
ORDER BY A.Numb_Key;
You will have to update the correct Table name and the correct field names. But, this gives you one row for each Number Key with a count of the Number of records, Sum of the hours as Total_Hours, and finally the Avg_Hours.
Let me know if there needs to be any adjustments.
Bob Scriver
SELECT A.Numb_Key, Count(1) AS Numb_Records, Sum(A![Hours]) AS Total_Hours, Sum(A![Hours])/Count(1) AS Avg_Hours
FROM YourTableName A
GROUP BY A.Numb_Key
ORDER BY A.Numb_Key;
You will have to update the correct Table name and the correct field names. But, this gives you one row for each Number Key with a count of the Number of records, Sum of the hours as Total_Hours, and finally the Avg_Hours.
Let me know if there needs to be any adjustments.
Bob Scriver
- Thread starter
- #3
Inside the table there are 20,000 records and alot of them are ducplicates of each other like there could be 5 records that have 51211 for there number key and 10 that have 62131 as there number key so what i have to do is add up the hours associated with the owns with duplicate number in the key divide by the number of duplicate records for that key and record that number in a another field for each one of the duplicate number keys(the hours will be recorded evenly across the duplicate number keys). Don't know if this is any clearer or not thanks for the help.
This SQL does just as you described. You just have to update the table name and fields and test it out. Now if you need help updating someother table we can do that also. Or we can make a table of the results. What is it that you want to do with this information. Sounds like you want to update something but I am not sure what.
For starters use the SQL and create a query as I suggested and fill in the correct names and run the query. Let's see if this is the data that you are looking for.
Let me know then what is the next step. Bob Scriver
For starters use the SQL and create a query as I suggested and fill in the correct names and run the query. Let's see if this is the data that you are looking for.
Let me know then what is the next step. Bob Scriver
- Thread starter
- #5
I have a similar problem. I need to calculate the number of days between multiple records where the member ID is the same. The SQL statement works great, but I'm not sure what function to use to count the number of days between service dates. For further clarification I'm trying to report on patients who visit an ER 3 or more times within a 30 day period. However there is a catch, a patient may visit ER once, let's say on 01/01/03 then not return until 03/01/03 and again on 03/03/03 and again on 03/05/03 and again on 03/15/03. Which if this is the scenario I do not want to count the ER visit on 01/01/03, just the following 4 visits. Any thoughts and ideas would be greatly appreciated!!
- Status
Similar threads
- Locked
- Question
