Sql / report help?

[mejia.j88]

Hi all,

I have been working on a report which has proven to be a tad bit difficult only because the data is being pulled and rearranged from 4 different tables (actually, 1 query and 3 tables).

The query which populates the report is working perfectly fine. it displays teh data from the 4 tables in the correct manner. When i run the query, it works great.

the problem is when i try to open my report, a pop up message appears saying:
The specified field 'Glassing_inspection.operator' could refer to more than one table listed in the FROM clause of your SQL statement.

The following is the SQL from the query i am using on my report:




SELECT ([Glassing_Inspection].[date_time]) AS shiftdate, Switch(TimeValue([glassing_inspection].[Date_Time]) Between TimeSerial(0,0,0) And TimeSerial(5,0,0),"3rd Shift",TimeValue([glassing_inspection].[Date_Time]) Between TimeSerial(5,30,0) And TimeSerial(14,0,0),"1st Shift (Days)",TimeValue([glassing_inspection].[Date_Time]) Between TimeSerial(14,0,0) And TimeSerial(22,0,0),"2nd Shift",TimeValue([glassing_inspection].[Date_Time]) Between TimeSerial(22,0,0) And TimeSerial(0,0,0),"3rd Shift") AS WorkShift, Glassing_Inspection.Part_Number, Glassing_Yield.WO, Dicing_Yield.Diffusion_Lot, Glassing_Inspection.EvapLot, qry_totalyield.SumOfStart_Qty, qry_totalyield.SumOfYield, Glassing_Inspection.Operator, Glassing_Inspection.Glassing_Lot, Glassing_Yield.Operator
FROM Dicing_Yield INNER JOIN (qry_totalyield INNER JOIN (Glassing_Yield INNER JOIN Glassing_Inspection ON Glassing_Yield.WO = Glassing_Inspection.Work_Order) ON qry_totalyield.WO = Glassing_Inspection.Work_Order) ON Dicing_Yield.Work_Order = qry_totalyield.WO
GROUP BY ([Glassing_Inspection].[date_time]), Switch(TimeValue([glassing_inspection].[Date_Time]) Between TimeSerial(0,0,0) And TimeSerial(5,0,0),"3rd Shift",TimeValue([glassing_inspection].[Date_Time]) Between TimeSerial(5,30,0) And TimeSerial(14,0,0),"1st Shift (Days)",TimeValue([glassing_inspection].[Date_Time]) Between TimeSerial(14,0,0) And TimeSerial(22,0,0),"2nd Shift",TimeValue([glassing_inspection].[Date_Time]) Between TimeSerial(22,0,0) And TimeSerial(0,0,0),"3rd Shift"), Glassing_Inspection.Part_Number, Glassing_Yield.WO, Dicing_Yield.Diffusion_Lot, Glassing_Inspection.EvapLot, qry_totalyield.SumOfStart_Qty, qry_totalyield.SumOfYield, Glassing_Inspection.Operator, Glassing_Inspection.Glassing_Lot, Glassing_Yield.Operator
HAVING (((([Glassing_Inspection].[date_time])) Between [Forms]![frm_rptInspectionShiftYield_ByDateRange]![txtStartDate] And [Forms]![frm_rptInspectionShiftYield_ByDateRange]![txtEndDate]));



What would the main source of this error pop up be?

thanks
j

[June7]

Not sure. Why use GROUP BY clause? I don't see any aggregate calcs in this query. Try removing the GROUP BY.

[mejia.j88]

Hi June,

problem fixed, thanks for your rec.
i have another question that is tied to this post, sort of.
in the report i am putting together, i need to display the operators who work on the work order.
the work order is composed of several Glassing Lots, more than one operator can work on these lots.
To clarify, several operators work on Glassing Lots that make up a Work Order.

a second group of operators inspect the glassing lots after they are built.
i am grouping by Glassing lots in my report, and would like to display the operators that have glassed and the operators that have inspected.

