Access Lookup

[henrypotter]

Hi,

I am trying to play around with query but got stuck in fixing the bug.

Please see attachment. I am trying to link the color code from a table to another table which stores the definition of color code. However, it gives me duplicate results.

In Excel, this could be achieved by using the vlookup function. How do I do it in Access?

Thanks a lot!
Henry

 

[henrypotter]

Could anybody help? Thanks!

 

[boblarson]

1. get rid of the non-linked table in the query.

2. You shouldn't have tables that are date specific. Include a date field and fill it in.

 

[henrypotter]

Even I got rid of the link it is still showing 125 records. All I want is 5 record showing the Color.

Could you help please?

 

[boblarson]

I said to get rid of the table that WASN'T linked. When I do it (remove the 12/31/2008 table) then I get 5 records. If you remove the other table then you have to make sure that there is a link still between Color Code fields.

 

[boblarson]

And just so there's no wondering - see attached modified version of yours:

 

[henrypotter]

Thanks boblarson!

>2. You shouldn't have tables that are date specific. Include a date field and fill it in.

Why can't I have multiple tables for different dates? Sometimes I may need to track the particular car model prices from quarter to quarter.

How could I write VBA codes to perform the vlookup function?

Henry

 

[DCrake]

Firstly, a car is a car and will remain so, the price of the car may change each quarter as you suggested. Therefore what you need is a table that holds the information about those changes.

The consistant element is the PK of the car model.

VV09 = Vauxhall Vectra 2009 model

The first entry in you costs table would be (Price as new)

VV09 £12000 01/01/2009

Then the cost of the car reduces in March

VV09 £10000 01/03/2009

Again in June

VV09 £7500 01/06/2009


So now you have one table 3 records

You could now do a crosstab query using the model as the row heading
the date of the price change expressed as a quarter/Month/etc and the value as the data item.

If you had different tables for different price changes hwo would you compare one against the other? Different models may have price changes on different dates, so are you going to have a new table for each and every model and its relevant price change date? This is not normalisation.

Hope this explanation is clear enough for you to understand the need to normalise your data.

David

 

[henrypotter]

DCrake,

Each quarter, I will get a list of car data from my coworkers. The price of the car may change, it may be sold, or we purchased new cars. Since he gave me the file in Excel, I was thinking to store his files in separate tables. There are more data fields than this such as location, contact person, MSRP etc for each car. So I should be able to identify the exact same record of that car.

My initial plan is to create an unique field by may be merging a few fields to get a unique identifier and link tables from two quarters together. Then I can see the price change of the car and be able to know what's been sold (which would see a blank in MSRP), what have been manufactured(a new record)

Please let me know if you think this doesn't work. Thanks!

Henry