Relationship problem in query (1 Viewer)

[kentwood]

Have a couple of queries that I am trying to joing and I am having problems with matching the date and figures from one query to the other query. In other words, 1 query has has a date field with measurements that began effective Oct 2003. The other query has a date field with measurements that span each month, Jan - Oct 2003. The joining field is the employee Extension #. When I join them, the 2 measurements (No Reason & Fatique) that were effective Oct 2003 also repeat themselves for the employees extension for each month rather than just Oct. Not sure how to correct that problem. Have tried many combinations.

I have a test file that I am attaching that is a bare skeleton to show what I am trying to do. Only includes the necessary tables, the queries I am trying to combine and the report.

Hopefully, someone can help me. After a couple of days playing with this, I am getting a bit frustrated and need to complete my project.

 

[boblarson]

Your database is in SERIOUS need of Normalization (run a search for Normalization here on this web site and put in Pat Hartman as the poster when you fill in the search form. She's posted some really good stuff on it).

One thing, is that your primary key of one table should be the foreign key in the other table. And, you do not duplicate data, other than the key within your tables. Right now you violate that one by having the name of the person in all 4 tables.

 

[kentwood]

I guess you were right about my DB....it was a mess. Thanks for the directions on where to find the information on normalization. Some great stuff and very helpful. I am correcting the DB at this point.

One other question if you do not mind. I have searched and searched for the answer, and just can't seem to find it anywhere. On the DB that was attached, the data I was having trouble with imports as: 00:00:00, 6 digits using a zero for the 1st of each set if there are not 2 numbers. Unfortunately, this imported data represents Minutes (not hrs), Seconds, Partial Seconds. I have it formated as hh:nn:ss because I have no idea how else to do this. I think this will work fine, except for those times when the 1st 2 digits are 2 numbers. I.e. In excel, my number might show 04:20:33. In Access, it is 4:20:33. Why is this, when I used the hh:nn:ss? I have a couple sets of numbers that did not import because they are formatted in Excel as 37:22:43. Access will not read the double diget hh. What is wrong?