Software·43,882 discussions

MS Access 200x

Locked

Should be easy…
Please reply using the field names in the formula, if applicable.

I have an employee table, skills table and another table that has the emp_id, skill_id to identify which employee have which skills,

So, lets use this for the ee.table
emp_id emp_code last_name
1 ab123 Babbit
2 fd456 Dillon
3 ds789 Smith

Skill Table
skill_id s_Name s_Desc
1 Poker p
2 Blackjack b
3 Craps c

emp_skills table

emp_id skill_id
1 1
1 2
2 2
2 3
3 1
3 2
3 3

Now, I want a report that has the employee_code and the skill_desc field, but all on one line, like this

Employee Skills
——— ——–
ab123 pb
fd456 bc
ds789 pbc

But here is what I get
ab123 p
ab123 b
fd456 b
fd456 c
ds789 p
ds789 b
ds789 c

Suppressing dups does not cut it, still same number of lines, so dont go there.

I?m sure there is a function for this, but how to I search for it if I don?t know what its called, besides, its points for YOU! 🙂

I can provide remote access for assistance if this will be easier, just let me know.

Thank you in advance.

Topic

Reply To: MS Access 200x

In reply to MS Access 200x

When composing this I had spaces between field names, and now they are lost, please refer to this link for this asme question with correct spacing.

http://eparisek.com/Techrepublic_MSAccess200x.htm

Reply To: MS Access 200x

In reply to MS Access 200x

When composing this I had spaces between field names, and now they are lost, please refer to this link for this same question with correct spacing.

http://eparisek.com/Techrepublic_MSAccess200x.htm

Reply To: MS Access 200x

In reply to MS Access 200x

As far as I am aware, there isn’t a simple function to do this in Access. You might want to check this article: http://support.microsoft.com/?kbid=210163

You could also do it programmatically using VBA.

Reply To: MS Access 200x

In reply to Reply To: MS Access 200x

Perfect. I walked through article, then I did it in Crystal Reports XI, thank you.

Reply To: MS Access 200x

In reply to MS Access 200x

I believe what you want is called a “Cross-Tab Query.” You will need to create this type of query and then base your report on the query. In the query, you specify which table contains the columns and which contains the rows.

Hope this helps!

Reply To: MS Access 200x

In reply to Reply To: MS Access 200x

Poster rated this answer.

Reply To: MS Access 200x

In reply to MS Access 200x

This question was closed by the author

[Author]

Replies