I'm working on an asp.net application that is to be used for keep track of attendance. I created an access database to hold all the information I need.
I have two tables-
One table (called attendance) will keep track of days off. The fields are dDate, tReason, and nEmployee_id.
The other table is employees. It has fields tName, dStart_date, and nID.
As you can probably guess, I want to use the employeeID field in both databases for the relationship. Inside access that is no problem, but how do I use this programatically through VB?
The first page comes up, and it is a listbox titled "The following people are out today". Now I just want to populate the listbox.
Basically, all I need to do is query based on today's date, and then using the ID field of the recordset, pull up the name from the other table.
How do I do this? I really don't know much about databases, and come from a VB6 background. I would think there is a relatively easy solution, maybe even one on the internet somewhere, but I don't know how to even search for it.
Welcome to Forum !
To answer your Question, I am not as familiar with ASP.Net as other things but as teddy stated I am certain it allows you to run SQL statements. From what I hear you are looking to perform a JOIN in your query. A Gentle Introduction to SQL is where I got my SQL feet wet.
OK, I think that is getting closer, but I'm not sure it's exactly what I want to do. That introduction to SQL is great BTW, I have read much of it, but I want to get this issue solved so I don't feel so "rushed" reading it.
I will try to explain better. I have two tables:
Table 1: Employees
ID | Name
1 | John
2 | Bill
3 | Sam
Table 2: Attendance
ID | Date
2 | 1/3/05
2 | 1/4/05
3 | 1/4/05
The second table keeps track of days off. All it contains is the employee ID and the date they were off. Naturally, the same employee could be in there dozens of times (for different days).
I am creating a list that will show on any day what employees are out.
So the query will simply check for Attendance.Date = ????
But, rather than returning just the ID, can I match the ID fields in the two tables together, and have it return the names?
Please help me, I'm really struggling with this. DB is a whole new way of thinking.
I'm not sure if I am answering your question but I'll give it a try.
If you create a one-to-many relationship between your two tables then you can create the query. In the query make sure you put the date from the attendance table and the name from the employee table. It will automatically match them up.
Now for the date. You can use a parameter for you to be able to select the date (any date). When the query is in design, use the criteria of [Enter Out Date] or something to that effect. When you run the query (or a report or form based on the query) it'll ask you for a date.