• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

Forms/Subforms/Reports

Hello
I have an access database where it has a main form and a sub form within.  The sub form can have many records usually 12.  These 12 have the same trace code (as we call it) example 09B15PF-UF which is on the main form.  Now the difference of each record is the suffix at the end.  So each record on the sub form has an AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL and so on.  I have a report that is linked to these which is a cert of conformance.  How can I make a field on the report which will take the AA (the first suffix) thru AL (the last suffix).  Right now if I put the suffix on the report it only puts AA.

thanks for the help
0
jpb12345
Asked:
jpb12345
  • 6
  • 5
1 Solution
 
als315Commented:
Can you upload sample DB and show expected result?
0
 
PatHartmanCommented:
Here's some code that shows how to concatenate many-side records.  However, your requirement is more complicated.  You have an alpha string that you want to summarize by showing only a starting and ending value.  How do you want to handle gaps?  What if AD is missing?   Should the string be AA-AC, AE-AX?
Concatenate-AllenBrowne.docx
0
 
jpb12345Author Commented:
Pat this is exactly what Im talking about.  I didnt know if there was a way to handle the gaps.  For example, I might have 12 parts AA-AL and AD might be rejected.  Would there be a way to take AA-AC, AE-AL?  that would be exactly what im looking for.  please advise
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jpb12345Author Commented:
I didnt know if there was a control field that I can put in to grab the suffix for the last record on the subform but that still wouldnt answer how to handle gaps if required
0
 
PatHartmanCommented:
Forms don't have anything to do with this process.  You are processing a recordset made from the table.

I'm sure there is a way to handle the gaps but the code I posted won't do it.  You are going to have to write your own custom code.  Create a query that sorts the data into sequence by the unique identifier.  The code will split the common part from the suffix.  It will need to save the common part to a hold area that is used to match subsequent records.  You will need to have an array to capture the suffixes.  When there is a break in the common part, your code must loop through the array and build the grouping string.  It will need to compare each code to the previous 1 to ensure there is no gap.  This is more difficult than you might imagine since you can't subtract AC from AD to determine the difference is 1.  I would probably create  a table to translate the suffixes from numeric values and use the numeric value in the subtract.

Well, that's it in a nutshell, you should be able to finish the code by 2:00 :)

Take a stab at it and we'll help.
0
 
jpb12345Author Commented:
Is there any way to just grab the last suffix on that particular sub form (example AL)?  I figured the database can figure out that AL is after AK but really just to take the suffix from the first record and the last record.  I think if I want to get rid of the gap I can add a field on my form where the user can input to exclude these suffixes.  The cert would then say AA-AL except AD.  (AA being a field, - being a label, AL being a field, except AD being a field).  More input by the user but simpler to create if there was a way to grab the first and last record.  thanks for the help

thanks
0
 
PatHartmanCommented:
Again, this is not something you do with a form.  It is something you do with a recordset.  You could create a query that selects the min and max suffixes but it won't handle gaps.

Select Left(TraceCode, Instr(TraceCode, "-") -1) as TraceCodeGroup, Min(Right(TraceCode, 2)) as MinSuffix, Max(Right(TraceCode, 2)) as MaxSuffix
From yourtable
Where Left(TraceCode, Instr(TraceCode, "-") -1)  = Left(Forms!yourform!TraceCode, Instr(TraceCode, "-") -1)
Group By Left(TraceCode, Instr(TraceCode, "-") -1) ;

Of course, this would be simpler if the suffix was separate.  It is always better to store something like the suffix in a separate field.  You can then concatenate the two fields when you display them.
Select TraceCode, Min(Suffix) As MinSuffix, Max(Suffix) as MaxSuffix
From yourtable
Where TraceCode = Forms!yourform!TraceCode
Group By TraceCode;
0
 
jpb12345Author Commented:
The thing is that the trace code is in the main form and the suffix is a separate field in the sub form so it is already been concatenated.  it wont do a min of the suffix (it gives an error)
0
 
PatHartmanCommented:
concatenated means stuck together.  It sounds like they are separate.  The group part in the main form and the suffix in the subform.

Please show us the query you wrote and the exact error message.

The query I suggested needs to get the common part of the TraceCode from the main form since you want it without the suffix.  If necessary, it can reference the subform but that will only work if the subform actually has records.

It would also help to see what the data looks like in the parent table and in the child table.
0
 
jpb12345Author Commented:
Yeah I know how to concatenate.  They are separated right now.  I open up the cert by a command button which has this code
Private Sub Command28_Click()
DoCmd.OpenReport "New COC", acViewPreview, , "[furnace run#]=Forms![Final Input new]![furnace run#]"
End Sub

My tables look like the excel file I attached
New-RVC-dimensions.xlsx
0
 
PatHartmanCommented:
OK.  Did you try the query I suggested?  Please post it.
0
 
jpb12345Author Commented:
Sorry for the delay I have been busy

This is what my SQL looks like before adding your query

SELECT [New TBLRVC].[PO#], [New TBLRVC].SO, [New TBLRVC].[Trace Code], [New TBLRVC].[Part Name], [New TBLRVC].[Inspection Qty], [New TBLRVC].[Furnace Run#], [New RVC many to many Table].Suffix, [New TBLRVC].[Shipping Date], [New TBLRVC].[Packing Slip#], [New TBLRVC].[Lot Qty]
FROM [New TBLRVC] INNER JOIN ([New RVC many to many Table] INNER JOIN [New RVC dimensions] ON [New RVC many to many Table].[Suffix] = [New RVC dimensions].[Suffix (ID)]) ON [New TBLRVC].[Furnace Run#] = [New RVC dimensions].[Furnace Run#];
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now