Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: New help with relationships...

    No, my wife is just fine

    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.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use WHERE dDate = Date() in your recordsource. I'm not sure how recordsets are created in asp, but I imagine you're given the option to provide a SQL string at some point?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    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.

    That's my 2 Deutch marks.
    Darasen

  4. #4
    Join Date
    Dec 2004
    Posts
    3
    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.

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    look at joins, That is what you need

    something like

    Code:
    SELECT Employees.Name, Attendance.[Date]
    FROM Employees INNER JOIN Attendance
    ON Employees.ID = Attendance.ID
    HTH
    Dave

  6. #6
    Join Date
    Nov 2003
    Location
    Moorpark, CA
    Posts
    104
    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.

    Is that what you were asking?

    Some one beat me to it with a code =)

  7. #7
    Join Date
    Dec 2004
    Posts
    3



    It worked- well, I had to mess around a bit, but the general idea worked. I am learning a lot about DBs.

    Thanks alot for all your help. Here is the line:

    Dim strSQL As String = "SELECT * FROM Attend, EmployeeInfo WHERE (Attend.nEmpID= EmployeeInfo.Employee_ID) and Attend.dDate='" & Date.Today.ToShortDateString & "'"

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Huzzah! Good show sir.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •