Passing Multiple Values To Query With Multi Select Box

[Access4444]

I trying to solve for data having multiple values in one given year. The data/table is as follows...I have years 2000-2016 and say they're for store credit. So each person or account has one record associated with it and they all have that group of years 2000-2016. I have a multi select box on my user form that has all the years and an "All" selection. When the user holds control+click they can select multiple years and that is passed to the query criteria via VBA. In order to provide the year for the query to find I have a formula in a base query that says (see below) and that allows me to pull records for a given persons year, however when someone has credit it both 2001 and 2001 or 2001,2002,2003 the formula only gives a value of 2001, the first instance it's found. This causes the query results to be incorrect because if I query 2002, 2003, 2004 then I lose the record that had a person in 2002 and 2001 because they aren't satisfying the criteria 2002.



AnyYearCredit: IIf((([2004]>0)),"2004",IIf((([2005]>0)),"2005",IIf((([2006]>0)),"2006",IIf((([2007]>0)),"2007",IIf((([2008]>0)),"2008",IIf((([2009]>0)),"2009",IIf((([2010]>0)),"2010",IIf((([2011]>0)),"2011",IIf((([2012]>0)),"2012",IIf((([2013]>0)),"2013",IIf((([2014]>0)),"2014",IIf((([2015]>0)),"2015",IIf((([2016]>0)),"2016","False")))))))))))))

So what I need is a way ideally with VBA where a user can select multiple years from a selection box and the query looks at fields [2001],[2002]...[2016] for values > 0

Thanks!!

[ranman256]

Multi select listbox are not functional for queries.
what you want is a single select with a double-click to add item to a 'find' table. All
items in the find table, when joined to the data table, will pull only those values.

[pbaldy]

So next year you'll add a 2017 field, and have to add it to all queries, forms and reports? You have a spreadsheet there, not a database. Research normalization:

Fundamentals of Relational Database Design -- r937.com

[Access4444]

pbaldy I am familiar (not yet mastered) normalization. Typically I would never set up my tables this way, however as the process and data stands, the team will be receiving this excel spreadsheet monthly and then would upload it to the database where reports and queries are pre-built for them. Per the Fundamentals article, I would ideally have one column for Year and a single record for each year there's a value greater/less 0, but instead I have one record and columns of years in the raw data. So yes, I will have to adjust the database in future years (also not ideal). Do you have a recommendation?

Ranman256 - how would I implement that? Would that be the users double-clicking the value and that event passing a ">0" into the query parameter?

thanks for the help. I am open to ideas.

[pbaldy]

So yes, I will have to adjust the database in future years (also not ideal). Do you have a recommendation?

You know your situation better than I, but I'd probably normalize the data as it gets imported.

[Access4444]

Is there a way to automatically generate those additional rows or records. So, if there is a record (1 row) that has values in 2010, 2011 and 2012 in excel (pre-upload) I need to create 3 rows/records from that 1 and place a year column and amount column and somehow get the years and amounts on the correct lines. Is there a macro idea that can automate this or possibly an access idea that can assist in normalizing the data and then I can query a normalized query.

[Bulzie]

So you have a customer record that has all the year fields on it:

Name, 2001, 2002, 2003...
Jane, Yes, No, Yes, ....

And if you pull up Jane's name, you want it to show which Year fields were marked Yes (2001 and 2003)?

[pbaldy]

A union query can normalize the data, or vba code could be written to do it. The query is probably simplest.

[Access4444]

Yes. Pulling one customer shows the entire record with years 2010-2016 and I have a query with yes and no. The end goal is a form with a combo like option to pull multiple years and the form calculates total amounts and customers for that group of years.

[Bulzie]

So can you explain again what you want from the data. Is it a report, query, etc. And what will show on this output, CustomerName, each Year that was marked a Yes? You said something about a calculation or amount that goes with a year? Can you give an example of an input record with the years and what the output should look like?

[Access4444]

I have attached an example. I know normalizing the data is what I should do. That would take the avg. 80,000 row excel file and multiply it by 5+, but it would make this much easier. I just don't know a quick way of doing that.





example.zip

[pbaldy]

I'll get out of the way.