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];

 

[Pat Hartman]

You are fixing up the second field by using the left and mid functions. Do the same thing with the FY field.

OR better yet, FIX THE **** data. Don't let the user enter a dash if you always want the separator to be a slash.

Not sure why people persist in coding work arounds when the solution is to prevent the problem at the point of data entry.

If the data is sent to you from a different application so you have no control over its formatting on entry, then fix the data when you import it. you aren't required to live with poorly formatted data. It is a choice.

You would add validation code to the Form's BeforeUpdate event to either silently fix the data or refuse to save it until the user fixes it. Once that is done, you would back up the database and run an update query to fix any existing bad data.

 

[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 - .

 

[Pat Hartman]

The issue is that file we receive is from the State Level so we have no say so on the / or - .

Been there. I get it. Did you understand the solution? You FIX the data when you load it. Then you don't have to deal with it later.