Solved

Help building Access Report with multiple tables.

Posted on 2007-04-10
44
3,236 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:CudaGal
  • 24
  • 20
44 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18883992
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
 

Author Comment

by:CudaGal
ID: 18884508
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18884556
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18884560
>>>72 Cuda Ralley Sport with a 340
pictures...we need pictures!!
so jealous
0
 

Author Comment

by:CudaGal
ID: 18889450
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
 

Author Comment

by:CudaGal
ID: 18889517
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
 

Author Comment

by:CudaGal
ID: 18889536
Also, I built a form that shows Iem code and used subforms to show the related support and maintenace codes.  It works nicely.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18889667
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
 

Author Comment

by:CudaGal
ID: 18890732
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18890861
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
 

Author Comment

by:CudaGal
ID: 18890890
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18891010
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
 

Author Comment

by:CudaGal
ID: 18891052
I emailed them to see if it can be deleted as soon as you look at it.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18891123
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
 

Author Comment

by:CudaGal
ID: 18891164
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
 

Author Comment

by:CudaGal
ID: 18893965
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
 

Author Comment

by:CudaGal
ID: 18901878
Can someone take a look at my database, please.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18902141
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18902196
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
 

Author Comment

by:CudaGal
ID: 18902255
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18902407
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
 

Author Comment

by:CudaGal
ID: 18902419
Yes, that would work perfect.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 18902429
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18902647
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
 

Author Comment

by:CudaGal
ID: 18904769
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18905090
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
 

Author Comment

by:CudaGal
ID: 18909269
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18909439
Paste in what you actually used for code
0
 

Author Comment

by:CudaGal
ID: 18917032
if nz(me.[SupportCode],"")="" then me.[SupportCode_label].visible=false else me.[SupportCode_label].visbile = true end if
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18917745
>>if nz(me.[SupportCode],"")="" then me.[SupportCode_label].visible=false else me.
[SupportCode_label].visbile = true end if
                                  ^^^^
That's spelled incorrectly
0
 

Author Comment

by:CudaGal
ID: 18918448
I corrected the spelling error.  I still receive the same error message...Microsoft Access can't find the macro if nz(me.....
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18918835
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
 

Author Comment

by:CudaGal
ID: 18919140
I have not used it before.  I will try the isnull.
0
 

Author Comment

by:CudaGal
ID: 19009031
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 19009188
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
 

Author Comment

by:CudaGal
ID: 19010388
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
 

Author Comment

by:CudaGal
ID: 19016313
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 19016991
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
 

Author Comment

by:CudaGal
ID: 19017418
No.  I have not heard from them.  They are busy with a large project at the moment.

0
 

Author Comment

by:CudaGal
ID: 19017713
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
 

Author Comment

by:CudaGal
ID: 19017752
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 19017775
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
 

Author Comment

by:CudaGal
ID: 19018213
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 19018309
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now