Crosstab Query

[rama]

Hello,
In my database there is a table called “tblMaterial” which is having
fields called Description, Vendor, Ref, Note, RefDate, Ref1, RefDate1,
Note1, Ref2, RefDate2, Note2. Sometimes Vendors may require additional
clarifications these will be entered in Ref1, RefDate1, Note1 and
subsequent correspondence will go to Ref2, RefDate2, Note2. My problem
is to consolidate all these “Ref, Note, RefDate, Ref1, RefDate1,
Note1, Ref2, RefDate2, Note2” as “REFERENCE”, “DATE”, “NOTES” with
the help of a query. How can I work it out? Please help me.
Rama

 

[Duane Hookom]

I'm not sure what you mean by "consolidate". I do think your table structure
is wrong. I would store the notes in a separate related table.

 

[rama]

I'm not sure what you mean by "consolidate". I do think your table structure
is wrong. I would store the notes in a separate related table.

--
Duane Hookom
Microsoft Access MVP





- Show quoted text -

As per my current table design data will be displayed as below.
Description Vendor Ref RefDate Note Ref1 RefDate1 Note1
Material1 X X1 01-11-08 OK
Material2 Y Y1 01-11-08 NOK Y2 02-11-08 OK
Material3 Z Z1 02-11-08 OK
Here “Y” has quoted Material2 but was not acceptable. So “Y” re-quoted
and it become OK. What I wish to do is by bringing all Ref under
column Heading REFERENCE, all RefDate under DATE and all Notes under
NOTES to filter out Note with OK, and corresponding Ref & RefDate
against each material. If Note field is OK then no more
correspondence but NOK then vendor will be contacted twice to get the
correct material specification. These data will be put in Ref1,
RefDate1, Note1, Ref2, RefDate2, Note2 fields
Rama

 

[John Spencer]

PERHAPS what you are looking for is to use a UNION query to normalize
the data,

SELECT Description, Vendor, Ref As Reference
, RefDate as ReferDate
, Note as Notes
FROM TblMaterial
UNION ALL
SELECT Description, Vendor, Ref1
, RefDate1
, Note1
FROM TblMaterial
UNION ALL
SELECT Description, Vendor, Ref2
, RefDate2
, Note2
FROM TblMaterial



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[rama]

PERHAPS what you are looking for is to use a UNION query to normalize
the data,

SELECT Description, Vendor, Ref As Reference
, RefDate as ReferDate
, Note as Notes
FROM TblMaterial
UNION ALL
SELECT Description, Vendor, Ref1
, RefDate1
, Note1
FROM TblMaterial
UNION ALL
SELECT Description, Vendor, Ref2
, RefDate2
, Note2
FROM TblMaterial

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================




- Show quoted text -

Thank you very much Mr. John for your kind suggestion. My query is
working fine.
Rama