MS access report design trouble (badly needed for help)

[franciszarf]

I have a table like this in my MS Access with the data on it




but when I designed a report in access
instead of getting an output like this

i got something like this...


can you please help me with my problem. Because some data are repeated
and it's annoying.

NOTE: The ID has its own header..

[ranman256]

its your join, when you get repeated records, its the join.
make 2 queries,
Q1 , a union query (to get 1 list of child records)
select id, name, Qty,Cost from tReplacements
union
select id, name, Qty,Cost from tFuel

then Q2 is your report query (of master recs)
join Q1 to tEquip (1 join)
report with this query.

[franciszarf]

I am new to using also queries in access sir can you please explain it
as a novice like me can understand. Thanks a lot sir. By the way I am glad
that there is a solution to my problem I will also try to research on queries.

[June7]

This data involves two dependent tables that have a many relationship to a master table. Including all 3 in one query will produce the results you show.

If you want to display raw data, options are:

1. report/subreport(s) arrangement.

2. UNION query as described by ranman then use Sorting & Grouping features of report to organize the records by the two types of cost - recommend another field in the UNION to identify the type and each SELECT line can include a join to the Equipment table.

SELECT Equipment.ID, Qty, Description, Cost, "Parts" AS Category, Model FROM [Replaced Parts] RIGHT JOIN Equipment ON [Replaced Parts].ID = Equipment.ID
UN ION SELECT Equipment.ID, Qty, [Type], Cost, "Fuel", Model FROM [Fuel Cost] RIGHT JOIN Equipment ON [Fuel Cost].ID = Equipment.ID;

There is no wizard or builder for UNION, must type into SQL View of query designer.

If all costs were in one table to begin with, would not have this difficulty.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Also, should not use reserved words as names - Type is a reserved word.

[franciszarf]

Hello sir I just tried the Union Query but what happened is that it combined the
tables "replaced parts" and "Fuel Cost" ... what I want is that I will still display those two
tables separately for easier review of the data.

[June7]

Options are still the same.

With option 2 (UNION query), use report Sorting & Grouping features to organize data into the two groups. However, this approach will not allow the two groups to be side-by-side. For that, use option 1 - report/subreports.