Report problem

[tmarsh]

I'm having problems with a report printing the wrong information. I posted a while back and thanks to the replies I modified the join and it worked. Except I have just noticed that the report lists a staff member and training done OK but if there's say, 3 Smiths it lists the first Smith but the other two's training as well.

I've tried modifying the table join using the 3 types in access but no good.

Any idaes?

Thanks.

 

[Lightwave]

TMarsh,

I suspect this is because you are joining two tables based on a non unique field. I am assuming you have two tables - The first consisting of staff members and the second consisting of Training done.

The staff members table should have a unique ID field preferably an autogenerated number. Each staff even with the same name will have a different unique ID.

The Training Done table will have a related field which is generally a long integer field. I tend to call it something along the lines of LinkID. This is linked to the name table in a one to many relationship and there is the option to enforce referential integrity.

In order to make your tables agree you will need to add the [uniqueID] field to the staff table and add a [linkID] field to the training done table. Depending on the number of staff and amount of training they've done this could be easy or more involved as in order to make the information comply you would have to ensure that the [LinkID] field was populated with the correct numbers that relate to the [uniqueID] field if you have the link established and referential integrity enforced you will never need to go back and go through this process again..

So it looks like you're trying to go back and re-structure a slightly dubious join... Never the easiest thing...

M

 

[tmarsh]

TMarsh,

I suspect this is because you are joining two tables based on a non unique field. I am assuming you have two tables - The first consisting of staff members and the second consisting of Training done.
M

Thanks for that Lightwave. I think I have the tables set correctly, I do have what you said. Someone here at work said it may be that in the query I have use DISTINCT so I'll look at that tomorrow and let you know.

 

[tmarsh]

TMarsh,

I suspect this is because you are joining two tables based on a non unique field. I am assuming you have two tables - The first consisting of staff members and the second consisting of Training done.

So it looks like you're trying to go back and re-structure a slightly dubious join... Never the easiest thing...

M

Well, it's not DISTINCT so it looks like you're right lightwave. I'll have a look at the tables.

 

[Lightwave]

T Marsh,

Here's a very basic database which I made up in Access 2000 that show's what I'm talking about. You will see as an example I've input two T Marsh's. As each has a unique ID the training associated with each remains separate..

Your database should conform to a similar structure .. see the tools / relationships


M