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
jpb12345Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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

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
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
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.