Create two columns from one, based on two different WHERE cause

[ili_sophia]

Create two columns from one, based on two different WHERE clause

Hello,

I have created a split form where it allows the users to select the date range and it will show in the data in the table below.

I need to create two new columns based on the field of Loading Quantity (kgs)

Both columns have to be grouped by date and the value has to be multiplied by 2.2046

The first column would look like this :

Select Sum([Loading Quantity (kgs)]*2.2046) AS Actual(Sample Machine)
FROM Dyeing
Where Machine = 'HT1' or 'HT2' or 'HT3' or 'HT4' or 'HT5' or 'HT6' or 'HT7' Group by Date

The second column:

Select Sum([Loading Quantity (kgs)]*2.2046) AS Actual(Mass Machine)
FROM Dyeing
Where Machine = 'HT8' or 'HT9' or 'HT10' or 'HT11' or 'HT12' or 'HT13' Group by Date

Query does not allow me to create the column as it is limited to either group or where cause.

How do i add this two columns into the table in the split form using vba

Also does split form table allow user input?

Thank you

 

[arnelgp]

you have to add Machine on the Group.
then create two query for your two column.

on the original query create a Left Join on those queries.

 

[plog]

You need a new table to categorize your [Machine] field.

MachineCategories
MachineCode, MachineCategory
HT1, Sample
HT2, Sample
HT3, Sample
...
...
HT8, Mass
HT9, Mass

Take one of those queries, bring that new table into your query, link via [Machine] to [MachineCode], SELECT and GROUP BY [MachineCode] and remove the WHERE clause.