ideally, i would want to display the list of operators that glassed, as well as a list of operators that inspected, without having duplicate entries in report. currently i am putting the glassing operators and inspection operators in the details section. the problem is, if we have 1 glassing operator and 3 inspection operators, the glassing operator repeats (and vice versa).

what is the best way to approach this kind of data?

[June7]

See if setting textbox HideDuplicates property to Yes helps. If not, possibly subforms or VBA code to concatenate values (review http://allenbrowne.com/func-concat.html). A listbox could show the operators but listboxes don't work well on reports, they are not intended to.

[mejia.j88]

hi june7,
the HideDuplicates served the purpose temporarily (my boss now wants to see a concatenated form)
i am using the allenbrowne code from the link you posted.
i copied/pasted to a module, and placed in the ControlSource of my text box:
=ConcatRelated("[Operator]","[1qry_rptInspectionShiftYield]")

I tried running the report and i get hte following error:
Error 3061: Too Few parameters. Expected 2.

My sql for the query is below:

SELECT ([Query1].[date_time]) AS shiftdate, Switch(TimeValue([query1].[Date_Time]) Between TimeSerial(0,0,0) And TimeSerial(5,0,0),"3rd Shift",TimeValue([query1].[Date_Time]) Between TimeSerial(5,30,0) And TimeSerial(14,0,0),"1st Shift",TimeValue([query1].[Date_Time]) Between TimeSerial(14,0,0) And TimeSerial(22,0,0),"2nd Shift",TimeValue([query1].[Date_Time]) Between TimeSerial(22,0,0) And TimeSerial(0,0,0),"3rd Shift") AS WorkShift, [1qry_totalyield].SumOfStart_Qty, [1qry_totalyield].SumOfYield, [1qry_totalyield].Glassing_Lot, Query1.Diffusion_Lot, Query1.Operator_inspection, Query1.EvapLot, Query1.Work_Order, [1qry_UniqueOperators].Operator, Query1.Part_Number
FROM Query1 INNER JOIN (1qry_totalyield INNER JOIN 1qry_UniqueOperators ON [1qry_totalyield].Glassing_Lot = [1qry_UniqueOperators].Glassing_Lot) ON Query1.Glassing_Lot = [1qry_UniqueOperators].Glassing_Lot
GROUP BY ([Query1].[date_time]), Switch(TimeValue([query1].[Date_Time]) Between TimeSerial(0,0,0) And TimeSerial(5,0,0),"3rd Shift",TimeValue([query1].[Date_Time]) Between TimeSerial(5,30,0) And TimeSerial(14,0,0),"1st Shift",TimeValue([query1].[Date_Time]) Between TimeSerial(14,0,0) And TimeSerial(22,0,0),"2nd Shift",TimeValue([query1].[Date_Time]) Between TimeSerial(22,0,0) And TimeSerial(0,0,0),"3rd Shift"), [1qry_totalyield].SumOfStart_Qty, [1qry_totalyield].SumOfYield, [1qry_totalyield].Glassing_Lot, Query1.Diffusion_Lot, Query1.Operator_inspection, Query1.EvapLot, Query1.Work_Order, [1qry_UniqueOperators].Operator, Query1.Part_Number
HAVING (((([Query1].[date_time])) Between [Forms]![frm_rptInspectionShiftYield_ByDateRange]![txtStartDate] And [Forms]![frm_rptInspectionShiftYield_ByDateRange]![txtEndDate]));


The query that i am using is made from 3 different queries.
i am not fully understanding the error message.

[June7]

Possibly it is not finding the Operator field because it comes from a subquery.

Try:
=ConcatRelated("[1qry_UniqueOperators].Operator", "[1qry_rptInspectionShiftYield]")

If still fails, would you like to provide the project for analysis?

[mejia.j88]

http://www.box.com/updates#/updates/1/a/1/1854612483

contains the project.
the report can be found under switchboard, glassing inspection, shift report.

thank you

[mejia.j88]

this is the database Glassing Application Rev P (2).zip

[June7]

I pasted Allen's function into an existing project and changed nothing and the function ran great. So then I tested in your project in a query that pulls straight from Employee Numbers table and it also works without flaw. Something about your complicated query that you are drawing data from for the report. Even if the function would work with the query, your expression calling the function needs the WHERE parameter, like:
=ConcatRelated("Operator","1qry_rptInspectionShift Yield","Glassing_Lot='" & [Glassing_Lot] & "'"). And because you are grouping the report by date/shift/workorder, all of those might have to be included in the WHERE parameter. I tested that but the function still doesn't like the query.

At what level do you want the Operators summarized? The function will work in 1qry_UniqueOperators.
Operators: ConcatRelated("Operator","Glassing_Yield","WO='" & [WO] & "' AND Glassing_Lot='" & [Glassing_Lot] & "'")
This field will carry through to the report.

Also, your date search criteria is not working because of the time component. I input 3/18/2012 and 3/20/2012 as Start and End and only 3/19/2012 records returned. BETWEEN AND is supposed to be inclusive for start and end. Need to have a field in query that extracts the date part and apply criteria to that field.
shiftdate: CDate(Format([Query1].[date_time],"mm/dd/yyyy"))

[mejia.j88]

Thanks so much,
i have it working in 1qry_uniqueoperators. i'm not sure if this is possible, but take this for example:
if WO is W12345 and Glassing lot is GAB12345, and operator 111 makes 3 records, and operator 222 makes 2 records, then the concat function shows
111,111,111,222,222 which is correct, but is there a way to truncate to only show 111,222 ?

the 1qry_uniqueoperators is filtered to show unique records,
i.e. it would show one records for W12345, GAB12345, opt 111, and W12345, GAB12345 opt 222 but still concatenates 111,111,111,222,222.

also, fixed the date and time fcn.

[June7]

Modify Allen's function to only concatenate the operator if that value not already in the constructed string. Something like:

If InStr(strOut, rs(0)) = 0 Then
strOut = strOut & rs(0) & strSeparator
End If

But that might give inconsistent results.

Better might be to include ORDER BY argument in the ConcatRelated function call - order by Operator. Then have a variable hold the operator id and compare to the rs(0), if same don't concatenate if not same, then concatenate and reset the variable to the new value. Like:

If varID <> rs(0) Then
strOut = strOut & rs(0) & strSeparator
varID = rs(0)
End If

Also, you might want to set the CanGrow property of the textbox for the concatenated operators to Yes.

[mejia.j88]

Hi June7,

I am not exactly sure where to put that (the code you mentioned in the previous post) in Allen's code, i beleive it should be in the section:

ElseIf Not IsNull(rs(0)) Then           
        strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close

between Elseif and srtOut=, also, i am unsure of what to declare the varId variable you suggested, and where to declare it in the code.

[June7]

It is common practice to declare all variables at the beginning of a procedure. varID would be a Variant type.

Replace the original strOut = line with the 4 lines of my suggested code.

[mejia.j88]

sooo clooooseeeee,

maybe "where to declare" is the wrong phrase to use.
the varID has to be set equal to operator somewhere in the code correct, or equal to something?

i put the suggested 4 lines of code in, set [operator] as the ORDER BY in the concat function, ran it then
i get the following error:

Error 3075: Syntax error (missing operator) in query expression '0130A' (0130A is an operator entry)

when i push OK, the next error occurs:

Error 3061: Too few parameters. Expected 1.

[June7]

The expression in query would be:

Operators: ConcatRelated("Operator","Glassing_Yield","WO='" & [WO] & "' AND Glassing_Lot='" & [Glassing_Lot] & "'","WO, Glassing_Lot, Operator")

varID initializes as an empty string just by declaring it with the Dim statement. It gets reset in the code I provided by reference to the recordset field.