Combining Several Records into one...HELP!!!!

[Medeo]

I am trying to combine a bunch of information from the same table into one line and I can't figure it out. I have a field named VIN (&lt;--- has the duplicates entries), one named WC (&lt;--- unique entries when compared against VIN), and a whole bunch of yes/no fields.<br>
<br>
Here is an example of what I am looking for:<br>
<br>
VIN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WC&nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;C&nbsp;&nbsp;&nbsp;&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;E&nbsp;&nbsp;&nbsp;&nbsp;F&nbsp;&nbsp;&nbsp;&nbsp;G&nbsp;&nbsp;&nbsp;&nbsp;H <br>
27717&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n<br>
27717&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n<br>
27717&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;250&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n<br>
27717&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;280&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;Y<br>
<br>
Compiled&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;n&nbsp;&nbsp;&nbsp;&nbsp;Y<br>
<br>
(I also want the first yes/no (&quot;A&quot to default to no if anthing else shows yes)<br>
<br>
This is probably going to involve some VB, unfortunately I know very little about VB ...<br>
<br>
HELP!!!<br>
<br>
<br>
Luggy

 

[JimHorton]

Luggy,<br>
Can you explain what you mean by 'Compiled'. It looked like you were putting a 'Y' in the A,B,C, etc column if any of the columns had a 'Y', except the 'A' column didn't follow that logic. Also, what do you mean by combining into one line--do you just want to display only, or load into another record?<br>
<br>
Depending on what you actually want, an Aggregate (Group By) query might help here. Give us some more info...<br>
--Jim

 

[DougP]

Does my example to your question posted several days ago, not work.<br>
<A HREF="

http://www.tek-tips.com/viewthread.cfm?SQID=8289&SPID=181&CFID=1053871&CFToken=47370164"

TARGET="_new">

http://www.tek-tips.com/viewthread.cfm?SQID=8289&SPID=181&CFID=1053871&CFToken=47370164</A><br>

<br>
This is a sample that does work<br>
--------------------------------<br>
Public Sub sample(VinNumber)<br>
Dim MyDB As Database, rst As Recordset, SQL As String<br>
Set MyDB = CurrentDb<br>
SQL = &quot;Select * From [Table1] Where [VIN] = '&quot; & VinNumber & &quot;';&quot;<br>
Set rst = MyDB.OpenRecordset(SQL)<br>
rst.MoveLast<br>
rst.MoveFirst<br>
For a = 1 To rst.RecordCount<br>
For b = 2 To 32 'Number of fields starting with the ordinal postion for A<br>
Debug.Print rst.Fields(b).Name<br>
If UCase(rst.Fields(b)) = &quot;N&quot; Then<br>
rst.Edit<br>
rst.Fields(b) = &quot;Y&quot;<br>
rst.Update<br>
End If<br>
Next<br>
rst.MoveNext<br>
Next<br>
rst.Close<br>
MyDB.Close<br>
End Sub<br>
-------------------------------- <p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[Medeo]

I am using one single table with the field VIN having duplicate numbers in it dependant on which WC it goes through. The VIN is an item going through a production line and the WC's are the different Work Centres the VIN is going through. A,B,C ..etc. are Fault Codes. If the VIN had a particular problem, that Fault Code would be checked off as Yes. What I am trying to do is have a final report that lists all of the faults relating to a particular VIN, all on one line. So if 50 items go through the production line, I will have a report from each WC that the item went through that station and the faults involved at that station, and a final report that is 50 lines long that has the VIN and <u>any</u> fault that happened at any station listed on it.. Also, A is No Faults - and if any of the other Yes/No fields show up as Yes on the final report, I need<br>
<br>
Sorry about the confusion with this... i am sort of new to MS access. And sorry about the Luggy at the end ... that's my alias for IRC ....<br>
<br>
<br>
Medeo

 

[Medeo]

DougP:<br>
<br>
Thanks for the code! ... I will see whether it will work (hopefully it will !) ... this has frustrated me off and on for about two weeks.<br>
<br>
<br>
Medeo

 

[JimHorton]

Medeo,<br>
The below sql assumes an example table shortened with only 3 faults for clarity:<br>
<br>
Vin--Vin <br>
Wc--Wc <br>
Fa--(Fault A)<br>
Fb--(Fault B)<br>
Fc--(Fault C)<br>
<br>
Vin and Wc combine to be a Primary Key <br>
<br>
The below sql will give you the # of faults per fault type, per Vin, on one line. Included is a count of the Wc's for each VIN.<br>
<br>
SELECT tblVins.VIN, Count(tblVins.WC) AS CtWc, Abs(Sum([fa])) AS A, Abs(Sum([fb])) AS B, Abs(Sum([fc])) AS C<br>
FROM tblVins<br>
GROUP BY tblVins.VIN;<br>
<br>
Change your fields to match, this will work.<br>
--Jim<br>
<br>
<br>

 

[JimHorton]

Forgot to clarify: The Fa, Fb, and Fc are Yes/No, you must have this for the Abs(Sum()) to work...<br>
--Jim