Populating listbox on a form using multiple fields. (1 Viewer)

[bakkouz]

Hey guys,

Beginner here.

I have a table called Prod_DB, in this table i have a field called "Country" which stores country names, and then i have many other fields for years, ie: a field called "2009" which stores values (production in Kilograms) relating to the country in field "Country" and another field "2010" and field "2011" and so on.

So basically its a table which stores production numbers of fertilizer for certain countries by year.

I have a form "form1" which has a combo box used to select county names and then populate a list box based on the selection made.

now what i want to do and cannot figure out how to do it is how to create a similar combo for the years, since the values are stored in many fields.

to simplify the issue, i want the user to select a country (or countries) and select the desired year (or years), from the table Prod_DB, and then display a report containing the desired information.

I have attached the sample database.

thank you in advance.

 

[AccessMSSQL]

Well, first off you don't want to store your data this way. I would recommend changing your table structure. You should not have the years listed as separate fields. You really should have a relational table that has fields to store the country, the year, and production values. You will always be able to manipulate the data to display a report that has the years as columns but you should store your data in a table like this:

Table: ProductionValues
Country
Year
Total

Your data will look like this:
Brazil 2008 4700
Brazil 2009 3300
United States 2008 31000
United States 2009 41329
etc..for each country/Year