Query to match XLS lookups

[pickslides]

Hi there, I have attached an XLS file I wish to replicate in Access using querie(s).

I seem to be losing to many records with my attempts.

The XLS sheet has 281 records when appending data using lookups and the Access Query only has 213 records.

MQ

My DB file was 2.3 MB so it couldn't be uploaded!

 

[pbaldy]

Try this to attach the db:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[pickslides]

Thx Paul, attached the DB in post #1

 

[pbaldy]

I get a warning on the spreadsheet so have not looked at it. In the db, it appears the timesheet table contains the 281 records you're looking for? I don't understand the data in the tables right off, but perhaps the query needs to be based primarily on the timesheet table?

 

[pickslides]

Yeh I have tried to link everything back to the TIMESHEET table with different types of joins and losing records

 

[pbaldy]

Well, if you just queried that table you'd get all the records. I don't understand how the tables relate to each other, so can't offer a solution that includes the other tables. If they related directly to the timesheet table, you can edit the join to get all records from timesheet.

 

[pickslides]

It is true that TIMESHEET returns all 281 records but I want to append the following fields to this table

Append

catchment.display_text

Using the relationship

Catchment.entity_id = CASES.assignee_user_id

AND
Append

Subtype.display_text

Using the relationship

Subtype.entity_id = CASES.id

AND

ALSO needing to join

 Timesheet.description2 = CASES.reference_number

 

[pbaldy]

The records in other tables would need a direct or indirect relationship to timesheet. Can you describe the nature of the data in the tables and how they relate to each other?