Can a query do this?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All

I would imagine that it would be a crosstab query but is there any way that
I can go from Input A to Output B:

Input Fields:

Name (Text) - Any name
FieldID (Integer) - 28, 29 or 30
Value (Text) - A date or place or time

e.g.

Record 1 - Fred: 28: "21:00"
Record 2 - Fred: 29: "Glasgow"
Record 3 - Fred: 30: "Busy"

Output Fields:

Name 28 29 30
Fred "21:00" "Glasgow" "Busy"

I think it would be a crosstab query since a value in a field would become a
Column Header.

I have created the outout through code, which works but is very slow since I
am dealing with a large number of records.

Thanks again.

Alastair MacFarlane
 
Hi,


Indeed, that is a job for a crosstab.

TRANSFORM LAST( [Value] )
SELECT [Name]
FROM myTable
GROUP BY [Name]
PIVOT FieldID




Hoping it may help,
Vanderghast, Access MVP
 
Michael,

What you have responded with makes sense and I thank you for your response.
I will check it out tomorrow with the complete data.

Alastair MacFarlane

Michel Walsh said:
Hi,


Indeed, that is a job for a crosstab.

TRANSFORM LAST( [Value] )
SELECT [Name]
FROM myTable
GROUP BY [Name]
PIVOT FieldID




Hoping it may help,
Vanderghast, Access MVP


Alastair MacFarlane said:
Dear All

I would imagine that it would be a crosstab query but is there any way
that
I can go from Input A to Output B:

Input Fields:

Name (Text) - Any name
FieldID (Integer) - 28, 29 or 30
Value (Text) - A date or place or time

e.g.

Record 1 - Fred: 28: "21:00"
Record 2 - Fred: 29: "Glasgow"
Record 3 - Fred: 30: "Busy"

Output Fields:

Name 28 29 30
Fred "21:00" "Glasgow" "Busy"

I think it would be a crosstab query since a value in a field would
become a
Column Header.

I have created the outout through code, which works but is very slow
since I
am dealing with a large number of records.

Thanks again.

Alastair MacFarlane
Click to expand...
Click to expand...
 
You right you should use crosstab query, try this

TRANSFORM Last(MyTableName.Value) AS LastValue
SELECT MyTableName.Name
FROM MyTableName
GROUP BY MyTableName.Name
PIVOT MyTableName.FieldID
 
Back
Top