Access report issues

[Tekay]

I have been stuck on a report issue that I can't find the answer for.
I am working on a manufacturing database. I have an item table that is linked to a b.o.m. (bill of materials) and then to a factory cost quote table.
A typical scenario is that itemA will require two or more components from the b.o.m. table.
I will send ItemA out to get cost quotation from manufacturers then key in the varoious costs that I get back.
I then need to report this data to Management so that they make the best decision on whom to manufacture itemA with.
The way I would like to setup the report is to show itemA and it's related descriptions, then all the components and their related description then all the Factories (in the detail area).
I have played with the sorting and grouping until I am frustrated. I can show all the data, but sometimes I get the first coponent, then the first factory, then the second component then the second factory etc.
I only want to show the factory detail ONCE...
Please help me figure what I need to change/do in my approach.

Thanks all.
Access2000/Xp

 

[THWatson]

I'm not fully clear on the make-up of your database, however I'll take a stab at something.

I think if you set up your Sorting and Grouping on the field you want to show as the main field...perhaps itemA...and then have a Group Footer for this field, that will keep all the various itemA together.

You may want to do a secondary Sort and Group on another field. How you want to show your data will determine that.

In any event, for the field you want only to show once...factory detail...set "Hide Duplicates" to Yes.

Hope that helps a little.
Tom

 

[Tekay]

Tbanks Tom, (my namesake).
Actually, I find it very difficult to accuraely describe the problem. But let me try again.
I have several cost that is related to one item (itemA) and I have several components that are related to the same item. I created a Query to link all this up and it works fine. (at least I think so)
When I make my report, I want to show Item once, so I created a sorting with group heaader, there I put the item # and it's description. Next I created another sorting group and made a header for the component(S) that this item is made up of. Then finally I put the factories and the cost that they have quoted to me for the item.
Right now my report can show me the multiple factoris in the details, but would not show the components together.
This is what I would like my report to look like.

=ItemA Header=
Item# 12344
Description : Test item

=Component Header=
ComponentA: metal shell (100% aluminum)
ComponentB: metal washer (100% steel)

=Details=
FactoryA $2.50
FactoryB $3.00
FactoryC $5.00

=ItemA Footer=
end of Item A


As you would know, I can get the factory details to show together, but the components are not showing together, the first one shows, then the factory details, then the second one then the factory details again etc. Hiding duplicates doesn't help.
Hope this makes better sense..

Thanks All.
Tekay

 

[THWatson]

Tekay
I am having trouble seeing this in my head. But I am wondering a couple of things...

1. Are you trying to do this in one main report, or are you using a main report plus subreport(s)?

2. Since, if I understand correctly, you want the components and the factory details to show together, is it possible you need to do a Union Query to join those before you do the query upon which the report is based?

Tom

 

[Tekay]

I have never considered sub reports, don't know the benefit. all this is in one report. Also don't know benefit of union query..
I am just a part time in-house developer with some 'user' knowledge and some desire to make some tools for my job so I am not working with much..

If you can show me how this may benefit me in my delima I'll be happy to try it...

In the meantime, I am going to do some research on the sub report..I did hear about it but never checked it out.

Thanks Tom

Tekay

 

[THWatson]

Tekay
It is really tough for me to suggest anything further without knowing something more about the design of your tables, and how the Items, Bills of Materials, and Cost Quotes are linked.

Along with Office, there is a sample database called Northwind. It shows how Customers, Orders and Invoices are linked, and how reports and subreports are made from them. It's a handy resource.

Also if you go the Microsoft Knowledge Base, you can download sample queries, forms and reports. They give tips about a number of things.

As for Union queries, they are used to bring together data from two different tables, so it can be correlated and more easily managed.

Good luck.

Tom

 

[THWatson]

Tekay
I had a thought. If you want to send me your SQL from your query that drives the report, and I could have a look at it and see if I can spot anything from that.

Just open the query in Design view. Then from the View menu click on SQL. Copy the SQL text and post it in a reply here.

Tom

 

[Tekay]

Here you go Tom,
Thanks for sticking with this for me..I am starting to see that the error looks to be at the query end. Still don't know how to fix it though. I viewed the result of the query and saw the double entries.

Thanks again bud!
Tekay
-----------------------------------

SELECT [Bill of Materials].ItemfabID, [Bill of Materials].UsedFor, [Bill of Materials].fabricItem, [Costing Data].Factory, [Costing Data].YY, [Fabric Group].fabric_content, [Fabric Group].Supplier_name, [Fabric Group].fabric_cuttable_width, [Fabric Group].fabric_content, tblImage.ImageId, tblImage.reference
FROM ((tblImage INNER JOIN [Costing Data] ON tblImage.ImageId = [Costing Data].ImageID) INNER JOIN [Bill of Materials] ON [Costing Data].ImageID = [Bill of Materials].ImageID) INNER JOIN [Fabric Group] ON [Bill of Materials].fabricItem = [Fabric Group].fabricID;

 

