Query to compare prices.

[odin1701]

I'm not quite sure how to go about this. I am wanting to make a query that compares the price of one part to the price of various other parts in a matrix. Example:

60 70 80
10 $20 $25 $30 $40
20 $30 $25 $30 $40
30 $20 $25 $30 $40
40 $25 $25 $30 $40
50 $40 $25 $30 $40

So Part 10 is $20, where part 60 is $25, 70 is $30, etc. I would like to be able to make a form/report from it and make it so it could be updated. Basically you could add additional rows or columns (columns would be limited to a certain number).

So if I wanted to remove part 80 and add part 90 I could do that in the form - the query could be based off a table setup like a matrix is what I was thinking but not sure how to quite do this.

 

[spikepl]

You are thinking Excel. Access isn't Excel. You can DISPLAY the data like you are showing, but internally it would be stored in a different way.

To actually be able to edit stuff like you want, is a bit tricky. You can display it using a crosstab query very simply, but that won't be editable.

So:
1. you handbuild your display out of eg textboxes, feed the data to them and then collect it
2. you can perhaps use the ListView control.
3. you can drop the idea and do something else
4. some other reader comes up with some brilliant idea

 

[odin1701]

I got it to work by building another table to store the comparison info, without keys, and just made a query that references to another table a bunch of times to get the relevant data. Inelegant, but it works and you have to manually setup the report using a form I made to edit this new table, but...well it works. I couldn't find a way to do it otherwise either.