Lookup from query (1 Viewer)

theinviter

Registered User.
Local time
Today, 10:00
Joined
Aug 14, 2014
Messages
237
Dear Guys
need help,
I have query that have combined table, so have create another query and i want to make an expression to get the data from that query based on 3 criteria.
i tried this code
Query Name : " Calender Query"
need add expression to get the data from MRN Query1

Expr1: DLookUp([MRN Query1].[Expr1],[MRN Query1],"[Item Code]='" & [MRN Query1].[Item Code] & "' AND [Clinic]='" & [MRN Query1].[Location - Clinics Name] & "' AND [Company] like '" & [MRN Query1].[Account Name] & "'")

but no successful
any help
find attached file
 

Attachments

  • Database2 - Copy.accdb
    1.1 MB · Views: 247

June7

AWF VIP
Local time
Today, 09:00
Joined
Mar 9, 2014
Messages
5,423
There are no records in MRN to match with Combine.

The field to search and the table must be within quote marks.

Which query are you putting this DLookup into?

Think I suggested in another thread that you should save PK of Combine as FK into MRN. You are not saving designated PK. Either save the PK ID or set a compound key on [Item/ quantity ordered] and Clinic fields. I avoid compound keys.

Also, strongly advise not to use spaces nor punctuation/special characters in naming convention.
 

theinviter

Registered User.
Local time
Today, 10:00
Joined
Aug 14, 2014
Messages
237
There are no records in MRN to match with Combine.

The field to search and the table must be within quote marks.

Which query are you putting this DLookup into?

Think I suggested in another thread that you should save PK of Combine as FK into MRN. You are not saving designated PK. Either save the PK ID or set a compound key on [Item/ quantity ordered] and Clinic fields. I avoid compound keys.

Also, strongly advise not to use spaces nor punctuation/special characters in naming convention.
can you please guide me as i uploaded the file.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Jan 23, 2006
Messages
15,364
@theinviter
A few points to consider:

-with 115 posts I'm sure you have heard of using a naming convention where fields and object names have no embedded spaces, and no special characters.
-always a good start to describe (in simple terms) your database's purpose to put any issues into context
-provide a sample (even a mockup) of your starting data and your expected output/result
 

June7

AWF VIP
Local time
Today, 09:00
Joined
Mar 9, 2014
Messages
5,423
Guide you with what? Naming convention? Using PK? Using quote marks? Entering related records into Combine and MRN tables?

Okay, now I notice you do specify query to use. The DLookup WHERE CONDITION criteria is nonsense as is your data. You save text "Monthly", "Quarterly", etc into Combine. Yet, Calendar has 14 "Monthly" records - there is no way to know which one belongs with each record in Combine. Again, you are not saving primary keys into dependent tables as foreign keys. Possibly should have DateFrom and DateTo fields in Combine table and eliminate Calendar.

What is "MRN" and "Combine" data for?
 
Last edited:

theinviter

Registered User.
Local time
Today, 10:00
Joined
Aug 14, 2014
Messages
237
Guide you with what? Naming convention? Using PK? Using quote marks? Entering related records into Combine and MRN tables?

Okay, now I notice you do specify query to use. The DLookup WHERE CONDITION criteria is nonsense as is your data. You save text "Monthly", "Quarterly", etc into Combine. Yet, Calendar has 14 "Monthly" records - there is no way to know which one belongs with each record in Combine. Again, you are not saving primary keys into dependent tables as foreign keys. Possibly should have DateFrom and DateTo fields in Combine table and eliminate Calendar.

What is "MRN" and "Combine" data for?
What is "MRN" and "Combine" data for?

both are separate table from different source, so i have to linked the data in order to get required information.
 

June7

AWF VIP
Local time
Today, 09:00
Joined
Mar 9, 2014
Messages
5,423
That still doesn't tell what this data is. What is the data all about?

Do you understand what I was describing about these two tables - they do not have proper relationship.
 

Users who are viewing this thread

Top Bottom