Help building Access Report with multiple tables.

I have built an Access database and I need assistance on creating a report.  My database is a Product Price catalogue.  I have tables for Items, and two separate tables for related itme ( support & maintenance).  In addition, I have tables for Product Categories, Product Brands & Requirements.  I managed to build a couple of forms that show the Item codes and their related support & maintenacnce codes however I am really struggling with the report.  The report needs to show Category, Product Brand (and their requirements)  then each Item code, desc, price etc. and then their related support and maintenance codes if they have any then the next item code, support maintenance and so on till it hits the next Product Name..  I just need some direction as I have tried several ways of getting the information on the report with limited success.  I am questioning, at this point, whether I even have the database build correct.
CudaGalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jefftwilleyCommented:
Hi cudaGal,
Does your name imply that you drive a Cuda? if so...I'm SOOO jealous!

ok, about your issue. Are you able to build a query that brings all of your data together?
0
CudaGalAuthor Commented:
Yes, my name does imply that I drive a 72 Cuda Ralley Sport with a 340 (license plates are CUDAGAL).

About the query, I think I finally got a query to bring in all the information.
0
jefftwilleyCommented:
if you now have your query...you can use the Report Wizard to begin creating your report. it will ask you what to use as far as data is concerned, just pick your query. it will ask you about sorting and grouping as you go, so you'll have the option to tell it what is the top level, then what goes underneath that, etc...
Once you get the basic shape and size going...you can add additional elements if you need to in design vew. Are you planning on doing any kind of totaling? If not, just follow the wizard and come back if you run into issues.
J
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

jefftwilleyCommented:
>>>72 Cuda Ralley Sport with a 340
pictures...we need pictures!!
so jealous
0
CudaGalAuthor Commented:
I can't seem to get my query working right.  I am getting duplicate records when I try to add Product Name and Category to the query.  I just need the Category description plus the product name and requirements so that I can sort by them.
0
CudaGalAuthor Commented:
I want my report to look like this...
Category
        Product Name  -  Requirements
                  Item Code
                        Support code
                        Maintenace code
                  Item Code
                          Support Code
                          Maintenace Code
Category.....

I have a separate table for Category, Product Name, Requirements, Item Code, Support Code & Maintenace so that they will be easy to manage.  Support & Maintenace Codes are Children of Item Codes.  Product Name is a child of Categories.  Requirements are a child of Product Names, Items are a child of Product Names.  and I need to pull only active codes.

I think the db is designed right I just can't get the report built.  Do I need to use subreports to get what I want?

BTW - the Cuda is being restored so I do not have any good pics of it yet.  It's an original numbers car so we are restoring it from the ground up.  81,000 original miles.  :)
0
CudaGalAuthor Commented:
Also, I built a form that shows Iem code and used subforms to show the related support and maintenace codes.  It works nicely.
0
jefftwilleyCommented:
your query SHOULD show duplicates. Your report can/does filter those though with the grouping levels..that's what that's for.

Go ahead and create that query where those items are repeating...that's good.

Now build the report on top of that. Selecting the Category as the first grouping, Product Name as the second...then Item Code as the third.

