Help returning query row fields as columns (1 Viewer)

[spet]

Below are the results of my query calculating a Main Patron weight and a Sub Patron Weight (two totally different calculations). What I'm trying to attempt is to show both the MainPatron field and the SplitPatron field in the same column named "Patron". I would also like to do the same with MainWeight and SplitPatron1MonthlyWeight, showing them as one combined field named "Weight". Any help is appreciated!


SELECT qryMonthlyPatronSubPatronWeights.MainPatron, qryMonthlyPatronSubPatronWeights.MainWeight, qryMonthlyPatronSubPatronWeights.SplitPatron1, qryMonthlyPatronSubPatronWeights.SplitPatron1MonthlyWeight
FROM qryMonthlyPatronSubPatronWeights;

 

[spet]

I suck at trying to explain what I'm doing! Sorry.


Ok, so my query returns 4 different fields. PatronNo, PatronWeight, SubPatronNo, SubPatronWeight. PatronWeight and SubPatronWeight are calulated fields (two different types of calculations).



Is it possible to somehow have PatronNo and SubPatronNo returned as one field call Patron and then PatronWeight and SubPatronWeight return as Weight?


I hope that makes more sense?


Current:

PatronNo PatronWeight SubPatronNo SubPatronWeight
100 25,000 1001 5,000
200 10,000 2042 2,000


What I'd like to do:
Patron Weight

100 25,000
1001 5,000
200 10,000
2042 2,000


Sorry for the confusion.

 

[MajP]

Can be done with a union query.
https://www.w3schools.com/sql/sql_union.asp
Select the first two columns union with the second two columns.

 

[plog]

A UNION may be a hack on a hack. Can you show us what the SQL of qryMonthlyPatronSubPatronWeights.SplitPatron1MonthlyWeight looks like? Ultimately I would like to see the tables where the data resides.

 

[spet]

There are queries built on queries for multiple types of calculations, the union query worked but I don't want information if it is 0 or null.


SELECT qryMainPatronWithSubs.MainPatron, qryMainPatronWithSubs.MainWeight, qryMainPatronWithSubs.SplitPatron1, ([qryCalcSubPatronWeightByPercentB].[Split1PercentWeight]+[qryCalcSubPatronWeightPerDayB].[Split1TotalPerDayWeight]+[qryCalcSubPatronWeightPerPickUpB].[Split1TotalPerPickUpWeight]+[Split1SetMonthWeight]) AS SplitPatron1MonthlyWeight, qryMainPatronWithSubs.SplitPatron2, ([qryCalcSubPatronWeightByPercentB].[Split2PercentWeight]+[qryCalcSubPatronWeightPerDayB].[Split2TotalPerDayWeight]+[qryCalcSubPatronWeightPerPickUpB].[Split2TotalPerPickUpWeight]+[Split2SetMonthWeight]) AS SplitPatron2MonthlyWeight, qryMainPatronWithSubs.SplitPatron3, ([qryCalcSubPatronWeightByPercentB].[Split3PercentWeight]+[qryCalcSubPatronWeightPerDayB].[Split3TotalPerDayWeight]+[qryCalcSubPatronWeightPerPickUpB].[Split3TotalPerPickUpWeight]+[Split3SetMonthWeight]) AS SplitPatron3MonthlyWeight
FROM qryCalcSubPatronWeightSetMonthB RIGHT JOIN (qryCalcSubPatronWeightPerPickUpB RIGHT JOIN (qryCalcSubPatronWeightPerDayB RIGHT JOIN (qryMainPatronWithSubs LEFT JOIN qryCalcSubPatronWeightByPercentB ON qryMainPatronWithSubs.MainPatron = qryCalcSubPatronWeightByPercentB.MainPatron) ON qryCalcSubPatronWeightPerDayB.MainPatron = qryMainPatronWithSubs.MainPatron) ON qryCalcSubPatronWeightPerPickUpB.MainPatron = qryMainPatronWithSubs.MainPatron) ON qryCalcSubPatronWeightSetMonthB.MainPatron = qryMainPatronWithSubs.MainPatron;

 

[plog]

Yeah, you're just building this super inefficient tower of data and now instead of simply walking into the ground floor and getting what you want you are incurring the cost of riding an elevator all the way to the top penthouse and extracting your data from there.

That last query you posted still doesn't show where the actual data you want resides. You keep passing MainWeight and SplitPatron1 up the line. If you want a query with the results you demonstrated in post #3 you should try and extract it as close to the table it is housed in as possible.

 

[spet]

Thank you all for your help. I was able to back up these queries and use a union query closer to the tables.
I'm new at this! I appreciate all the knowledge.