Writing a Query to match Excel lookups

[pickslides]

Hi there, I have attached an XLS file I wish to replicate in Access using


querie(s).

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


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


TIMESHEET has 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

Thx, MQ

[ranman256]

in access, just a simple query is the same as a lookup.
put what you want to find in the criteria and run.

[pickslides]

Hi, thanks for the reply.

I do know that is the idea but for some reason as I make the joins I lose records.

In XLS I can lookup the values and #NA will appear if required, in Access the record is lost. I know how to use the left and right joins but when I link more then one table I lose some records.

[June7]

Table relationship links should use Primary Key and Foreign Key fields. Your designated Primary Key fields are not utilized in the links.

Your table relationships as established in the query don't make sense. What is the nature of this data? Which table is 'parent', which tables are 'dependent', which tables are 'lookups'?

However, shouldn't catchment entity_id be linking to CASES assignee_group_id? This is what the Excel VLookup is doing.

Forget everything you know about Excel when building relational database. Here is a good tutorial site http://www.rogersaccesslibrary.com/


Here is the Access equivalent for AssigneeGroupID and Catchment VLookup:
SELECT Timesheet.*, DLookUp("assignee_group_id", "Cases", "reference_number='" & [description2] & "'") AS AssigneeGroupID, DLookUp("display_text","catchment","entity_id='" & [AssigneeGroupID] & "'") AS Catchment FROM Timesheet;


Why do you have Choose() in the VLookup for CaseID? Why is the table array only one column, shouldn't there be at least 2?


Consider this query:

SELECT Timesheet.*, CASES.id, CASES.assignee_group_id, catchment.display_text, catchment.field_name, Subtype.display_text, Subtype.field_name
FROM Subtype RIGHT JOIN (catchment RIGHT JOIN (CASES RIGHT JOIN Timesheet ON CASES.reference_number = Timesheet.description2) ON catchment.entity_id = CASES.assignee_group_id) ON Subtype.entity_id = catchment.entity_id;

[ranman256]

Its possible the data in both table does not match exactly,