Query that combines columns when a specific field is the same

[dubcomesaveme]

Good Evening,

I have been scratching my head for ages over this..I know it is possible because I managed it, but I then broke the database and had to roll back to an earlier version and for some reason I cannot figure it out again. So any help would be greatly appreciated

I have a query that pull all data that was input for a specific day. The Data comes out as follows (It is input 3 times a day for each Batch)

Batch | Tank | Feed Type 1 | Feed Qty 1 | Feed Type 2 | Feed Qty 2 | Total Feed | Mortality

SPD1 | G1A | Grow Pro 1.4| 10 | Grow Pro 2.0| 5 | 15 | 1
SPD1 | G1A | Grow Pro 1.4| 10 | Grow Pro 2.0| 5 | 15 | 2
SPD1 | G1A | Grow Pro 1.4| 10 | Grow Pro 2.0| 5 | 15 | 3
SPD2 | G1B | Grow Pro 1.4| 5 | Grow Pro 2.0| 10 | 15 | 1
SPD2 | G1B | Grow Pro 1.4| 5 | Grow Pro 2.0| 10 | 15 | 1
SPD2 | G1B | Grow Pro 1.4| 5 | Grow Pro 2.0| 10 | 15 | 3




I would like it to just show the following that goes into a daily report:

SPD1 | G1A | Grow Pro 1.4| 30 | Grow Pro 2.0| 15 | 45 | 6
SPD2 | G1B | Grow Pro 1.4| 15 | Grow Pro 2.0| 30 | 45 | 5

Last time I had the Feed Qty and Mortalities all tallied up using SUM, but I cannot for the life of me work out how I assimilated everything into one row.

Thank you in advance for any help.

[Micron]

Your major issue is that you have designed this table (assuming the query view is representative of a table) as you would a spreadsheet. That is evident because you are placing several similar fields across the same record. A db table should be 'tall' and not 'wide' like a spreadsheet. Batch, FeedType, Qty, Mortality are probably all that should be in the table. You probably need a supporting table to list Feed Types and the PK from that table would be in the other table as a foreign key. You ought to research normalization if that's not making sense.

Since you are trying to sum several fields, I suspect you'll need separate queries to get those counts/totals individually and then link them in in a final query. Afraid I'm guessing a bit because I wouldn't have designed the table that way in the first place. Does that sound like what you had previously done?

[isladogs]

I agree that the table structure is badly designed and with the suggested changes to improve it.
Also
1. Where do Feed_Type3 and Feed_Qty3 come from?
2. You have the Morts Sum column twice

[June7]

Looks like a simple aggregate query to me. Following produces output shown for given sample.

SELECT Batch, Tank, Feed_Type1, Sum(Feed_Qty1) AS SumOfFeed_Qty1, Feed_Type2, Sum(Feed_Qty2) AS SumOfFeed_Qty2, Sum(TotalFeed) AS SumOfTotalFeed, Sum(Mortality) AS SumOfMortality
FROM SampleData
GROUP BY Batch, Tank, Feed_Type1, Feed_Type2;

Why is there a DLookup for the Batch?

You don't show Feed_Type3 and Feed_Qty3 in sample data even though they are in query design. Mortality is not in design but Morts and Molts are.

Is TotalFeed a calculated field?