UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Reports Problems With Subreports, Access 2003    
 
   
Pencil
post Sep 5 2019, 08:00 PM
Post#1



Posts: 17
Joined: 5-November 11



I am really stumped on this report thing. I have set my report up with two sub reports coming from two different queries. One is for work that has been completed and the other is parts that have been charge out for the completion on the work.

The problem I am having is it should only be a report of 3 pages but it keeps growing in page numbers as many as 10 pages now.

I have checked every thing I can think of and what is strange in the bottom subform which shows part if I start with one part number charge out the report is fine with 2 pages. If I add another part to the list the report now is 4 pages and gets basically double the complete report with the work completed and the 2 parts charge out.


Now if I add a 3rd part to the subform the report is now 6 pages with triple the report.

I have check the can grow can shrink, report footer, headers but just can figure this one out. Hope this kind of makes sense in some strange way

I am sure someone out there smarter then me can help

Any help work greatly be appreciated.
Go to the top of the page
 
theDBguy
post Sep 5 2019, 08:22 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


Hi. Are you able to post a sample copy of the db for testing?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Pencil
post Sep 5 2019, 08:42 PM
Post#3



Posts: 17
Joined: 5-November 11



thanks DB guy for your interest in this not sure if I can post the DB on the forums or not.
Go to the top of the page
 
Pencil
post Sep 5 2019, 10:20 PM
Post#4



Posts: 17
Joined: 5-November 11



DB guy here is what I am working on and the problem with the report it is the one marked invoice or in the reports its called Rpt_Customer_Invoice
Go to the top of the page
 
theDBguy
post Sep 5 2019, 11:07 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


Hi. I don’t see any attachment.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Pencil
post Sep 6 2019, 10:08 AM
Post#6



Posts: 17
Joined: 5-November 11



Hi DB Guy the file is too big as it says the file can only be 2MB
Go to the top of the page
 
RJD
post Sep 6 2019, 10:41 AM
Post#7


UtterAccess VIP
Posts: 10,097
Joined: 25-October 10
From: Gulf South USA


Hi Pencil: PMFJI, but have you tried cutting the db down to just relevant objects and enough data to test, and then compacting and zipping the file? That usually gets the resulting file under the 2MB limit, and meets the requirement for posting a zip file.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Pencil
post Sep 6 2019, 12:11 PM
Post#8



Posts: 17
Joined: 5-November 11



Hi Guys, well I was able to get this file smaller to attach it took some doing for sure.

If you go from the main menu and open the Completed W.O. and then select WO# 3339 the information is on my forum.

If you look under the Pre-View Work Order everything is fine here it shows all the information on three pages, but this is not the invoice I would like to print to send to the customer. If you click on the button Print Invoice it shows 6 pages and the report is triple up for some reason…

Thanks for all the help on this matter as I am really at a loss here.



Attached File(s)
Attached File  db2.zip ( 1.6MB )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Sep 6 2019, 04:01 PM
Post#9


UtterAccess VIP
Posts: 10,097
Joined: 25-October 10
From: Gulf South USA


Hi: Well, you had a few things causing doubling (and more) of the invoice data. First, in the main report query you had a table tbl_Parts_List without a JOIN, and, in any case, that table was not needed for the main report part. Second, you included tables and data in the main report query that were not necessary in that part, causing multiplication of results. Third, the main query did not have criteria to limit to just the WO in question. Plus some tweaks to the aggregate type in a couple fields.

In addition, there was no way to search for and limit a WO in the second form.

I corrected/added these, and some other tweaks, and this seems to work better. Take a look and see if this is closer to what you need. There are probably more things I did or that need to be done, so take a hard look.

HTH
Joe
Attached File(s)
Attached File  UA_20190906_Pencil_Rev1.zip ( 1.41MB )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Pencil
post Sep 6 2019, 06:09 PM
Post#10



Posts: 17
Joined: 5-November 11



Wow Thanks so much RJD and I see where I went wrong in that query which now makes sense for the double up and triple ups.

I see you added a search all and search W.O. number you mention other tweaks but I have looked and not sure what other changes you might have made?

Not sure if I like the idea of search all and search W.O. number will leave this for now and see if it works. I appreciate these changes, but there is a cmd button to search by W.O #. I will give that a serious try.. At least my report seems to function now properly.

I have another question and not sure how I am going to implement this. I need to charge a mark-up on my parts and it is going to be based on customer rating. This would be based on whether they are paying within 30 days or later then 30 days. The mark up on this would 15% and 20% for longer paying customers.
Any ideas on how a person should best apply this?

Thanks again for all your help and it sure is nice to have a forum to look for help when using access. I am still learning all the time with access and don’t know much about VBA but have been watch a lot of video on it, book a book the other day also..
Go to the top of the page
 
RJD
post Sep 6 2019, 06:55 PM
Post#11


UtterAccess VIP
Posts: 10,097
Joined: 25-October 10
From: Gulf South USA


QUOTE
you mention other tweaks but I have looked and not sure what other changes you might have made?

I didn't record them all - just look around and compare with your previous version. I usually just make minor tweaks as I go along to satisfy my way of doing things.

QUOTE
Not sure if I like the idea of search all and search W.O. number

The All option is used to revert to all WOs after you have selected one. The Search is to find the WO you want. I did not see any other way for you to zero in on a particular WO (but perhaps I missed something). The WO field on the Form is bound, so cannot be used for searching.

QUOTE
I need to charge a mark-up on my parts and it is going to be based on customer rating.

I think the best way to do this is to create a new table of mark-ups with indications of customer rating/payment cycles. You can then add a field to the (probably) WO to indicate the mark-up and populate the field from the new table (e.g. DLookup) when the rating or cycle is entered. Then use the resulting markup in your calculations.

Good luck with your db development, and let us know if we can be of further assistance.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st October 2019 - 03:30 PM