Help with Query

[Ctwizzy]

I have 2 tables

Emp_Records and Logins

they are like so:

Emp_Records
- empID
- empName
- empAddress
- empPhone
- etc..

Logins
- empID
- timeIN
- timeOUT
- designation

Now I want to show the following in a data grid (Name, timeIN, timeOUT, designation)

This is my query, yet its returning no results.


SELECT Emp_Records.empName, Logins.timeIn, Logins.timeOUT, Logins.designation FROM Logins INNER JOIN Emp_Records ON logins.empID = Emp_Records.empID


Am I doing something wrong?

Also

The timeIN and timeOUT would probably show as the DATE I want the TIME, how would I go about throwing this in my query also as a dateformat?

And is it possible to change the names of the fields.
(name, timeIN, timeOut, designation) will look odd in the Data Grid. Would be nice to have (Employee Name, Time Logged In, Time Logged Out, Designation)

Thanks.

[Michaelw]

You may not have any records for an employee id. Try a left outer join that will give you a return regardless as long as an employee is defined. Alias naing is included in the query. Be careful with having aliases with spaces in them. It may cause complications.

SELECT Emp_Records.empName as 'Employee Name' ,
Logins.timeIn as ' Time Logged In',
Logins.timeOUT as 'Time Logged Out',
Logins.designation as 'Designation'
FROM Emp_Records
LEFT OUTER JOIN logins
ON logins.empID = Emp_Records.empID

[Ctwizzy]

employee ID is the non null primary key. There has to be a empID.

Ill try what you said though.

[Ctwizzy]

You were right its showing Employee ID as having nothing but NULL values all the way down.. this is odd as they are populated. hmmm.

Also in the emp_records the primary key is ID and name, but in the logins I dont have one as I dont know what the primary key should be. Its a table of time ins and outs, and the ID. Id cant be as there will be multiple times they login/out.

Now seeing as I will be required to create reporting of the information on a day to day basis. Should I do tables on a day to day basis so it doesnt have to do large searches? or all in this table is ok?

[Michaelw]

Research the use of surrogate keys.

I personally would keep emp_records as is, but add a record id field and make it either an integer ( If your emp_id is an integer just use it instead. ) or guid ( only if you plan on replicating or importing/exporting ) from different databases that are similar in design. Make it your primary key.

In the login table I would make the addition of a record id ( name it whatever you want but standards in key naming are benefcial ) field here. Then use emp_id as your foreign key to emp_id in emp_records ( must be same data type as emp_id in emp_records ).

In emp_records hang a clustered index on Employee name as you will use that to look up specific employee info and a primary key index on emp_id.

In logins hang a primary key index on your new record id field and a regular non-clustered index on emp_id. Then set your foreign key constraint from emp_id to emp_id in emp_records.

Also hang a non-clustered regular index on any field you might reference in a WHERE clause of a select, like destination, address etc... This will boost query performance when you data gets volumous.

You can keep all data in the two tables unless you see your data getting really large in the short term future. You may hit a size restriction is Access. SQL has no size restriction you will hit.

[Ctwizzy]

Ill be honest, a lot of what you said flew right over my head lol.

Research the use of surrogate keys. Will do

I personally would keep emp_records as is, but add a record id field and make it either an integer ( If your emp_id is an integer just use it instead. ) or guid ( only if you plan on replicating or importing/exporting ) from different databases that are similar in design. Make it your primary key. My emp_id is a text string as its 14chars long, yes it is numbers but having a 14digit would be a huge pain.

So your saying add a new field call it recordID and make it an int (auto inc int?) and set it as the primary key? Why would I make this the primary key over (empID, empName)? If the records table there wont be duplicates?

In the login table I would make the addition of a record id ( name it whatever you want but standards in key naming are benefcial ) field here. Then use emp_id as your foreign key to emp_id in emp_records ( must be same data type as emp_id in emp_records ). Ok not sure how to set foreign keys in Access, but as a Foreign Key wont it still stop duplicates?
And whats the recordID doing here as well?

In emp_records hang a clustered index on Employee name as you will use that to look up specific employee info and a primary key index on emp_id. Clustered Index? no idea what this is either so guess Ill have to read up on this as well lol.
Primary key index? Above you said make the recordID the primary key, is a primary Key index and a primary key different?

In logins hang a primary key index on your new record id field and a regular non-clustered index on emp_id. Then set your foreign key constraint from emp_id to emp_id in emp_records. I can tell you know your SQL very very well... lol

