Okay,
tblProcessData contains:
Processed_Country - Text
Processed_Supplier - Text
Dispatch_Method - Text
Item_WeightGMS (it was pointed out with brackets, the sql could be
confusing) - Long Integer
Processed_Country will contain the name of the destination country,
Processed_Supplier will contain the name of the company who will be mailing
the product,
Dispatch_Method contains the service this item will be sent and
Item_WeightGMS will contain the weight of the item.
qryCountryandSuppliers contains
CountryName - Text
Supplier - Text
DispatchMethodName - Text
WeightBandStart - Long Integer
WeightBandEnd - Long Integer
How values relate:
Processed_Country = CountryName - Text
Processed_Supplier = Supplier - Text
Dispatch_Method = DispatchMethodName - Text
Item_WeightGMS will fall in between WeightBandStart and WeightBandEnd
By the way, thank you for your on going interest in this Duane, I really
appreciate it.
John
Duane Hookom said:
You need to provide the significant field names and data types of
qryCountryAndSuppliers and tblProcessData as well as how values in the tables
relate.
--
Duane Hookom
Microsoft Access MVP
:
Sorry yes, the 'supplier' and 'company' are one and the same.
Our customers would supply us data containing addresses for which we would
send a particular product to. Depending on where they are (along with weight
and service) will dictate which supplier will be sending this item.
:
I don't think you have described enough about the "customer data" to allow us
to tell you how to create a dlookup of the other table/query. Apparently you
want the DLookup() to return the company. Your earlier attempts tried to
return a field named "Supplier".
--
Duane Hookom
Microsoft Access MVP
:
The way the supplier is selected is dependant on country, weight and dispatch
method.
For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed
I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.
Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3
When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.
I hope this makes sense; if there's anything else i can clarify let me know!
John
:
You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP
:
That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!
:
If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")
--
Duane Hookom
Microsoft Access MVP
:
Hi Duane,
Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?
:
It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")
--
Duane Hookom
Microsoft Access MVP
:
Hi there,
I'm having trouble using the DLookup function at the moment, and need your
help!
I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.
So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".
With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:
CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd
the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:
DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])
All it seems to be doing is referencing the same supplier; where am I going
wrong?
