Solved Query criteria from different fields (1 Viewer)

[mistyinca1970]

OK, I am stumped on this one. I have a project in which I will be using a form's combo boxes to provide the criteria in a query in order to generate a report. My problem is that I have a table that has multiple year values. Here's the table:

I want to have a combo box direct to one of the annual Distributions. I can do this fine, if I'm directing the criteria of a field to a combo box, but how do I write the query to look in one of either of these fields (2017, 2018, 2019... etc).

Basically, I would have a form with a combo box that has each of these distribution years to be selected.

SELECT tblDistribution.City, tblDistribution.[2021Distribution], [AvgOfDistribution]*[2021Distribution] AS DistributionAmt
FROM qrySysEnhancDistribution INNER JOIN tblDistribution ON qrySysEnhancDistribution.EffectiveZone = tblDistribution.EffectiveZone;

This part here:

tblDistribution.[2021Distribution]

is what I want changed by the combo box option.

 

[bastanu]

What you have is a bad design, you should search and read about normalization and why it is so important to proper database design. What you can do at this point is to normalize your data using a union query:

SELECT tblDistribution.City, tblDistribution.[2017Distribution] As Distribution,2017 As DistributionYear From tblDistribution
Union
SELECT tblDistribution.City, tblDistribution.[2018Distribution] As Distribution,2018 As DistributionYear From tblDistribution
Union
SELECT tblDistribution.City, tblDistribution.[2019Distribution] As Distribution,2019 As DistributionYear From tblDistribution
Union
...

Now use this normalized query as the source of your report using the new DistributionYear with your combo.

Cheers,

 

[The_Doc_Man]

Let's first mention something that needs to be said. That is not a table. It is a spreadsheet masquerading as a table. Your data set is not normalized. May I respectfully suggest that you read up on Database Normalization? If you search THIS forum (which IS, after all, about databases) you can look for "Normalization" - but if you search the web, you have to use "Database Normalization" because there are other types of normalization. When you do this lookup, start with .EDU sites because often the .COM sites are selling something (even selling services) and might have some distractions. Once you feel more confident, you can look at the .COM sites knowing that they might be pushing proprietary items.

You have painted yourself in a corner because of that structure. I won't say you CAN'T do what you want, but it will be complex, because you have to dynamically change the .RowSource or .Recordsource (change field names) of anything that has to come from this.

IF your table was
CITY (text), DISTYEAR (integer), DISTRIBUTION (either Currency or Double or scaled LONG)

Then you could write a query to get particular distribution using: .... WHERE CITY = "citycode" AND YEAR = chosenyear...

If you have combo boxes for those, they might resemble

"... WHERE CITY = '" & Me.cboCITY & "' AND YEAR = " & cboYEAR & " AND ..."

As you have it written, though, you have to actually rebuild the SQL to change selected field names, which means string manipulation and reloaded .ROWSOURCE or .RECORDSOURCE (as appropriate) and forcing the control to then .REQUERY to get values based on the change.

EDIT: I see Vlad finished typing just before I did, but with generally the same theme. Fast fingers, Vlad!

 

[mistyinca1970]

What you have is a bad design, you should search and read about normalization and why it is so important to proper database design. What you can do at this point is to normalize your data using a union query:

SELECT tblDistribution.City, tblDistribution.[2017Distribution] As Distribution,2017 As DistributionYear From tblDistribution
Union
SELECT tblDistribution.City, tblDistribution.[2018Distribution] As Distribution,2018 As DistributionYear From tblDistribution
Union
SELECT tblDistribution.City, tblDistribution.[2019Distribution] As Distribution,2019 As DistributionYear From tblDistribution
Union
...

Now use this normalized query as the source of your report using the new DistributionYear with your combo.

Cheers,

I have some fields not included in the pic of this table for privacy reasons; "City" is one of them. I have intended to use a query instead of a table in this particular query. That is the part I am having trouble with.

Let's first mention something that needs to be said. That is not a table. It is a spreadsheet masquerading as a table. Your data set is not normalized. May I respectfully suggest that you read up on Database Normalization? If you search THIS forum (which IS, after all, about databases) you can look for "Normalization" - but if you search the web, you have to use "Database Normalization" because there are other types of normalization. When you do this lookup, start with .EDU sites because often the .COM sites are selling something (even selling services) and might have some distractions. Once you feel more confident, you can look at the .COM sites knowing that they might be pushing proprietary items.

You have painted yourself in a corner because of that structure. I won't say you CAN'T do what you want, but it will be complex, because you have to dynamically change the .RowSource or .Recordsource (change field names) of anything that has to come from this.

IF your table was
CITY (text), DISTYEAR (integer), DISTRIBUTION (either Currency or Double or scaled LONG)

Then you could write a query to get particular distribution using: .... WHERE CITY = "citycode" AND YEAR = chosenyear...

If you have combo boxes for those, they might resemble

"... WHERE CITY = '" & Me.cboCITY & "' AND YEAR = " & cboYEAR & " AND ..."

