Select query help!

[registan]

Hi all,


I have two tables, tb_Name and tb_Salary. In the tb_Salary table, there are more than one record for each person in tb_Name table.
Now, I want to select exact record from tb_Salary which corresponds my criteria based on date.

Please, help.


Thank you.

[Bob Fitz]

What is the relationship between the two tables?

[registan]

More details:

tb_Name: EmployeeID, Name, LastName;
tb_Salary: EmployeeID, Salary, Date.

Now, I want to create a query that selects records which are active in real period. I tried "Max" and "Last", but these are don't fit for me.

q_TimeTable: Date (Not the date that in Salary table), EmployeeID, Name, LastName, Salary

[Bob Fitz]

I repeat. What is the relationship between the two tables?
Do you have a Primary Key fields in these tables. If so, what are they called?
Name and Date are reserved words and should never be used as field names.

[registan]

I repeat. What is the relationship between the two tables?
Do you have a Primary Key fields in these tables. If so, what are they called?
Name and Date are reserved words and should never be used as field names.

Primary key is EmployeeID, tables have a "One-to-many" relationship.
tb_Name has only one record for one person, tb_Salary has more than one, depending on what salary do the employee has (i.e. In January David has $1000, in February $1200, in March $1500 etc.)
When I want to see David's history of salaries, I want q_TimeTable show in January $1000, in February $1200 etc.

[Bob Fitz]

Primary key is EmployeeID, tables have a "One-to-many" relationship.
tb_Name has only one record for one person, tb_Salary has more than one, depending on what salary do the employee has (i.e. In January David has $1000, in February $1200, in March $1500 etc.)
When I want to see David's history of salaries, I want q_TimeTable show in January $1000, in February $1200 etc.

Add both tables to a new query.
Insert the required fields in the fields columns (each field in it's own column). I think you want these fields:
Name
Date
Salary

Type "David" in the criteria row of the Name column.
As I said earlier, you will need to change the names of the fields: Name and Date
I have assumed that the EmployeeID field in the salary table is the foriegn key.
The Salary table needs a Primary Key.

[registan]

Thank you for the answer Bob.

I have [Enter the date] criteria in the Date field.
If I enter, let's say 15.01.2012, it should show me smth like this: Name: Dave, LastName: Smith Salary: $1000
But, if I enter February, i.e. 15.02.2012, it should show me: Name: Dave, LastName:Smith, Salary: $1200 etc.


Thank you.

[Bob Fitz]

Put this in a new column as field:
Mth: Month(NameOfDateField)
In the criteria row of this column put:
[Enter Month Number]

You will need to change NameOfDateField to whatever you have changed the "Date" field name to. I asume that you have changed it as I advised. If not you could have problems.
When prompted enter 2 instead of February.

[registan]

Put this in a new column as field:
Mth: Month(NameOfDateField)
In the criteria row of this column put:
[Enter Month Number]

You will need to change NameOfDateField to whatever you have changed the "Date" field name to. I asume that you have changed it as I advised. If not you could have problems.
When prompted enter 2 instead of February.

As far as I understand, the only criteria will be [Enter month number], if so, it's giving me more than one record for one person:

q_TimeTable.Date tb_Name.EmployeeID tb_Name.Name tb_Name.LastName tb_Salary.Salary

2 00001 David Smith $1000
2 00001 David Smith $1200
2 00001 David Smith $1500

[Bob Fitz]

If criteria is only applied to month, then that is the only restriction on what is returned. If you also want to only show records for one person, you will need to enter something in the criteria row of the Name column.
If you want the total of salary, rather than individual records, you will need to turn it into a Totals query. I use A2003, so I'm not familiar with your UI. In A2003 I have a button on the toolbar that look a bit like a back to front capital letter E. After this is clicked a new row appears in the design grid labelled Total. Each column will have a default setting of Group By. Change this to Sum in the Salary column.

[registan]

My porblem is in Salary section, not Name.
I want to see who has what amont of salary in which month. If I typed "2" (February), it should only show me $1200, because David's salary was $1200 in February. If I type "1" (january) it should show me $1000, because David's salary was $1000 etc.

[Bob Fitz]

So what does it show

[registan]

So what does it show

As I said earlier, it shows all the data David has, not the salary for particular month.

[Bob Fitz]

Can you post the SQL statement that you have now please

[registan]

Can you post the SQL statement that you have now please

Here it is:

SELECT Format([Enter date],"00\/00\/0000") AS [Date], tb_Name.Name, tb_Name.LastName, tb_Salary.Salary
FROM tb_Name INNER JOIN tb_Salary ON tb_Name.EmployeeID = tb_Salary.EmployeeID;