You can also sort in the report (which actually overrides any sorting you're trying to build into your query).

0
CudaGalAuthor Commented:
I am extremely frustrated.  I got a report bulit however i am not sure if it's correct.  I just do not know what I am doing.  I will try again later.
0
jefftwilleyCommented:
If the data in your report doesn't contain any sensative information, you can post it here

www.ee-stuff.com

and I would be able to take a look at it..
J
0
CudaGalAuthor Commented:
The data is our Corporate Sales Price Book so it is sensitive data.  Is there a way to post it long enough for you to look at it then remove it?
0
jefftwilleyCommented:
this is the e-mail address of the moderator if that site...
GhostMod@experts-exchange.com

I don't know if you can have things deleted...I don't see why not, but I've never tried.
0
CudaGalAuthor Commented:
I emailed them to see if it can be deleted as soon as you look at it.
0
jefftwilleyCommented:
You asked me earlier about using sub-reports. The answer to that question is yes...you can do that. And if the goal is to output a simple snapshot, or simply print the report, then that's a very good way to do it. If you ever planned to export the report out to .rtf, or html, etc...sub-reports act very flaky, and often times will not output at all. It seems you were able to get the "look" and grouping you wanted using forms and subforms...reports are almost exactly like forms except for the sorting and grouping options. The way you have the report layed out though...if you can get all the data into one query, then that will actually be easier than using sub-reports.
J
0
CudaGalAuthor Commented:
Thanks.  My brain is toast at the moment.  I will look at the query again after lunch.  Maybe I can get it to work.  I will let you know.
0
CudaGalAuthor Commented:
J-

I modified the information in my Price Book and uploaded it to ee-stuff.  Let me know what you think I should do with it.
0
CudaGalAuthor Commented:
Can someone take a look at my database, please.
0
jefftwilleyCommented:
I'm working on it now...sorry, busy day at work today...Home now.

What is the name of the query and the report you were working on?
0
jefftwilleyCommented:
using the CateProdReqItemsActive query, there are too many fields for the report.

I am going to attempt to break it down into a main report for Categories, and a subreport for Proucts, with a sub inside of that for items.

CateProdReqItemsActive query returned 0 records

0
CudaGalAuthor Commented:
Test Query brings in the category, product name and items.  I could not get support, maintenance, implementation & requirements to come in.

I had one that brought in everything yesterday however I must have modified it.  I think that it was the one you were trying ti use that returned nothing.
0
jefftwilleyCommented:
if I build this model, will that work...you can add in anything else you want after?

Category
        Product Name  -  Requirements
                  Item Code
                        Support code
                        Maintenace code
                  Item Code
                          Support Code
                          Maintenace Code
Category.....
0
CudaGalAuthor Commented:
Yes, that would work perfect.
0
jefftwilleyCommented:
right now, I've got the Category an Prouct Name in one query.
I created a second with the Item Code and support and maintenance codes.

I created a main report out of the first query...and a subreport out of the second.

BUT

If all you want is those items...and no details...I can put it into one query?

J
0
jefftwilleyCommented:
I uploaded here

https://filedb.experts-exchange.com/incoming/ee-stuff/3156-MyPriceBook_JT.zip

I built a basic query...and report based on the framework above.

To add detail items, just add them to the query first, then open the report, grab it's field list, and drag down the fields you want to add in the appropriate area.

You might want to change font, page layout (lanscape vs portrait) and any coloring you want. After all, it's your report :o)

If you want me to add or change anything...let me know.
J
0
CudaGalAuthor Commented:
Jeff-

The report looks great.  I believe I can use this as a base for what I need.  

I suppressed the printing of duplicate Support & Maintenance Codes.  Now I need to know how to suppress the Support & Maintenance Labels if the data field is either supressed because it's a duplicate OR is blank.  Is ther an easy way to that?

Thanks so much for your help on this issue.

Cuda
0
jefftwilleyCommented:
in the on format event for that section, you can put a statement something like this.
----------------------------------------
If nz(me.[fieldname],"")="" then
me.[mylabel].visible = false
else
me.[mylabel].visible = true
end if
--------------------------------------
nz is used to check for null values, and replaces the null with the value after the comma.
that will fire for each line in that section and evaluate the value in whatever field name you use. I'm not sure about the "supressed" part though.
0
CudaGalAuthor Commented:
I added the statement above to the On Format in that section.  I recieve this error when I try to run the report...
Microsoft Access can't find the macro if nz(me
The macro (or it's macro group) dosn't exist or the macro is new and has not been saved.
Note that when you enter the macrogroupname.macroname syntax in a argument, you must specify must be saved the name the macro's macro group was last saved under.

Obiviously, I did something wrong. :)
0
jefftwilleyCommented:
Paste in what you actually used for code
0
CudaGalAuthor Commented:
if nz(me.[SupportCode],"")="" then me.[SupportCode_label].visible=false else me.[SupportCode_label].visbile = true end if
0
jefftwilleyCommented:
>>if nz(me.[SupportCode],"")="" then me.[SupportCode_label].visible=false else me.
[SupportCode_label].visbile = true end if
                                  ^^^^
That's spelled incorrectly
0
CudaGalAuthor Commented:
I corrected the spelling error.  I still receive the same error message...Microsoft Access can't find the macro if nz(me.....
0
jefftwilleyCommented:
try using isnull

if isnull(me.[SupportCode]) OR me.[supportcode] ="" then
me.[SupportCode_label].visible=false
else
me.[SupportCode_label].visible = true
end if

I don't know why NZ is not working for you. Have you ever used it before?
0
CudaGalAuthor Commented:
I have not used it before.  I will try the isnull.
0
CudaGalAuthor Commented:
I tried to use your If IsNull statement and it will not take it.  It says I am missing () for the If statement.  I tried to enter () around the code but it still doesn't like the statement.  What am I doing wrong?
0
jefftwilleyCommented:
Hey,
You've got an issue with your MS Access. You have some components missing in your references...or you've got something wrong with your installation. NZ, IsNull are inherent functions that Access uses without you having to define them.

Read here

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21951732.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CudaGalAuthor Commented:
Thanks.

This is a desktop install from Corporate IS.  I am a field employee, so I will have to have them dealk with it if it requires a re-install.

Thanks again,
0
CudaGalAuthor Commented:
I am trying to create a report without having to use the NZ or IsNull functions.  So I decided to design the report in Landscape so that I could change the layout to the following...
Category
     Product Name
           Item Code            Support Code     Maintanence Code
         (one occurance)   (If Exists)           (If Exists)
                                          (could be multiples for on item code)
I can get them all on one line however I am getting duplicate item codes and blank lines?  Am I back to the same problem, needing to use the NZ function?
0
jefftwilleyCommented:
I believe so yes. The problem is filtering out the records that don't belong. An without having the tools to do so...you are crippled. Any word from your desktop support guys?
0
CudaGalAuthor Commented:
No.  I have not heard from them.  They are busy with a large project at the moment.

0
CudaGalAuthor Commented:
I edited the statement in the event window.  It was giving me an error because the 'else' was on the same line as the 'true' statement.  It now works....  woo hoo.

So I assume I have to write another statement to do the same to remove the Maintenance Code if it is blank.

Thanks so much for your help!!!
0
CudaGalAuthor Commented:
That did in fact cause them not to display on the report, however, I now have blank lines where the labels used to print (1/2 page of blank lines).
0
jefftwilleyCommented:
is this on the report or the sub-report (scratching my head trying to remember the set-up)

if it's the subreport, there may not be any matching records. If that's the case you have an option to not show it if there are no records.

Are you going to have to go back in once this is done and change it again after you get your NZ or isnull back?
0
CudaGalAuthor Commented:
I got the NZ to work when I opened the event procedure.  Was that where I was suppose place it all along?

It's in the detail section where all the blank lines are.  How do I tell it not to show if there are no records?
0
jefftwilleyCommented:
before we do that...you've shrank down the white space in your detail section right?

Truth is, I'm not sure why you're getting half a page of blank lines...

Make sure the detail section's property setting for "force new page" is set to none.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.