Solved Column divided into four other columns (1 Viewer)

[tihmir]

Hello, I have a query in which I want to divide a number from one column into four other columns, i.e. bus stations (100) -> first quarter (25), second quarter (25), third quarter (25), fourth quarter (25)

 

[ebs17]

I have a query in which I want ...

Too little information. Or is the query so famous that I need to know it without showing?

 

[CJ_London]

best guess - have 4 columns (Q1,Q2,Q3,Q4) with a calculation dividing the 100 by 4

after over 240 posts you should know by now the requirements for asking a question of this nature is to provide some context, some example data and the result required for that calculation.

 

[MajP]

SELECT Table1.FieldName,
       [FieldName]/4 AS [First Quarter],
       [FieldName]/4 AS [Second Quarter],
       [FieldName]/4 AS [Third Quarter],
       [FieldName]/4 AS [Fourth Quarter]
FROM Table1;

qry2 qry2

FieldName	First Quarter	Second Quarter	Third Quarter	Fourth Quarter
10​	2.5​	2.5​	2.5​	2.5​
11​	2.75​	2.75​	2.75​	2.75​
50​	12.5​	12.5​	12.5​	12.5​
100​	25​	25​	25​	25​
133​	33.25​	33.25​	33.25​	33.25​
200​	50​	50​	50​	50​

 

[tihmir]

Too little information. Or is the query so famous that I need to know it without showing?

best guess - have 4 columns (Q1,Q2,Q3,Q4) with a calculation dividing the 100 by 4

after over 240 posts you should know by now the requirements for asking a question of this nature is to provide some context, some example data and the result required for that calculation.

I sorry for the incomplete information. I describe in more detail..
That is my request:

SELECT qryAnnualPlan.CodeObject, qryAnnualPlan.ObjectType,Count(qryAnnualPlan.ObjectType) AS CountOfObject, tblCodeObject.Multiplicity
FROM qryAnnualPlan INNER JOIN tblCodeObject ON qryAnnualPlan.CodeObject = tblCodeObject.CodeObject
GROUP BY qryAnnualPlan.CodeObject, qryAnnualPlan.ObjectType, tblCodeObject.Multiplicity;

I need column "CountOfObject" to be multiplied by column "Multiplicity" and then the amount received divide into four new columns.
I hope this information is more detailed. I apologize again!

 

[MajP]

For ease I do it in two steps. Save that query above then do what I showed. So call your query
qryPlanCount

Select ...., CountOfObject * multiplicity / 4 as 1stQuarter,  CountOfObject * multiplicity/ 4 a 2ndQuarter, ... from qryPlanCount

 

[tihmir]

For ease I do it in two steps. Save that query above then do what I showed. So call your query
qryPlanCount

Select ...., CountOfObject * multiplicity / 4 as 1stQuarter,  CountOfObject * multiplicity/ 4 a 2ndQuarter, ... from qryPlanCount

MajP, thank you for your help and your time.
I am concerned about the case where the sum obtained from the multiplication of the two columns is odd. For example 75.
It means 75 to be random distributed in four new columns - column1 - 19, column2 - 18, column3 - 19, column4 - 19, the total sum of which is 75

 

[ebs17]

? 75 \ 4 + 1, 75 - 3 * (75 \ 4 + 1)
 19            18

The first calculation expression for the three front columns, the second for the fourth.

 

[MajP]

Why is column 2 18 and not the last column?

 

[MajP]

Removing the stupid logic that the remainder is not applied to the second and instead the last???

Public Function GetQuarter(Val As Long, WhichQtr As Integer) As Double
  Dim rtn As Double
  Dim qtr As Long
  Dim rmdr As Long
  qtr = Val \ 4
  rmdr = Val Mod 4
  rtn = qtr
  If WhichQtr > 0 And WhichQtr < 5 Then
    If WhichQtr <= rmdr Then rtn = rtn + 1
  End If
  GetQuarter = rtn
End Function

 

[tihmir]

Why is column 2 18 and not the last column?

I just wrote it like that without any particular intention.

 

[arnelgp]

select ((CountOfObject * Multiplicity)\4) As Q1,
((CountOfObject * Multiplicity)\4) As Q2,
((CountOfObject * Multiplicity)\4) As Q3
(CountOfObject * Multiplicity) - (((CountOfObject * Multiplicity)\4)*3) As Q4

 

[tihmir]

Thanks everyone for your help - ebs17, CJ_London, MajP,

arnelgp​

I appreciate it!