Query Quesiton

[dallascowboys]

Hello,

What is the SQL Statement if a field changes for example changes from 20/21 to 21-22 and you want another to populate with the change for example 21-22-1

 

[theDBguy]

Hi. Welcome to AWF!

Not sure I follow your question. Can you give some concrete examples? Thanks!

 

[The_Doc_Man]

This is a transformation question, i.e. reformatting. Show us a few examples of what you have at first and what you would like to see for each case.

 

[dallascowboys]

TRANSFORM Count(tblReimb.Row) AS CountOfRow
SELECT tblReimb.FY, Left([FY-Qtr_str],2) & "/" & Mid([FY-Qtr_str],3,2) & "-" & Right([FY-Qtr_str],1) AS [FY-Q], Count(tblReimb.Row) AS [Total Of Row]
FROM tblReimb
GROUP BY tblReimb.FY, Left([FY-Qtr_str],2) & "/" & Mid([FY-Qtr_str],3,2) & "-" & Right([FY-Qtr_str],1)
ORDER BY tblReimb.FY DESC
PIVOT tblReimb.[Corr-Rev];

FY FY-Q Total Of Row <>
20-21 20/21-1 68 68
19/20 19/20-1 62 62
19/20 19/20-2 63 63
19/20 19/20-3 68 68
19/20 19/20-4 68 68
18/19 18/19-4 62 62

Here is the Code and Datasheet view. So from time to time the FY field will display with a hyphen or a front slash. What is the command so the FY-Q field will populate according to the FY Field . So the FY-Q should be 20-21-1. Hope that makes semse

 

[Gasman]

Take the FY value and suffix the Q part of it?

 

[dallascowboys]

What do you mean, Can you show me an example

 

[arnelgp]

What is the command so the FY-Q field will populate according to the FY Field . So the FY-Q should be 20-21-1. Hope that makes semse

just copy FY:

TRANSFORM Count(tblReimb.Row) AS CountOfRow
SELECT tblReimb.FY, [FY] & "-" & Right([FY-Qtr_str],1) AS [FY-Q], Count(tblReimb.Row) AS [Total Of Row]
FROM tblReimb
GROUP BY tblReimb.FY, [FY] & "-" & Right([FY-Qtr_str],1)
ORDER BY tblReimb.FY DESC
PIVOT tblReimb.[Corr-Rev];

 

[dallascowboys]

You are right on the data file being consistent. The issue is that file we receive is from the State Level so we have no say so on the / or - .