Too Many Records

[Rbutts25]

For some reason, in the query I created, the number of records is 12321. It should only be 111. The number of records seems as though it was squared. Does anyone know anything as to why this is happening?

 

[Fuga]

Check the joins in the query.

Fuga.

 

[Rbutts25]

How do I do that?
I'm sorry, I am a newbie to Access.

 

[namliam]

*GRMBL*

Click on the query
Click on Design
Now check your relations (the lines)

If you cant solve it post the db. (or buy a book, or get a pro in to do the job)

Regards

 

[Rbutts25]

There are no lines

 

[Fuga]

Or, you could click view->SQL and paste it here.

Fuga.

 

[Rbutts25]

Here is the SQL, thank you for your help.


SELECT [MBUSA Resource List].[I/O Number], [MBUSA Resource List].[I/O Name], [MBUSA Resource List].Consultant, [MBUSA Resource List].Vendor, [MBUSA Resource List].[IT Department], [MBUSA Resource List].[Current Rate], [MBUSA Resource List].[Planned Days], ([Current Rate]*[Planned Days]) AS [Annual Cost], qselDateDifferenceDemonstration.WorkDaysDifference, [MBUSA Resource List].[Contract Expiration], [MBUSA Resource List].Active, [MBUSA Resource List].[Reports To], [MBUSA Resource List].Classfication, [MBUSA Resource List].*
FROM [MBUSA Resource List], qselDateDifferenceDemonstration;

 

[Fuga]

If there are no lines, that could be a problem.

Fuga.

 

[pjb]

You seem to be selecting loads of columns from your table MBUSA Resource List and the value WorkDaysDifference from a select query qselDateDifferenceDemonstration. If you view this in design view you should see a line between the two tables. if not - put one there!
What is common between the query and the table? Ie. you want to see values x y and z from the table plus the value from the query, but unless you stipulate a relationship between the two you will see every value from the query against every value from the table.
You say there should only be 111 results but in fact there are 12321, that suggests there are 111 results in the query also, hence each value from the table is shown with each value from the query.

 

[namliam]

Did you get it to work in the end?

Regards

 

[Rbutts25]

I still have not been able to get this to work. I am going to continue playing with it to try to figure out, thank you for your help.

 

[Rbutts25]

When I created relationships between the table and query I ran into another proble. Now, I am not getting any results.

This is the SQL:

SELECT tblMBUSAResourceList.[I/O Number], tblMBUSAResourceList.[I/O Name], tblMBUSAResourceList.Consultant, tblMBUSAResourceList.Vendor, tblMBUSAResourceList.[IT Department], tblMBUSAResourceList.[Current Rate], tblMBUSAResourceList.[Planned Days], [Planned Days]*[Current Rate] AS [Annual Cost], tblMBUSAResourceList.[Contract Expiration], tblMBUSAResourceList.Active, tblMBUSAResourceList.[Reports To], tblMBUSAResourceList.Classfication
FROM tblMBUSAResourceList INNER JOIN qselDateDifferenceDemonstration ON (tblMBUSAResourceList.[Actual Days] = qselDateDifferenceDemonstration.dtDay2) AND (tblMBUSAResourceList.[End Date] = qselDateDifferenceDemonstration.dtDay1);

Does anyone see any flaws in the sql?

Ryan

 

[Pat Hartman]

If you are not getting any records either your join criteria is not correct (you are not joining on the right columns) or there really are no records in the second table that match the first table. In the second case, you might need to use a Left join (or right) instead of an inner join.

 

[namliam]

Try posting a sample of your db with a little example data in it...

Regards