Single table, multiple returns on a query based on user choice (1 Viewer)

[jarednickle]

I have a single table. Each record has 1 date field with about 50 numerical fields. Not all dates have data (average record frequency of once a week). I'd like to have a form that allows the user to select 3 dates based on available dates from the table (this part I can do) and then grabs the relevant fields for the 3 chosen dates. Essentially I want to summarize basic numbers for the dates in question and compare them against each other (am I increasing numbers of customers or decreasing? etc). I can't seem to get a query to grab just the relevant fields from the single table for multiple dates chosen by the user and put them all side by side for comparison.

 

[theDBguy]

Hi. Welcome to AWF!

Is it possible that you may have an improper table structure that's why you're having problems pulling data out of your table?

 

[Pat Hartman]

Without an understanding of your data, it is hard to judge but given your explanation and the number of columns in the table, it seems like the table should be four columns
AutonumberID
DataDate
DataFieldName
DataValue

So, instead of having 1 row with 50 columns, you end up with 50 rows with 4 columns. Or more commonly, the rows would be sparse, meaning that there would only be rows for non-null or relevant Data Fields.

 

[GPGeorge]

I have a single table. Each record has 1 date field with about 50 numerical fields. Not all dates have data (average record frequency of once a week). I'd like to have a form that allows the user to select 3 dates based on available dates from the table (this part I can do) and then grabs the relevant fields for the 3 chosen dates. Essentially I want to summarize basic numbers for the dates in question and compare them against each other (am I increasing numbers of customers or decreasing? etc). I can't seem to get a query to grab just the relevant fields from the single table for multiple dates chosen by the user and put them all side by side for comparison.

As others have pointed out, this sounds more like an Excel worksheet than an Access table. Excel and Access are different tools, and that difference is crucial to getting the results you need with the most efficient approach possible, rather than creating a convoluted "wad of code" to manipulate poorly designed data.

Let's start, therefore, at the beginning, rather than at the "band-aid" stage. Show us the table, please, and explain what's in it.