Complex Queries

[Tamrak]

Hello,

I have the questions regarding how to make the queries to work. I am using the queries to design the report.

Let’s give an example of how data constructed:

Project Table

• Project ID (Primary Key)
• Project Description

Some of the data are as follow:

Project ID Project Description

58 Relocation of Accounting
59 Update Payroll
60 Remarketing

Text Table:

• Project ID (Foreign Key)
• Group ID
• Group Description


Some of the data in the Text Table are as follow:

Project ID Text ID Text Description
58 12345678 Bill White
58 12345689 John Doe
58 12345789 William Hart
58 12345890 <blank>
59 12345689 Jane Doe
59 12345789 Bill Richards
60 12345678 Jim Brown
60 12345689 <blank>
60 12345890 Joe Black

I would like to see the queries in the following format:

Project ID,Project Description,Proj Manager,Requestor, Prepared by,Approved by
58 Relocation of Accounting Bill White John Doe William Hart <blank>
59 Update Payroll <Blank> Jane Doe <blank> <blank>
60 Remarketing Jim Brown <blank> Joe Black

Is there anyway I can make the query within one line? Should I create another table for Text ID? <In the real situation, I have more than 10 numbers.>

The Text Table has been imported from Microsoft Project table. I cannot change it.

If I create the text ID table, it will be the following:

Text ID
Text Description

Text ID Text Description
12345678 Project Manager
12345689 Requestor
12345789 Preparer
12345890 Approval

I do appreciate your time and your response. I do wonder how to write the query to fulfill my requirement. I tried to write a few queries. They return me with 4 lines of the same project number when I set relation to Project ID. I am also concerning about the blank value because the query might not pick-up. I might have to run a query within another query.

I do appreciate your response. As I previously stated, I am looking for one line of project with the list of the people on the same line, another word, one line -> one project and the information will be reading from left to right.

Thank you for your time.

 

[dhookom]

This looks like a standard crosstab query with ProjectId and ProjectDescription as the Row Headings, TextDescription as the Column Heading, and first of other Text Description as the value.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]