As you have it written, though, you have to actually rebuild the SQL to change selected field names, which means string manipulation and reloaded .ROWSOURCE or .RECORDSOURCE (as appropriate) and forcing the control to then .REQUERY to get values based on the change.

EDIT: I see Vlad finished typing just before I did, but with generally the same theme. Fast fingers, Vlad!

I do have city in my table. I did not include it in the previous pic for privacy reasons... here is the table in design mode:

 

[Minty]

To try and summerise what the others have said with a really simple example;
You should have a DistributionYear In your table, and DitributionAmount. When 2022 arrives, you simply add more records, not more fields and break everything.

 

[bastanu]

Include all the fields you need in the union query then your result would look like what theDocMan and Minty are showing you:

SELECT Zone,Order,City,EffectiveZone, 2017Distribution As Distribution,2017 As DistributionYear From tblDistribution
Union
SELECT Zone,Order,City,EffectiveZone, 2018Distribution As Distribution,2018 As DistributionYear From tblDistribution
Union
....

Cheers,

 

[mistyinca1970]

What you have is a bad design, you should search and read about normalization and why it is so important to proper database design. What you can do at this point is to normalize your data using a union query:

SELECT tblDistribution.City, tblDistribution.[2017Distribution] As Distribution,2017 As DistributionYear From tblDistribution
Union
SELECT tblDistribution.City, tblDistribution.[2018Distribution] As Distribution,2018 As DistributionYear From tblDistribution
Union
SELECT tblDistribution.City, tblDistribution.[2019Distribution] As Distribution,2019 As DistributionYear From tblDistribution
Union
...

Now use this normalized query as the source of your report using the new DistributionYear with your combo.

Cheers,

There are only 30 rows in this table. It would not be difficult to parse the year fields into different tables, if that is what you are suggesting would be the better way...

 

[bastanu]

You definitively do not want different tables for each year if that is what you mean; the union query that I suggested normalizes you table by taking the individual yearly distribution presented as separate columns and lumping them together into one {Distribution] field and also adding the year value to a new DistributionYear field. Ideally you would have designed the tblDistribution table like this from the beginning and\or you could do it now. With 30 records using the union query as a substitute for the table in your report will not be an issue. So it is your choice to go ahead and change the table (including any other objects that depend on it) or use the union query approach.

Cheers,

 

[mistyinca1970]

To try and summerise what the others have said with a really simple example;
You should have a DistributionYear In your table, and DitributionAmount. When 2022 arrives, you simply add more records, not more fields and break everything.

Thank you for the clarification. I need to think about this and how to get the information into that format.

This project is my attempt at converting a huge, clunky spreadsheet (that has been passed down through multiple hands) into an elegant format that will produce the annual report with basically a couple of clicks. For the most part, I have accomplished that. The exception being this year issue.

I receive the annual distribution percentages each year from another organization, in spreadsheet format. 2 columns: the city, and the percentage. It seems then what I need to do is use a make table query to get that information into that format. I'll need to think on that for a few minutes.

Thanks,

 

[mistyinca1970]

Thank you.
I used a combo of MakeTable, Append, and Update Queries to put the data into a new table... I will report back if I am successful in writing my query to accomplish what I need.

 

[Uncle Gizmo]

. I will report back if I am successful in writing my query to accomplish what I need.

I suggest you have a look in the attached database. I describe the functionality of the Attached dB in the YouTube Video:-

tblDistribution - Nifty Access​

you can see how to use my transpose tool to shift your Excel type Fields into a table more suitable for using MS Access.

More information about "Excel in Access" on my blog here:-

Excel in Access​

Excel in Access – Nifty Access

www.niftyaccess.com

in the YouTube Video, I mentioned an example of how to get a Report to grab data from a Form, that's in my blog here:-

Passing Information to a Report​

Pass Info In to a Report - Nifty Access

Passing Information to a Report Often, I see examples where people pass information between Forms and Reports using Opening Arguments "openArgs" ... I did this myself until I happened upon an article, I think it was from "Smart Access" which explained a very satisfying way of Passing information...

www.niftyaccess.com

 

[Gasman]

I'd always have an auto number field in each table as well?

 

[mistyinca1970]

This solved my problem. The new table (and yes, I added the autonumber primary key afterward) fixes it. Added benefit is two queries that are used in this report can run from the same combo box!

SELECT tblDistribution.DistYear, tblDistribution.Order, tblDistribution.ZoneOrder, qrySysEnhancDistribution.Zone, tblDistribution.City, tblDistribution.Distribution, [AvgOfDistribution]*[Distribution] AS DistributionAmt
FROM qrySysEnhancDistribution INNER JOIN tblDistribution ON qrySysEnhancDistribution.EffectiveZone = tblDistribution.EffectiveZone
WHERE (((tblDistribution.DistYear)=[Forms]![frmSysEncReport]![cboReportYear]));

Thank you,