Help on generating custom Reports from a query 1

[ktchan741]

I need advise and help to generate a report from a query.

I created two Tables, a query and a form:

Table_Parts
Table_Usage
Query_Parts_Usage
Form_Parts

Table_Usage is a sub-form of Table_Parts in Form_Parts

Table_Parts comprises of the Qty, Parts No (Prime and Sub) and Description. A prime part is make up of other sub-parts.
Different sub-parts can be used in different Prime Parts.

Table_Usage comprises of the Usage, Qty, Parts No (Prime and Sub). The table captured the number of times the same sub-parts is used in different prime parts.

Query_Parts_Usage comprises of Table_Parts and Table_Usage. They are related by Parts No. When I run the query, the following is output:
Example

Usage Qty Parts No Description
- - -1 Car A
- - -2 Car B
-1 1 01VTI VTI Engine
-1 4 65N01 Tyres
-1 1 78N02 Steering Wheel
-1 1 80N01 Windshield
-1 2 90N02 Headlamp
-2 1 01CTI CTI Engine
-2 4 65N01 Tyres
-2 1 78N02 Steering Wheel
-2 1 80N01 Windshield
-2 2 90N02 Headlamp

How can I create a Report that can group those sub-parts that is used on a prime part together. I am trying to output a report that looks like this (based on the query):

Usage Qty Parts No Description
*****************-1 Car A**********************
- - -1 Car A
-1 1 01VTI VTI Engine
-1 4 65N01 Tyres
-1 1 78N02 Steering Wheel
-1 1 80N01 Windshield
-1 2 90N02 Headlamp
*****************-2 Car B**********************
- - -2 Car B
-2 1 01CTI CTI Engine
-2 4 65N01 Tyres
-2 1 78N02 Steering Wheel
-2 1 80N01 Windshield
-2 2 90N02 Headlamp
***********************************************

Currently, I am only able to output a report that looks like this:

Usage Qty Parts No Description
- - -1 Car A
- - -2 Car B
-1 1 01VTI VTI Engine
-1 4 65N01 Tyres
-1 1 78N02 Steering Wheel
-1 1 80N01 Windshield
-1 2 90N02 Headlamp
-2 1 01CTI CTI Engine
-2 4 65N01 Tyres
-2 1 78N02 Steering Wheel
-2 1 80N01 Windshield
-2 2 90N02 Headlamp

Appreciate advise.
Thanks,

 

[Steve101]

I think you're looking at a "Bill of Materials" type application, which can get quite complex, and require recursive programming.

However, since it appears that in your example, you're only interested in two levels of reporting, you should be able to achieve this via your query, providing you include both the parent AND the child part numbers (and associated description and usage fields) in the query.

Once this is done, you can use Grouping (based on the parent part field) within your report to produce the required output.

Hope this helps,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ktchan741]

Hi Steve,
Can you elaborate how this can be done?
I am a beginner in MS Access and does not quite get what you mean. Where can the Grouping be found?
Thanks,

 

[nberryman]

Go to Reports and select New. Use the report wizard and check each step carefully. You will be prompted to select one or more fields to group on. This would appear to be the Car field. Check the summary options as well and that should get you going



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[dhookom]

You can add the Query_Parts_Usage TWICE to a new query. Join the PartsNo from one to the Usage from the other. The fields from the PartsNo side of the join will be the Cars and the other side will be the parts. You should then alias your columns so you know which side of the join they come from (no columns should have the same name in your datasheet view).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ktchan741]

Hi Duane,
Thanks for the advise. From your post I am to getting closer to what is expected. The following is the output:

Usage Qty Parts No Description
- - -1 Car A
-1 1 01VTI VTI Engine

-1 4 65N01 Tyres

-1 1 78N02 Steering Wheel

-1 1 80N01 Windshield

-1 2 90N02 Headlamp

- - -2 Car B
-2 1 01CTI CTI Engine

-2 4 65N01 Tyres

-2 1 78N02 Steering Wheel

-2 1 80N01 Windshield

-2 2 90N02 Headlamp


The only problem is the prime part (-1 and -2) is always repeating itself on every subparts (I am able to remove them using Hide Duplicate in the properties). However, this does not solve the problem when the sub-parts goes into the next page. Any advise? Besides is there anyway to create a dividing line or "*" to split or differnitate each group of prime parts? Thanks.

 

[dhookom]

Did you group first by the Car PartsNo and create a header for this group? You can then format the group header to look exactly like your original specification. Other than that, I don't see any real difference between what you originally requested and what you are getting from the query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ktchan741]

Thanks for all the good advise. I have got it.
Happy New Year.