By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Selecting A Column from a Table

P: 4
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
Aug 16 '07 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 126
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.
Aug 16 '07 #2

P: 4
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
Aug 16 '07 #3

Expert 100+
P: 126
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.
Aug 16 '07 #4

P: 4
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
Aug 16 '07 #5

Expert 100+
P: 126
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:

Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Set rs = DBEngine(0)(0).OpenRecordset("SELECT price FROM Customer WHERE name = '" & cmbCustomer.Value & "';")
  3. lstPriceList.RowSource = "SELECT article_name, " & rs!price & " FROM Article;"
  4. 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.
Aug 17 '07 #6

P: 4
Thanks for all your help. I overcame the problem with two night shifts, reorganizing the data completely. For the moment it works just fine.
Aug 25 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.