Query definition to change field name

[scubadiver007]

Hi

I work as a performance data analyst in local government and report from a system called E-start which is used by children centre administrators to record attendees at their events.

One of the reports is for event outcomes. Each of these outcomes are compiled into a single field and separated by a full stop so I can do text-to-columns in Excel and then set up a link table.

For each record there can be anything from one outcome to over 20 though I don't think there would ever be more than 30.

I want to put all these outcomes into a single field. I could create 30 append queries but I thought it might be better to loop a query definition.

The field names would be AO1...AO30 (and I've set up a table if this helps).

In theory I could stop the loop when I get the first column that returns no records.

I've done something similar where I have used a loop to filter a query but not to change the field name.

Dim Email As String
Dim Kcode As String
Dim Qtr As String
Dim Kcodestr As String
Dim strSql As String
Dim mySql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Qtr = Me!Quarter_ID

    strSql = "SELECT DISTINCT Tble_Activity.Kcode, Tble_Practice.Email " & _
             "From Tble_Practice INNER JOIN Tble_Activity ON Tble_Practice.Kcode = Tble_Activity.Kcode " & _
             "WHERE ((Tble_Activity.Quartercode)=eval('[forms]![menu]!Quarter_ID'));" '& _
             '"AND ((Tble_Activity.Kcode)=('J82054'));"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)
    rs.MoveFirst
    
    Do While Not rs.EOF
    
    
    mySql = "SELECT PAYMENTS_Checks_src.Services, PAYMENTS_Checks_src.Total FROM PAYMENTS_Checks_src " & _
            "WHERE PAYMENTS_Checks_src.KCode= '" & rs("KCode") & "'" & _
            "AND ((PAYMENTS_Checks_src.Quartercode)=([forms]![menu]![Quarter_ID]));"
    db.QueryDefs("Payments_KCode").SQL = mySql
    Kcodestr = rs!Kcode
 
    DoCmd.OutputTo acOutputQuery, "Payments_KCode", acFormatXLS, "I:\Medical\Enhanced Services\ENHANCED SERVICES\2013-2014\payments\" & Qtr & "\" & Kcodestr & " " & Qtr & " - payments.xls"
        
    
   
    
    rs.MoveNext
    Loop

 

[June7]

Not really clear to me what you want. Example raw data and desired output could be helpful.

If you want to concatenate data from multiple records to a single field, review http://allenbrowne.com/func-concat.html

I see syntax errors on both SQL statements. The first shows two semi-colons and extraneous apostrophes.

Both need a space in front of AND: " AND

Recommend concatenating form controls instead of including within quote marks and not use Eval().

 

[scubadiver007]

Hi,
Each record has a certain number of outcomes that are stored in a single column delimitered by a full stop. I can split these out in Excel and create a link table.
I want to put all the outcomes in two fields (outcomeA and outcomeB).
This is the query for the first outcome...

INSERT INTO Outcomes_List ( Member_ID, Setting_Seen, Event_Date, Event_Description, OutcomeA, OutcomeB )
SELECT Outcomes.[Member ID], Outcomes.[Setting Seen], Outcomes.[Event Date], Outcomes.[Event Description], IIf(InStr([AO1],":"),Left([AO1],InStr([AO1],":")-1),"") AS OutcomeA, IIf(InStr([AO1],":"),Mid([AO1],InStr([AO1],":")+2),"") AS OutcomeB
FROM Outcomes
WHERE (((Outcomes.[Setting Seen])<>"XXX Do Not Use") AND ((Outcomes.AO1) Is Not Null) AND ((Outcomes.Attended)="Yes"));

Then the second query would be as follows...

INSERT INTO Outcomes_List ( Member_ID, Setting_Seen, Event_Date, Event_Description, OutcomeA, OutcomeB )
SELECT Outcomes.[Member ID], Outcomes.[Setting Seen], Outcomes.[Event Date], Outcomes.[Event Description], IIf(InStr([AO2],":"),Left([AO1],InStr([AO2],":")-1),"") AS OutcomeA, IIf(InStr([AO2],":"),Mid([AO2],InStr([AO2],":")+2),"") AS OutcomeB
FROM Outcomes
WHERE (((Outcomes.[Setting Seen])<>"XXX Do Not Use") AND ((Outcomes.AO2) Is Not Null) AND ((Outcomes.Attended)="Yes"));

All I am saying is that I would like to loop the query so the field name is replaced and ideally stops when a query returns no records.

Thanks

 

[June7]

Fix the syntax errors for a start.

 

[Gasman]

I believe your structure is wrong and you have your Excel head on.
In Access in your OutComes table you would have a field for index (1 -30) and then the rest of the data in the record.


Then you would just have one query and choose which index or all that you want to interrogate.?

 

[The_Doc_Man]

I want to put all these outcomes into a single field

You think you do, but you really don't. With 1 or 2 outcomes, that might fit in a Short Text field even with concatenation. With 20 to 30 outcomes, you will be likely overflow any Short Text field. When your list of outcomes is all scrambled up in that single field, you risk having to deal with a Long Text field which is not easy to process. You cannot index such a field for content, and searching the content of such fields can run into issues for those functions designed to work only on short text fields. In fact, SQL string operators can balk in some cases with Long Text fields.

As Gasman points out, you are thinking in two dimensions when you need to think in at least three or more. (See also the movie Star Trek II: The Wrath of Khan for why two-dimensional thinking is bad. )

Then, there is the issue that using INSERT INTO doesn't help you with your concatenated field because INSERT INTO doesn't update previously existing records. It creates new records. So if you were appending one outcome at a time, you would INSERT a new record on the second outcome to be stored and probably would get a key violation.

You want to use a parent/child relationship for two or three reasons.

1. Doesn't matter how many outcomes (or how few) you have. You can add 1 or 30 or 300. All works the same way.

2. No subsequent need to parse out the contents of a concatenated field because each result stays separate.

3. You can manipulate Short Text fields a lot more easily because (a) less to search and (b) most functions work VERY well with Short Text fields.

4. Statistical analysis of the child table becomes almost trivial because of #3. Including that you would have no need to come back to the long text field and ask how many outcomes were stored there. You can just count the child records at any time.

If you are not comfortable with parent/child tables, you can read up on the subject here in the forum using the Search function. Look in the thin blue ribbon near the top of the page and look 3rd from the right.

Now as to converting the output of your 3rd party software to Excel as an intermediate, you can do that, but to then LINK the Excel as a table so you can run queries on it? The query you WANT to run is to insert into a child table, not update a big Long Text field. You can do this with VBA and a recordset operation or you can do some complexities in your putative loop to generate SQL "INSERT INTO" queries. But here's the next warning: If you import your Excel as a linked table, EVERY RECORD will be the length of the Excel row with the most responses in it. So you would have to test every field for whether it was empty or not because the fields WILL exist. Null or populated, they WILL exist.