Group By first 3 digits of 6 (1 Viewer)

[KDH]

Hey all, I am trying to put together a query that takes a field that has 6 digits and Group By the first 3 digits. This information is then displayed in a Form.

Ex.
Dept ID: 500104 Balance: 20
Dept ID: 500108 Balance: 30
Dept ID: 500204 Balance: 50
Dept ID: 515221 Balance: 20

Group By: 500 Balance: 100
Group By: 515 Balance: 20

I was thinking to do the criteria by 500 * or something but having trouble. This is also a Number Type NOT Text based. Anything thoughts would be appreciated!

 

[pbaldy]

Have you tried

Left(FieldName, 3)

 

[MSAccessRookie]

I can think of two choices for the groupings (depending on whether you like strings or numbers better). Either one should do what you are asking

SELECT int([Dept ID]/1000), sum(field2) from table10
group by int([Dept ID]/1000);

SELECT Left(cstr([Dept ID]),3), sum(field2) from table10
group by Left(cstr([Dept ID]),3);

 

[KKilfoil]

Hey all, I am trying to put together a query that takes a field that has 6 digits and Group By the first 3 digits. This information is then displayed in a Form.

Ex.
Dept ID: 500104 Balance: 20
Dept ID: 500108 Balance: 30
Dept ID: 500204 Balance: 50
Dept ID: 515221 Balance: 20

Group By: 500 Balance: 100
Group By: 515 Balance: 20

I was thinking to do the criteria by 500 * or something but having trouble. This is also a Number Type NOT Text based. Anything thoughts would be appreciated!

If numeric, create a GroupBy field like
FirstThree: +int([Dept ID]/1000)

 

[MSAccessRookie]

I can think of two possibilities. Which one you want to use depends on whether you need a string or a number as the interim value.

SELECT int([Dept ID]/1000), sum(Balance)
FROM <YourTableName>
GROUP BY int([Dept ID]/1000);

SELECT left(cstr(Dept ID]), 3), sum(Balance)
FROM <YourTableName>
GROUP BY left(cstr(Dept ID]), 3);

 

[MSAccessRookie]

If numeric, create a GroupBy field like
FirstThree: +int([Dept ID]/1000)

What is the effect of the "+" in the example above? I tried it with and without and do not see any difference.

 

[KDH]

Thanks for all of your help! I got it working just fine now thanks again ^_^.

 

[KDH]

Another Query question, but a little different.

We recently recieved a data dump from an outside vendor and there are two fields that are always related to each other:

Account_ID AND Patient Name

It seems that Account_ID is always filled in, however, there are times when there is no Patient Name.

Ex
Account_ID Patient Name
12345 Bob
12345
78945 Jim
78945 Jim

The problem comes when i do a Group By and what shows up is:

12345 Bob
12345
78945 Jim

Is there a way to make the Group By Account_ID and display the Not Null value? And I cannot do a Criteria: Is Not Null since this will elimante information that is stored to that specific Account_ID. Thanks again for all of your help!

 

[redneckgeek]

Use MAX instead of GROUP BY on the PatientName field

 

[KDH]

Using the Sort By: MAX elimanites those records and i lose the information that is stored there. I worked around it just by removing the Patient Name and using a group by Account ID.

 

[khawar]

SELECT Table1.Account_Id, Table1.Patient_Name
FROM Table1
GROUP BY Table1.Account_Id, Table1.Patient_Name
HAVING (((Table1.Patient_Name) Is Not Null));

Replace table1 in the above sql statement with your table name