linking to column headers

[jtkjames]

Hi all,

I am having a bit of trouble with a query I want to make, such that I can't actually figure out how to do it (tried trial and error, and about an hour of googling).

Basically I have a Select Query with two tables joined. One table has a series of rows with a "sale date" field and the other is a matrix of sale dates (in month) versus prices.

I have attached a simplified example. If anyone can help me build a query that "finds" the Standard Price at Date of Sale I would be very grateful. The attached DB is an Access 2002 version for reference.

Thanks!

James

 

[Galaxiom]

Your data is extremely denormalized and will be a nightmare to work with. How much of the data model do you control yourself and how much is inflicted upon you?

If you can change the table structures then start reading about "normalization" by searching the forum for that term.

 

[jtkjames]

Your data is extremely denormalized and will be a nightmare to work with. How much of the data model do you control yourself and how much is inflicted upon you?

If you can change the table structures then start reading about "normalization" by searching the forum for that term.

unfortunately both tables are linked to CSV sheets on a network drive (archaic but required) which in turn are produced by some 1990s POS that they use.

...so no modification possible there. But a query could produce a modified equivalent to a table?

I have read a bit about normalisation, but can't see how it relates to this.Grouping the product types (such that product "A" is in one table / query, "B" in another etc.) would be good if there is only three, but the horror in my actual db is that I am working with 60 months (back to 2005) and about 2500 rows (IDs), some dupes but with different other criteria in the table (manufacturer), combined with some custom functions based on modules I didn't write and I'm not allowed to modify.

(9am-5pm = aspirins, 5pm-9am = vodkas xd)*

 

[Galaxiom]

I would normalize the StandardPrices data by using a Union query.

SELECT
ItemID, "Jan10" As PriceDate, Jan10 as Price
FROM tblStandardPrices
UNION ALL
SELECT
ItemID, "Feb10", Feb10
FROM tblStandardPrices
UNION ALL
etc

I haven't included the conversion to Date datatype but I would do that too.

 

[jtkjames]

I would normalize the StandardPrices data by using a Union query.

SELECT
ItemID, "Jan10" As PriceDate, Jan10 as Price
FROM tblStandardPrices
UNION ALL
SELECT
ItemID, "Feb10", Feb10
FROM tblStandardPrices
UNION ALL
etc

I haven't included the conversion to Date datatype but I would do that too.

I never thought of that (d'oh)

One final Q - Will it work unioning 60 months (5 years worth) - which is the data I have to work with? IIRC there is a limit - I could try but I don't want to get half way through and find a "query too complex" error

 

[jtkjames]

OK i have been reading and it seems the DLOOKUP function might help me overcome this?

I tried adding

Expr1: DLookUp("["&SaleDate&"]", "tblStandardPrices", "")

but this doesn't return anything. Is this a fundamental problem, or just my bad syntax? I don't get an error or anything, just a blank column lol

 

[Galaxiom]

Sixty months might exceed some limit but I think it would be OK. Sometimes the 255 fields bites before it is expected because they can get counted twice.

Here are the limits for A2007
http://blogs.msdn.com/b/access/archive/2006/06/05/access-2007-limits.aspx

You would want to generate the query in VBA.

The DLookUp is not the way to go. The query using DLookup like this would probably take longer to run than it would to type the union query because each time it is used is essentially a query going on behind the scenes.

The third argument of a DLookUp is in the form of:
"fieldname=somevalue"
(somevalue can be a reference.)

 

[jtkjames]

Hi again

I've tried to make a union all for 60 months and its come back Query Too Complex. Most I can do is 7. I've tried nesting the unions but still no avail, even so - 7x7 = 49 at the most via nesting.

I know it's bad practice - but I've got the Dlookup to work on my example, but for some weird reason i can't apply it to my actual DB (spent all day trying to figure out why!) so thats a dead end all round.

Basically I've made a form based on the query, and have a new text box with control source:

(by the way, the date field in the StandardPrices table is in the form m_yyyy)

=DLookUp("""" & Format([SaleDate],"m_yyyy") & """","qryGetLatestStandardPrices","[ProdID]="&[ProductID])


but weirdly it returns the [SaleDate] formatted as m_yyyy. This is completely weird and undocumented anywhere - could I have found a new bug?

alternateively, any other suggestions to normalise the horrendous data?

thanx

 

[Galaxiom]

The first argument in your DLookUp is [SaleDate] field formatted as m_yyyy. It sees SaleDate and takes this as the field it is mean to use even though it is inside an expression.

To use it as the fieldname you would have to convert it to a string first and build the DLookUp in VBA. The DLookUp cannot be allowed to see a naked reference to an object in that string.

Another approach would be to open your table as a recordset in VBA and process it by stepping through the records. Each record would be processed in a loop that wrote the value from each field as a new record to the destination table or recordset.

I would certainly create a table rather than having to reprocess the data from the denormalised table every time.

Only guessing but I suspect the appends might be faster if you open the destination table as an append only recordset and add new records rather than execute an append query for every value.

 

[jtkjames]

thanks for you help with this one! I managed to get it to work using Dlookup. Still can't get it to transpose using unions - but at least I got summat working...