Selecting A Column from a Table

[BigAl]

Hi,

although I was intensely searching the web, I couldn't find an answer for the following problem:

I have a set of different price lists organized in a table (articles as rows, different prices as columns) and I have a set of different customers in a table. Now I want to generate a query combining a specific customer with a specific price list - in other words, select a specific column from the price list table.

As I am a bloody beginner in VBA and SQL I would appreciate if answers could include Code examples.

Thank you

Al

[Stwange]

I need a bit more information, does the customer have a field telling it which of the price lists it should use? Or will you be changing this each time?

eg. is there a Customer.[priceList] which is identical to the field name you want to return from product?

Please provide an example.

[BigAl]

I need a bit more information, does the customer have a field telling it which of the price lists it should use? Or will you be changing this each time?

eg. is there a Customer.[priceList] which is identical to the field name you want to return from product?

Please provide an example.

The relation customer to price list is fixed, so every time the customer gets the same price list. the "Customer.[priceList]" could be implemented.

In terms of example, what do you need? a dummy data base? just some described scenarios?

thanks for your interest in helping me

Al

[Stwange]

The relation customer to price list is fixed, so every time the customer gets the same price list. the "Customer.[priceList]" could be implemented.

In terms of example, what do you need? a dummy data base? just some described scenarios?

thanks for your interest in helping me

Al

I'm not sure if this is possible with one query.
you could do several queries (ie, one for each price), and then use code to select which one is run.
Or, possibly a better idea, you could have a combobox allowing you to select a customer, and then using the information in this, populate a two-column listbox with ARTICLE and PRICE.
Which would be better? Or, ideally, tell me what you want to do with this query once it's done, as it may be easier just to do whatever that is directly.

It may be tomorrow before I can reply again.

[BigAl]

I'm afraid the thing is not quite so easy. First we are talking about approx. 500 individual prices and second, even worse, not all price lists contain all prices.

I like the second option, the combobox, although, I admit, I am not sure how to do this.

And answering your question, the resulting query should be used to generate a report for printing out a price list.

Thank you really for your help

Al

[Stwange]

I'm afraid the thing is not quite so easy. First we are talking about approx. 500 individual prices and second, even worse, not all price lists contain all prices.

I like the second option, the combobox, although, I admit, I am not sure how to do this.

And answering your question, the resulting query should be used to generate a report for printing out a price list.

Thank you really for your help

Al

First, create a combobox (call it cmbCustomer) and make it whatever size you want. Second, create a listbox (called lstPriceList), and go into its properties and change column count to 2 In the combobox properties, set the Row/Source Type to "Table/Query" and change the RowSource to:

'"SELECT name FROM Customer ORDER BY name;" (or whatever your field and table is called)

Then, still in the properties, go to the ON EVENT tab and click AFTER UPDATE -> click the "..." and click CODE GENERATOR.
In that code that comes up, write:

Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT price FROM Customer WHERE name = '" & cmbCustomer.Value & "';")
lstPriceList.RowSource = "SELECT article_name, " & rs!price & " FROM Article;"
lstPriceList.Requery

Then try opening the form, and selecting a particular customer. If the listbox doesn't update automatically, try clicking off the combobox. I haven't tested this code, so if you have problems, please let me know. Good luck.

[BigAl]

Thanks for all your help. I overcame the problem with two night shifts, reorganizing the data completely. For the moment it works just fine.