Can a query do this?

[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

 

[Michel Walsh]

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]

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

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

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

 

[Guest]

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