[THWatson]

Tekay
Just picked this up...4 p.m. my time.

I'll have a look at it and post back. Might be this evening.

Tom

 

[THWatson]

Tekay
I do notice one thing in the SQL. You have [Fabric Group].fabric_content showing twice. There may be a reason for this, but I'm not sure.

You may have to change one of your joins. Not sure. Just hunches I am making.
For example, in a one-to-many join, you sometimes want show all from the "one" side and only those from the "many" side that are equal. Also, in one database I have, in a Statement of Accounts report, I want to be sure that the Customer only shows once, so I set Unique Record to Yes.

I tried to create tables, using the SQL as a reference, but am unsure about the table field data types, so am a bit at sea. For example, why type of data is the YY field in [Costing Data].

By the way, as an aside, if you are naming tables, you are better off not to put spaces in the names. It avoids problems. I see you named one table tblImage. That's the best way.

If you want me to persist in this, I will need you to do one of the two following alternatives...
1. Post back each table name, exact field name, and data type of each field. (I assume, for example, that the ImageID field is an AutoNumber field, but I'm not sure, And I don't know what the link is between the ImageId in tblImage and ItemfabID in [Bill of Materials].) Also, give me a little bit of example data for each table to work with.

2. Make a copy of your database, remove any sensitive data from it, reduce it down to a few records, and send it to me at twatson*at*sentex.net (change the *at* to the normal symbol...I have shown it this way because otherwise the mail address gets removed from the posting)

Tom

 

[Tekay]

Tom

Can't thank you enough..have sent you an email with more details.

Tekay

 

[THWatson]

Tekay
Just picked things up. Have been away all day. Will look at it this evening.
Tom

 

[THWatson]

Tom
I have been playing around with the db you sent. I keep getting hung up on the "usedFor" item, because it doesn't always exist. Maybe it's not causing a problem for you, because your db will have more data, but I get errors.

I also get an error which says that [FabricUsage].[fabric_usageId] doesn't exist. But I can't find any reference to this in your tables, so don't know where that comes from. I get that error when I try to construct a query, and also when I try to construct a report.

I tried to make a query with the SQL you sent me, and the same problems occurred there.

You have quite a complex db, and there is no logical reason you shouldn't be able to get the report you want. It's a matter of constructing the query properly and then doing the Sorting and Grouping in the report correctly.

I have re-read your original post and your notes, and from them I gather that the ImageID (290, 291, etc.) is the same as what you call ItemA from your original post. So that is the item on which you wish to group. You can choose to keep all 290 items together, and then all 291 together, etc., by setting the "Force New Page" property to "After Section" in the ImageID footer.
You can also keep every different instance of 290, for example, on a separate page by setting the "Force New Page" property to "After Section" in the Detail section.

I am wondering about a few things...
1. Your original SQL included an Inner Join on ImageID. I am wondering if there should be a Left Join. So that all ImageID's show up, but only Bill of Materials items if they are equal to the ImageID join.
2. Maybe you need to make more than one query, joining some things, and then bringing them all together.
3. For some reason, I'm still thinking about subreports. Let me explain why.
One of my databases has an Invoice system. Some customers have only 1 invoice, while others have several. Also, an invoice can have 1 item or several items (the same as your garment can have 1 material or several). So my Invoice Report shows the customer, but then a subreport brings in the respective Invoice details.
Also I have a Statement of Accounts report. This shows each Customer only once, but then a subreport shows the totals for each respective Invoice for that customer.

Anyway, you will know best what you want, and need.

Because of the errors with the "usedFor" field, I ran into a dead end, so can only offer the hunches I have offered above. I'm feeling I'm not being as helpful as I would like to be.

Tom

 

[Tekay]

Tom,

That's what you get from us 'part timers'. I can understand the reason for your dead end. If you don't mind, I will find some time later today and reconstruct the database the way it works now..sorry, I tried to keep it lean so as not to send you a huge file. I'll make sure it all runs smoothly then resend. I'll even reconstruct the report the way I am getting it and the query that I used. I am afraid I haven't been as thorough as I should have been.
Is it okay if I resend?

Thanks
Tekay.

 

[THWatson]

Sure. Resend away.
Tom

 

[Tekay]

Tom,

You wouldn't believe it, with your righ guidance and while trying to make an error free sample database to send to you, I finally got it!
It was indeed a sub-report that was required along with multiple queries.
There is stil hope for us part-time developers yet! of course with you guys around.

Hope I can call on you anytime..
Thanks a bunch!
Tekay

Wish I knew how to give you a star..but here goes * hehe!

 

[THWatson]

Tekay
Hey...glad you got it!

Don't worry about the star. I'll accept the * hehe.

Many times I have received help from others on this site, so I also try to return the favour.

Good luck. Send me a note to my e-mail address if you need to.

Tom