473,320 Members | 1,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query in Microsoft Access 2013

I need to add a phrase into the following SQL that will performed the following: If the sum of [VT] reaches 20 insert a line or some type of divider at that point. I essentially need the report to divide VT into sets of 1-20, 21-40 etc....

TRANSFORM Count([Weld Log].Activity) AS CountOfActivity
SELECT [Weld Log].[Welder/Insp], [Weld Log].[Spool #], [Weld Log].[Weld #], [Weld Log].WPS, [Weld Log].Type, [Weld Log].Date, [Weld Log].[Pass or Fail], [Weld Log].Comment, Count([Weld Log].Activity) AS [Total Of Activity]
FROM [Weld Log]
WHERE ((([Weld Log].Type)="BUTTWELD") AND ((([Weld Log].[Pass or Fail])="PASS" Or ([Weld Log].[Pass or Fail])="Fail") And (([Weld Log].[Pass or Fail])="Pass" Or ([Weld Log].[Pass or Fail])="Fail")))
GROUP BY [Weld Log].[Welder/Insp], [Weld Log].[Spool #], [Weld Log].[Weld #], [Weld Log].WPS, [Weld Log].NDE, [Weld Log].Type, [Weld Log].Date, [Weld Log].[Pass or Fail], [Weld Log].Comment
PIVOT [Weld Log].NDE;

I have attached a copy of the crosstab Query and Report. The report is perfect except for the fact that under the detail portion I need to divide at sum of 20. There will be a separate report for each welder stencil.
Attached Files
File Type: doc Crosstab Query.doc (40.0 KB, 89 views)
File Type: pdf Report.pdf (746.2 KB, 111 views)
Oct 3 '18 #1

✓ answered by PhilOfWalton

Welcome to Bytes

I suggest you leave your query alone. What you need to do is insert a page break after VT gets to 20

Try
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim VTCounter As Long
  5.  
  6. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  7.  
  8.     VTCounter = VTCounter + Nz(VT)
  9.     If VTCounter = 20 Then
  10.         Detail.ForceNewPage = 2   ' after section
  11.         VTCounter = 0
  12.     Else
  13.         Detail.ForceNewPage = 0   ' none
  14.     End If
  15.  
  16. End Sub
So we add up the VTs, when it gets to 20, force a new page and set the VTCounter back to 0.
Note VTCounter is in the header of the module

Phil

19 1535
PhilOfWalton
1,430 Expert 1GB
Welcome to Bytes

I suggest you leave your query alone. What you need to do is insert a page break after VT gets to 20

Try
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim VTCounter As Long
  5.  
  6. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  7.  
  8.     VTCounter = VTCounter + Nz(VT)
  9.     If VTCounter = 20 Then
  10.         Detail.ForceNewPage = 2   ' after section
  11.         VTCounter = 0
  12.     Else
  13.         Detail.ForceNewPage = 0   ' none
  14.     End If
  15.  
  16. End Sub
So we add up the VTs, when it gets to 20, force a new page and set the VTCounter back to 0.
Note VTCounter is in the header of the module

Phil
Oct 3 '18 #2
Do I just add this to the bottom of my SQL? Sorry just learning how Access works.
Oct 3 '18 #3
I need to create a VBA. Just took me a moment to figure it out. Thanks!
Oct 3 '18 #4
PhilOfWalton
1,430 Expert 1GB
No, as I said nothing to do with your SQL

On your report, in design View, click on the bar that says "Detail"
Then find the Event tab on the property sheet.
On the line named On Format, click on the 3 dots ... on the right of the row. That should bring up a box with 3 options. Choose "Code Builder"

This will probably bring up some code - delete it and copy and paste the code I gave you in Post #2

Phil
Oct 3 '18 #5
I followed the steps in your last response but it doesn't seem to be working. I have attached a screen shot of the report in design view and the code display.
Attached Files
File Type: doc Code Screen.doc (223.0 KB, 58 views)
File Type: doc Design view of Report.doc (177.5 KB, 66 views)
Oct 4 '18 #6
twinnyfo
3,653 Expert Mod 2GB
You may have to change line 8 to:

Expand|Select|Wrap|Line Numbers
  1. VTCounter = VTCounter + Nz(Me.VT, 0)
Oct 4 '18 #7
I changed the to the above and I am getting page breaks every where in my report. When I copy the code into the display do I leave the box at the top selected as "detail" after I paste the code?
Oct 4 '18 #8
PhilOfWalton
1,430 Expert 1GB
I think Twinnyfo's suggestion is correct.

There is a lot of unnecessary white space on the right hand side of your report. If the report width is greater than the paper width, you may well get extra pages.
On your "Arrange" Tab, select "Size / Space" and select Ruler on, Grid on & snap to Grid.

Depending on whether you use Metric or Imperial measurements, on a Form or Report's format tab, I set both Grid X and Grid Y to either 5mm or 1/4". That makes controls easy to size and line up.

So first drag the right margin of the report as far to the left as it will go (it will jamb up against the Date & Time controls at the top of the form, which could be narrowed anyway).

Then we need to check the width of your report from the Ruler, and ensure that it is less than the width of the paper - left & right margin.

See if that cures the problem

Phil
Oct 4 '18 #9
I removed the blank space and this is what the report looks like when I print it. I am not seeing where it is breaking on the section that the VT is over 20. Sorry to be such a pain I am pretty new at formatting reports.
Attached Files
File Type: pdf 20181004111000964.pdf (1.54 MB, 96 views)
Oct 4 '18 #10
PhilOfWalton
1,430 Expert 1GB
I'm having difficulty is understanding what is going wrong. I suspect it is a combination of the report putting in it's natural page breaks, and the code forcing additional page breaks.

I am going to suggest an approach which may be much clearer, but I leave you to judge.

There is an icon on your ribbon in the picture that says "Group & Sort". Click on that, and you will see information at the bottom of the screen.

Delete the rows that say Group on Spool #, Group on Weld # & Group on VT. You are not using them.

On the Group on Type, there are various options which you appear to have set correctly, but select the option "Keep whole group together on one page"

Unless the requirement for exactly 20 VT's per page is set in gold, I think this will make a much better report.

Phil
Oct 4 '18 #11
Ok I started from scratch with my report. I thought maybe it was something in the settings I had changed that was causing the problem. However, I ran another one and I am still having problems. I attached the new report so you could take a look.
Attached Files
File Type: pdf 20181004144751425.pdf (891.4 KB, 83 views)
Oct 4 '18 #12
PhilOfWalton
1,430 Expert 1GB
Have you removed the Detail OnFormat Event?

Let's have another look at the report in Design view, showing the Group on bit at the bottom, and the Event tab for the Detail Section.

Phil
Oct 4 '18 #13
I have done some tweaking on the report and have it doing what I want with the exception of the first group on B7. It's not separating the first page correctly. I have attached a copy of the report. I included a item number to make sure the counts were correct per page and they are with the exception of the first 3 pages. I have tried everything I know but cant get the first group to separate correctly.
Oct 8 '18 #14
PhilOfWalton
1,430 Expert 1GB
No report attached.

Please read Post 13

Phil
Oct 8 '18 #15
Reformatted the attachment and it is keeps saying upload failed. I'll keep trying. I have Never had this problem before.
Oct 8 '18 #16
PhilOfWalton
1,430 Expert 1GB
It may be too big. There is a limit on file sizes shown at the bottom of the "Manage Attachments" tab.

Have you tried zipping it?

Phil
Oct 8 '18 #17
I was able to scan the three pages I am having problems with but not the whole report. I tried zipping it and converting it to other file types and it would not let me attach. Its weird because I attached the whole report the other day.
Attached Files
File Type: pdf 20181008104337499.pdf (289.6 KB, 73 views)
Oct 8 '18 #18
PhilOfWalton
1,430 Expert 1GB
Sorry, this is the third and final time I shall ask you to respond to Post 13.
Oct 8 '18 #19
Just sent a reply. Been having problems with attachment feature.
Oct 8 '18 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: steveradaza | last post by:
Sir,Good Day..I am a newbie here and i am just learning the usage of microsoft access..can you help me solve my problem in making a running balance in a query of microsoft access 2013 of my In and...
4
by: ESAKKI109 | last post by:
In Microsoft access 2013 how can I set new record as a default without VBA coding and macro when form opens?
1
by: Jasm1234 | last post by:
Hi, We recently tried to move our Access database from a file to another and somehow corrupted or changed the settings to the file. Originally, multiple users can access and this is without the...
2
by: edkreuz | last post by:
I have moved a database from work to home and it went from 2010 to 2013. Most of my fields are functioning correctly in my forms, but I have one field that is a combo dropdown that will not display...
1
by: MatthewEliason | last post by:
I have a table called "Students" and in this table I have their student ID, which is also the unique identifier number, along with first and last name and grade. I also have an "Enrollment" table...
1
by: tallen | last post by:
ADezii and zmbd, I have one other question. There seems to be some other code which possibly should reflect the new DAO code. the following does not allow forms to open any longer and files were...
5
by: Bryan1 | last post by:
Hello everyone I'm new to Access and have been putting together an Assets Database for the company i work for but i have no idea how to create a reminder form or report. I would like the reminder to...
1
by: Penuel | last post by:
l want to display the map from ArcGIS by click the button in my interface l design from access 2013. Is it possible if it is l need your help plz
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.