Link to home
Start Free TrialLog in
Avatar of MichMat
MichMat

asked on

SQl for Access query

Hi,

I have anSQL query in my MDB which produces a new table by only selecting the top 10 entries for each emploees jobs by date (basicaly selects last 10 jobs they did)
it looks like this

SELECT * INTO EmpTemp
FROM ALLEMP
WHERE ALLEMP.HDATE IN (SELECT top 10 i.HDATE from ALLEMP i where i.name = ALLEMP.name)
ORDER BY ALLEMP.name, ALLEMP.HDATE DESC;

I need some help with expanding this by only selecting the workers that are working today, this information is in a nother table in this database and is called 'WorkingToday' and it also has 'name'
column.

The result would be to create table EmpTemp with the last 10 jobs the workers working today did.

Michal
Avatar of Smart_Man
Smart_Man
Flag of Egypt image

if you choose names from workingtoday , will that be ok ?

or do you have to choose from allemp then again from working today ?

i suppose the ones in workingtoday are already in allemp !



like this


SELECT * INTO EmpTemp
FROM ALLEMP
WHERE ALLEMP.HDATE IN (SELECT top 10 i.HDATE from workingtoday i where i.name = ALLEMP.name)
ORDER BY ALLEMP.name, ALLEMP.HDATE DESC;


waiting for your reply
ASKER CERTIFIED SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MichMat
MichMat

ASKER

Smart Man,

The names are indeed in the working today however the workers history is in ALLEMP
Avatar of MichMat

ASKER

Without doing too much testing of the result, this solution seams to produce the correct results.

Thank you to all
Avatar of MichMat

ASKER

The solution works But does JOIN the infromation from both tables into the temp table ......
which I didnt want. I submited a nother question in order to eliminate this from the table
I only want the history data NOT  the data from working today table
Glad to be of help :)