Also hang a non-clustered regular index on any field you might reference in a WHERE clause of a select, like destination, address etc... This will boost query performance when you data gets volumous. Starting to think I know nothing of SQL besides basic queries (**** university course)

You can keep all data in the two tables unless you see your data getting really large in the short term future. You may hit a size restriction is Access. SQL has no size restriction you will hit. No I really cant see it getting too big, but ill try and figure out how to set it up that when the largest size is reached, it will dump the contents to a new table as an archive.

Thanks for your help so far Michael.

[Michaelw]

You make the new record id field your primary key because you have to in order to have another table reference it as a parent table. The primary key is just another unique index, but is labeled "primary" so you can reference it with other children tables.

Yes, I would create a recordid column and make in auto increment.

You handle duplicates by putting a unique index on emp_id and name. Neither the record id field nor the combination of emp_id and name can be duplicated via the indices.

The use of the terms primary key and primary index are interchangable. Keys are always an index, but an index may not always be a key.

Access may not have clustered indices. In that case just use a unique index.

In good database structure every table should have a column ( or combination of them ) that make that record unique. In the case of you login table you really don't have a good candidate for that role except emp_id + timeIn + timeOut. That could be cumbersome and if you allow nulls in any one of those columns you have a problem. The answer is too create a "surrogate" key column like a record id and that will always be unique. You can still hand a regular index on emp_id + timeIN + timeOUT if you wanted to so you could use that combination of column values ina search later.

To set a foreign key in Access once you have the indices built in the database designer, click on the index for emp_id in logins and drag it onto the primary index in emp_records and let go. The IDE will do the work for you.

In code you would execute the SQL Statement that builds your child table ( logins ) and add the statement "REFERENCES emp_records ( emp_id ) in the line that defines emp_id ( foreign key field ).

I know more about SQL than Access. I also am an Xbase programmer.

[Michaelw]

Sadly, but indexing itself is a science and should be a whole class and is not emphasized enough at universities.

Many performance problems can be traced to improper indexing or no indices on the proper columns.

There is a trade off. More indices make queries faster but inserts and update are slow because the indices have to be updated everytime. You have to find the right balance. For practice you can play with teh index building wizard on SQL Server ( Access ).

As a rule every table should have at least one column to use as a primary key. I prefer single column surrogate keys. You can also hang indices on columns or combination of columns that make a record unique ( there are called strong keys ).

Then you put regular indices on columns ( or combos ) that you frequently use in queries.

Example the phone book. You would have a unique index on the number. You would then have an index on last name ( not unique ). Then one on last name + first name. The another one on city and street columns. If address was all one field you would be using the LIKE keywords in you query.

Now if you wanted to run a query that looked for all John Doe's in Chicago

SELECT lastname + ', ' + firstname as name,
streetnum + ' ' + street +', ' + city as address ,
phonenum as telephone
FROM book
WHERE lastname + firstname = 'Doe John' and city = 'Chicago'

The indices you built on names and city columns would make this search a lot faster.

[Ctwizzy]

Hey Micheal,

I was away for a week (hence the late response).

Anyways I have been looking up the use of Surrogate keys.
Seems that its just a number field used to ID the rows from parent to children.

So your saying use Surrogate Keys vs Regular (fields as the primary key).
Ok so unless my understanding of surrogate keys is wrong I dont quite understand the following:

So table 1 has 15 entries so they each have a unique entry for their surrogate key like so:

Table1: Emp_Records
Primary key is RecordId which is a surrogate Key
RecordID | UserId | UserName | Email | address | city ...
1 123 Joe
2 456 Tom
3 789 Ryan
4 101 Bill
5 112 Chris
6 131 Richard
7 415 Roger
8 161 Cliff

Table2: Punch_Clock
Primary key is a foreign Key for RecordID in Emp_Records
RecordID | UserID | TimeIn | TimeOut | designation


Now what I dont understand is how the RecordID links the two together? As new entries in either table just incrememnt the number, so the numbers in both tables hold no actual relevance?

What I need to have is the Emp_Records be the parent table, and the Punch_Clock be the child.

I dont completly understand Index's and dont have the time atm to learn them in depth, so a crash course or something would be appreciated.


You handle duplicates by putting a unique index on emp_id and name. Neither the record id field nor the combination of emp_id and name can be duplicated via the indices.

Ok so indices stop duplicates, indices are just a way of organizing and referencing the database right?

Thanks so far Micheal.