Query Creation from Existing Query

[Crdmster]

Query Help.pdf

Hey all! I'm looking for some direction as to what approach I should take to create the attached query. All of the information exist in the existing query, I just can't seem to find a way to populate my desired query.

Any help is appreciated! This is part of a much larger database, but I believe the sketch I made is sufficient to illustrate the issue.

Thanks in advance!

[ranman256]

Use the query wizard and create a crosstab query.It wilL ask what col to use as rows, what to use as col headers, and what as values.
Pick FIRST, since these don't sum.

[Crdmster]

Thanks ranman! I've never used a crosstab query, but this got me one step closer to what I need. I have one more issue that arose.

After the crosstab query was created, it was populated with numbers instead of the appropriate contact name or project position. Instead of displaying the name "John", it displays "34" (which is the ID number for John). The same issue is occurring for the new column headers. Instead of stating the project position as the header, it calls out the ID of the position. This seems like it has to do with which bound column it calls, but apart from just doing a VBA coded table I'm not sure how to resolve this.

Please let me know if this doesn't make sense, I'll be happy to draw up another illustration to help illustrate all of the tables that are feeding into this.

[NTC]

those columns/fields are lookup fields, holding the key of another table although displaying the English text. Join in that other table into the query and use that English field.

if this makes your initial cross tab query too complicated you can instead do it in a simple select query, saved with a name, and then use the select query as the starting point of your cross tab query

this behavior of the lookup field with the discrepancy as to what is actually stored versus what is displayed is a reason some developers won't use that field type - but if you understand the behavior it is very manageable.