I am very new to Access.
I have difficulty in generating a report
I have an IT facilities database which contains 3 tables, hardware software and accessories. Each hardware, software and accessory have its user name and one user name can have one or more hardware, software or accessory. (ex. A user may have more then one software.)

The fields of the tables:

Table 1 (hardware):
User Name, Post, Maint end date, S/N, remark

Table 2:
User Name post, software name, S/N, remark

Table 3:
User Name, post, accessories, S/N, remark

I want to build a report(user list) which can show the following:
--------------------------------------------------------------------------------
User Name
Post

Hardware

Hardware1
Maint end date 1
S/N 1
Remark 1

Hardware 2 (if any)
Maint end date 2
S/N 2
Remark 2
.
.
.
Software
Software 1
Software_S/N 1
Software_remark 1


Software 2 (if any)
Software_S/N 2
Software_remark 2
.
.
.
Accessories
Accessories 1
Accessories _S/N 1
Accessories _remark 1

Accessories 2 (if any)
Accessories _S/N 2
Accessories _remark 2
.
.
.


---------------------------------------------------------------
The report shows all the hardware, software and accessories that a user has but it should not be repeated.

How can I do to build this user list??

Thanks in Advance!!:D

One way would be a UNION query to pull the 3 tables into one:

SELECT Field1, Field2, "Hardware" AS TypeOfAsset
FROM HardwareTable
UNION ALL
SELECT Field1, Field2, "Software" AS TypeOfAsset
FROM SoftwareTable
...

From that your report should be fairly simple.

One way would be a UNION query to pull the 3 tables into one:

SELECT Field1, Field2, "Hardware" AS TypeOfAsset
FROM HardwareTable
UNION ALL
SELECT Field1, Field2, "Software" AS TypeOfAsset
FROM SoftwareTable
...

From that your report should be fairly simple.


But will the user name be repeated?
I want each user name is shown once only and its hardware, software, acceessories are shown subsequently.
for example, a Computer Engineer called ABC which has 2 hardware, 1 software and 1 accessories

-------------------------------------------
Mr ABC
Computer Engineer

Hardware1OfABC
Hardware2OfABC
Software1OfABC
Accessory1OfABC

Mr DEF
.
.
.
---------------------------------------

Each user name only shown once.
Thanks!!:)

Yes, but Sorting and Grouping in the report would create the display you're looking for.

Yes, but Sorting and Grouping in the report would create the display you're looking for.

But after sorting and grouping, i got the following:
-------------------------------------------------------------
User Name
Post

Hardware1
Software1
Accessories1
Hardware2
Software1
Accessories1
--------------------------------------------------------------
Instead of:

-------------------------------------------------------------
User Name
Post

Hardware1
Hardware2
Software1
Accessories1
--------------------------------------------------------------
The software and accessories parts are repeated.
Have I used a wrong method?
How to solve the problem?

Thanks!!

Can you post the db?

Can you post the db?
do u mind giving me your personal email?

Is the attached not what you want (I didn't include all the fields, so just the basic layout)?

Is the attached not what you want (I didn't include all the fields, so just the basic layout)?

That's what I want!How can u achieve this?

Exactly what I suggested earlier. A UNION query to bring the 3 tables together, a report based on that query with grouping on the user and type of asset.