Darren Bartrup
Well-known Member
- Joined
- Mar 13, 2006
- Messages
- 1,297
- Office Version
- 365
- Platform
- Windows
Hi all,
I need to extract a percentage of records for each team member from an original raw data table.
My raw data doesn't have a natural key, but can add an auto-number if needed.
All the fields, except date, are text fields in a table called tbl_PrimaryData:
dDate, sProcess, sPropID, sPropAddress, sSurname and sForename.
The sSurname & sForename combination are pushed to a separate table called tbl_TeamMember which has these fields: ID, Surname, Forename and SampleSize. SampleSize is a double formatted as a percent and is manually entered - usually set at 5%.
The SQL for this is:
So now I want to extract the SampleSize of records from tbl_PrimaryData for each team member and place the results in a table called tbl_Audits.
Both team member names and process text will be pushed out to separate tables to normalise the data.
If anyone has any thoughts on this please - I'm not having much luck.
Edit:
SQL like the below will give me random ordering of the records grouped by team member, but I can't seem to restrict it to the first n percentage.
I need to extract a percentage of records for each team member from an original raw data table.
My raw data doesn't have a natural key, but can add an auto-number if needed.
All the fields, except date, are text fields in a table called tbl_PrimaryData:
dDate, sProcess, sPropID, sPropAddress, sSurname and sForename.
The sSurname & sForename combination are pushed to a separate table called tbl_TeamMember which has these fields: ID, Surname, Forename and SampleSize. SampleSize is a double formatted as a percent and is manually entered - usually set at 5%.
The SQL for this is:
Code:
INSERT INTO tbl_TeamMember ( Surname, Forename )
SELECT DISTINCT sSurname, sForename
FROM tbl_PrimaryData
WHERE sSurname & sForename NOT IN (SELECT sSurname & sForename FROM tbl_TeamMember);
So now I want to extract the SampleSize of records from tbl_PrimaryData for each team member and place the results in a table called tbl_Audits.
Both team member names and process text will be pushed out to separate tables to normalise the data.
If anyone has any thoughts on this please - I'm not having much luck.
Edit:
SQL like the below will give me random ordering of the records grouped by team member, but I can't seem to restrict it to the first n percentage.
Code:
SELECT dDate,
sProcess,
sPropID,
sPropAddress,
sSurname,
sForename
FROM tbl_PrimaryData
ORDER BY sSurname, sForename, RND(dDate)
Last edited: