Newbie needs a direction

[Ed from AZ]

Jeff Boyce bravely tried to help me:
http://groups.google.com/group/microsoft.public.access/browse_thread/thread/35bf6e83d16813ec?hl=en
and I greatly appreciate all his effort. Unfortunately, Jeff or
anyone else, I think I don't know enough about what I am wanting to do
to get there from what I have been given so far.

Here's the basics of what I want to accomplish:
Think of something like an auto repair shop. For each invoice, you
have info about one car, with many lines of work time and many lines
of parts replaced. I want to wind up with a report that shows for
each invoice number the one line of data about the car, and all of the
many lines of data about the maintenance and parts associated with
that invoice. Then we do the next invoice number, and so on.

Like so:
Inv # 1 Make Model Year

Mech 1 Time
Mech 2 Time

Part 1
Part 2
Part 3

Inv # 2 Make Model Year

Mech 1 Time
etc

I have four Excel tables. One table has the invoice number with a
single row od data for that number. The other three tables may have
multiple lines of data for each invoice number.

I have made unworkable attempts at creating a query that will join and
present this data in an understandable way. I can pull all four
tables into a query and set relationships to the invoice number field
in all of them. Beyond that, though, I often get nothing when I try
to run the query. I can query each table individually, but can't
figure out which direction to go in to learn more about how to
accomplish my end report.

Can someone drop-kick me towards the next step I need to take?

Ed

 

[Guest]

The other three tables may have multiple lines of data for each invoice
number.
You did not say what was in the three tables, only that they had the invoice
number.

Not knowing I suggest using a union query to combine the three.

Use the the first table left joined to the union query for your report.

In your report set the hide duplicates property to Yes for the fields of the
first table.

 

[Guest]

How do you have your "4 table" query set up? In particular how are you doing
your joins?

It sounds to me like you're maybe using straight inner joins and bumping
into tables with no matching invoice numbers... thus returning nothing.

If you could provide a little more detail about each table that would help
as well.

 

[Ed from AZ]

Hi, Lance.

Not sure what kinds of details you want. The invoice number is in
each table. I used the Relationships pane to join the invoice number
field of all tables.

I created a query based on only two tables - the Main table with one
line of info for each invoice, and the Maintenance table which has
sseveral lines per invoice. I can run it and get a single table with
mulitiple lines for each invoice - the unique data repeated on each
line and each line having one line of the Maint data. Does that make
sense the way I said that?

I then went into the Report wizard and it puts out just one line for
each invoice. i want the one line of unique data and every line of
the multi-line data.

Am I heading in the right direction?

Ed

 

[Guest]

I created a query based on only two tables - the Main table with one line
of info for each invoice, and the Maintenance table which has sseveral lines
per invoice.
What are you doing with the other two tables?

 

[Guest]

In the report wizard there should be a section about grouping, put your
invoice in as a group level.

Then in design view move all the "unique data" fields to the same report
section as the report ID, that will make them appear only once. Then in the
section below it you can arrange the rest of your data.

You can have many different group levels in an Access report. For example
if you wanted to further break the report down by mechanic, you would just
add the mechanic ID as a group level.

 

[Ed from AZ]

Hi, Karl. I haven't included them yet. I though I would start with
just these two, figuring that if I couldn't get it with two, four
would be a real mess!!

Ed

 

[Ed from AZ]

Oh, wow! I'm actually doing something!!

Okay - I'm using two tables in one query, and I got my unique fileds
into one group and the multiple fields into another group. And I can
play with the formatting to make it look close to how I want.

Two questions:
(1) Can I put a page break in the report at each new "unique data"
line!

(2) To integrate my other two tables, do I put them into the same
query? Or should I create a different query for each multiple-line
table and put those fields into the report?

Also, is there a good basics book that would cover all of this?

Ed

 

[Albert D. Kallal]

While you have several posters here telling you to brush fire the query
builder and joins several tables together to make the report, in fact there
actually giving you a bad or wrong advice.

when you have one table that you need to show some data from and then need
to show many records from another related table that recommended approach
here is to use a sub report.

so, we have:

Like so:
Inv # 1 Make Model Year

the above fields and perhaps additional information about the invoice number
customer from knows what you want can be displayed on the report. you can
base that report directly on the table, you don't need to build a bunch of
queries here at all.

Mech 1 Time
Mech 2 Time

for the above information you simply build a sub report that was played the
mechanics time. this sub report can be joined on the invoice number. this
sub report will automatically expand to as many lines of detailed as you
need.

Part 1
Part 2
Part 3

again to display the part numbers you simply build a sub report that
displays the part numbers. This sub reporting can be joined on the invoice
number (in fact use the wizard to insert the sub report, as it'll actually
ask you what fields in this case invoice number to use for the relational
join). This report will automatically expand to as many lines of details as
you need.

what this means is is that you don't have to use relational SQL queries and
joined to build this report. the time you have a related set of data or
record you want to display, you simply insert a sub report that displays its
relational data. you will not have to build queries, you will not have to
build a relational joins. your main report, and the two sub reports will
simply be based on their appropriate tables.

Hence, you do not need to use relational sql joins to build this report. Use
sub reports in the report report writer, and it will do the joins for you.

 

[Ed from AZ]

Thank you so much, Albert!! That works great! I assume that from
this step, all I do is create a separate report for each table, and
drop them into the report as desired.

Very, very nice!

Greatly appreciate the boost!
Ed