a query (i think it's simple, but impossible for me)

[iammichael]

Hi,

I have 3 tables (i have 5, but i think it's the same)

t1 (id,name,month,hours)
1,peter,july,25
2,peter,july,5
3,mike,june,7
4,peter,april,50

t2 (id,name,month,hours)
1,peter,june,13
2,jane,july,5
3,peter,july,6

t3 (id,name,month,hours)
1,jane,july,70
2,jane,june,3

i want a query that returns me (the order doesn't matter):

peter,june,13
peter,april,50
peter,july,36
mike,june,7
jane,june,3
jane,july,75

thank you very much for your help

 

[Jibbadiah]

Ian,

Doesn't look like you should have this information in separate tables. It looks like all of the info should be in one table because it contains the same fields, the same detail and there is nothing to distinguish between them. I would suggest that you take another look at the design of your database. Read up on concepts about "normalisation"... sounds like it would be a better place to start.

J.

 

[iammichael]

Ian,

Doesn't look like you should have this information in separate tables. It looks like all of the info should be in one table because it contains the same fields, the same detail and there is nothing to distinguish between them. I would suggest that you take another look at the design of your database. Read up on concepts about "normalisation"... sounds like it would be a better place to start.

J.

that 3 tables have another fields that i haven't post, different for each table.
In each table I have the hours of an specific work and i want to know how many hours has worked a person for month.

thanks

 

[Jibbadiah]

Ian, No offence mate... but if you have 3-5 tables that all have name,month,hours then you should look into the concept of normalisation. I would suggest that you have table tblEmployee with employee details, and then this is joined to an tblHoursWorked table on EmpId. You shouldn't have the same details in different tables... it is inefficient and dangerous to have to update multiple tables with the same data... which table is the correct one if they get out of sync? The other fields may warrant tables of their own, but the fields that you have described don't sound like they should be separated. Having a different id in each table for different people sounds fundamentally wrong as well... id is used for a reason... it should be unique, and it should identify a single individual across all of the tables with which they have a relationship. Just my opinion... others may disagree.

 

[iammichael]

Ian, No offence mate... but if you have 3-5 tables that all have name,month,hours then you should look into the concept of normalisation. I would suggest that you have table tblEmployee with employee details, and then this is joined to an tblHoursWorked table on EmpId. You shouldn't have the same details in different tables... it is inefficient and dangerous to have to update multiple tables with the same data... which table is the correct one if they get out of sync? The other fields may warrant tables of their own, but the fields that you have described don't sound like they should be separated. Having a different id in each table for different people sounds fundamentally wrong as well... id is used for a reason... it should be unique, and it should identify a single individual across all of the tables with which they have a relationship. Just my opinion... others may disagree.

the tables have to be separated because the haven't a lot of different data. It's impossible for me now to explain you why i have the tables that way. but thanks for your advice!!! Any idea of a query that makes what i want??? thanks again

 

[Jibbadiah]

Ian, If you post your db in Access 97 (all that I have available at work) I can take a look.
Otherwise look up my email address, zip it and email it to me.

J.

 

[iammichael]

where i can see your e-mail adress? i your profile i can't...

 

[Jibbadiah]

I think that you should be able to download a vcard?!

Anyway... try this

james DOT ramsay AT rbs.co.uk

replace the capital letters with their respective symbols... don't want this email to be picked up through phishing.

J.