Vlookup from the base table

[irfanparbatani]

Hi Friends



I am writing here in regards to a scenario which I have recently come across and not being able to resolve it, would need you guys expertise on this.

Route Type ByRoad/Air Jan Feb MAR APR DEC
123....100......road.......22..22...22...22...22


I want to write a query in which i will be executing a price column in which I will combine Route, Type and ByROAD/AIR columns and get the price according to the month.

Please help. I am using Dlookup but the result is varying

Thanks and Regards

[ranman256]

Your table is structured wrong. Queries search top/down, not left to right.

Structure should be:
route, type, ByRoadAir, month, price
123 , 100 road, Jan, 22

This way a single query will return anything you ask.
Your current stucture, you must execute a query for each month column. (try not to use: Dlookup. Queries are faster)
but it would be
Dlookup("[route]","tbl","[jan]=22" )
then
Dlookup("[route]","tbl","[feb]=22" )
etc...

[irfanparbatani]

Your table is structured wrong. Queries search top/down, not left to right.

Structure should be:
route, type, ByRoadAir, month, price
123 , 100 road, Jan, 22

This way a single query will return anything you ask.
Your current stucture, you must execute a query for each month column. (try not to use: Dlookup. Queries are faster)
but it would be
Dlookup("[route]","tbl","[jan]=22" )
then
Dlookup("[route]","tbl","[feb]=22" )
etc...

----------------------------------------------------------------------------------------

I want to find total price and I am having table in the similar way you mentioned from Top to bottom. And what do I use to find with various criteria like route+type+byroad and find price from month column.

Regards

[June7]

Where would the month parameter come from?

The table structure is not normalized but can be dealt with.

Maybe like:

SELECT ID, DateOrder, DLookUp(Format([DateOrder], "mmm"), "PricesTable", "Route='" & [Route] & "' AND Type='" & [Type] & "' AND [ByRoad/Air]='" & [ByRoad/Air] & "'") AS Price FROM OrdersTable;


BTW, should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be ByRoadAir or Road_Air.

[irfanparbatani]

Where would the month parameter come from?

The table structure is not normalized but can be dealt with.

Maybe like:

SELECT ID, DateOrder, DLookUp(Format([DateOrder], "mmm"), "PricesTable", "Route='" & [Route] & "' AND Type='" & [Type] & "' AND [ByRoad/Air]='" & [ByRoad/Air] & "'") AS Price FROM OrdersTable;


BTW, should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be ByRoadAir or Road_Air.

================================================== =====

Hi Mate

Thanks for the reply

This is how my table would look like:

Route	Type	Road_Air	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG
125	Liquid	Road	12	12	12	12	12	12	12	12
126	Metal	Air	13	13	13	13	13	13	13	13
125	Waste	Road	14	14	14	14	14	14	14	14
124	Plastics	Air	15	15	15	15	15	15	15	15
115	Glass	Air	16	16	16	16	16	16	16	16
130	Paper	Air	17	17	17	17	17	17	17	17

If I select Route and Type and Road_AIR and if the current month is July Then 12 dollars for particular route should be displayed.

Thanks for your help

Route	Type	Road_Air	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG
125	Liquid	Road	12	12	12	12	12	12	12	12
126	Metal	Air	13	13	13	13	13	13	13	13
125	Waste	Road	14	14	14	14	14	14	14	14
124	Plastics	Air	15	15	15	15	15	15	15	15
115	Glass	Air	16	16	16	16	16	16	16	16
130	Paper	Air	17	17	17	17	17	17	17	17

[June7]

Okay, so where are you 'selecting' Route and Type and Road_Air?

Did you try the suggestion offered?

[irfanparbatani]

Okay, so where are you 'selecting' Route and Type and Road_Air?

Did you try the suggestion offered?

-----------------------------------------------------

Yes I tried it and there are only few values displaying from Price table and I have one table where data will be saved and other table as prices one to compare it according to the above mentioned query. Tried your query and few values are missing when that particular route and type is selected.

[irfanparbatani]

Okay, so where are you 'selecting' Route and Type and Road_Air?

Did you try the suggestion offered?

--------------------------------------------------------------------------------------------------------------
I am using the below query to get the output from the table

Price: DLookUp(Format([SvcDate],"mmm"),"[BNE_Subbie_Prices_Table]","[RouteNo]='" & [Subbie_Clean_Data].[RouteNo] & "'" & " [Type]='" & [Subbie_Clean_Data].[Type] & "'" & " [BNE_Subbie_Prices_Table].[Road_Air]= '" & "Road" Or "Air" & "'")


I am not getting appropriate output.

[June7]

Need the AND operator in the WHERE CONDITION argument. Look at my example again.

The Road_Air criteria syntax is not correct. Why are you using literal text and not referencing field same as for RouteNo and Type?

Why is the WHERE argument referencing two tables?

That is not the entire query statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

[irfanparbatani]

Need the AND operator in the WHERE CONDITION argument. Look at my example again.

The Road_Air criteria syntax is not correct. Why are you using literal text and not referencing field same as for RouteNo and Type?

Why is the WHERE argument referencing two tables?

That is not the entire query statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

----------------------------------------
Sorry My bad for the incorrect query.

Correct Query for which the result is not showing up is as follows:

Price: DLookUp(Format([SvcDate],"mmm"),"[BNE_Subbie_Prices_Table]","[RouteNo]='" & [Subbie_Clean_Data].[RouteNo] & "'" & " [Type]='" & [Subbie_Clean_Data].[Type] & "'" & " [Road_Air]= '" & "[Subbie_Clean_Data].[Road_Air]" & "'")

[June7]

That is not a query, it is just one expression used in query.

Do not put [Subbie_Clean_Data].[Road_Air] within quote marks.

Still not using the AND operator. PLEASE review my example in post 4.

[irfanparbatani]

That is not a query, it is just one expression used in query.

Do not put [Subbie_Clean_Data].[Road_Air] within quote marks.

Still not using the AND operator. PLEASE review my example in post 4.

----------------------------------------------------------------------------

Still no luck My new query is as follows:
Price: DLookUp(Format([SvcDate],"mmm"),"[BNE_Subbie_Prices_Table]","[RouteNo]='" & [Subbie_Clean_Data].[RouteNo] & "'" AND [Type]='" & [Subbie_Clean_Data].[Type] & "'" AND [Road_Air]= '" & [Subbie_Clean_Data].[Road_Air] & "'")

Still no prices is coming up

[June7]

Extra quote marks in front of each AND. Try:

Price: DLookUp(Format([SvcDate],"mmm"), "[BNE_Subbie_Prices_Table]", "[RouteNo]='" & [Subbie_Clean_Data].[RouteNo] & "' AND [Type]='" & [Subbie_Clean_Data].[Type] & "' AND [Road_Air]= '" & [Subbie_Clean_Data].[Road_Air] & "'")

[irfanparbatani]

I tried the same still no luck

[June7]

The syntax is correct and works for me.

If you want to provide db for analysis, follow instructions at bottom of my post.