Pull Next Record from Table

[MegSteele]

Hi there,

I am very new to Access and do not really use VBA code at all. I am making a query in Query Design with several tables joined together. (ICPartSubAssembly Table - contains components of parts & ICPart Table - contains other info regarding the parts like price, cost, description, etc.) The main table I am concerned with (ICPartSubAssembly) has the following fields:

SKICPartSubAssembly
NextRecord
FKICPart (field that links to the ICPart table)
FKPartSeqNum
Type
NumberofComponents
Comp1_FKICPart (these fields 1-10 also link to ICPart table)
Comp1_FKPartSeqNum
Comp1_Qty
Comp2_FKICPart
Comp2_FKPartSeqNum
Comp2_Qty
Comp3_FKICPart
Comp3_FKPartSeqNum
Comp3_Qty
Comp4_FKICPart
Comp4_FKPartSeqNum
Comp4_Qty
Comp5_FKICPart
Comp5_FKPartSeqNum
Comp5_Qty
Comp6_FKICPart
Comp6_FKPartSeqNum
Comp6_Qty
Comp7_FKICPart
Comp7_FKPartSeqNum
Comp7_Qty
Comp8_FKICPart
Comp8_FKPartSeqNum
Comp8_Qty
Comp9_FKICPart
Comp9_FKPartSeqNum
Comp9_Qty
Comp10_FKICPart
Comp10_FKPartSeqNum


Comp10_Qty

I made a query that links the CompX_FKICPart with the proper field in the ICPart table to show the name of each component for a particular part. My problem is that many of our parts have more than 10 components. Is there a way that I can use "NextRecord" to properly create my query to pull ALL of the components for each part?

Thank you!

[ssanfu]

You need to normalize your table structure. The fields "CompX_FKICPart", "Comp1_Qty" & "Comp2_FKICPart" should be in their own table.

Something like this:

table "ICPartSubAssembly"
--------------------------
ICPartSubAssembly_PK (autonumber) (link to Subcomponents table)
SKICPartSubAssembly
NextRecord
FKICPart (field that links to the ICPart table)
FKPartSeqNum
Type <<= this is a reserved word in access - shouldn't be used as object names
NumberofComponents


Table "SubComponets"
-----------------------
Subcomponents_PK (autonumber)
ICPartSubAssembly_FK (field that links to the ICPartSubAssembly table)
FKICPart (link to ICPart table)
FKPartSeqNum
Qty

Could use a main form/sub form to enter sub assembly components.

[MegSteele]

Okay, thank you. These tables come from our accounting software. I cannot change them. I pulled them into Access to be able to pull different reports than what the software allows. I attached our tables and fields guide that I use to determine how the tables are linked to each other. Is this helpful at all?Sage BW Tables & Fields.pdf

[ssanfu]

OK, that helped a little.

Is there a way that I can use "NextRecord" to properly create my query to pull ALL of the components for each part?

No, that could be a field to sort on. It looks like you might be able to use the "ICPartSubAssembly" field "FKICPart" to find all components for a part.
You may be able to generate the reports you want.... without some examples it is hard to say. I am starting to understand their table structure, but it is taking a lot of time.

These tables come from our accounting software. I cannot change them. I pulled them into Access to be able to pull different reports than what the software allows.

How often do the parts change (added/edited/deleted)?

Using queries and VBA, the data could be normalized; it depends on how much effort you want to invest to have the new reports. And, depending on what the new reports you want look like, you might be able to use the existing table structure..... it all depends on you.

[MegSteele]

Hahaha! I understand how frustrating reading that can be! Thank you for your explanation. The parts are not changed that often; however, new parts are added occasionally. I just wanted to create a report that I could type in the Part # that I wanted to look up and pull all of the components that are associated with that part into a simple list (basically a work order sheet that the line can use to determine what parts they need). I want to do this in Access because I want to customize the report to include other fields such as date, time, a unique work order #, etc. I can pull the software's subassembly list that is already created but I cannot do anything with it except print it.

From the query I already created, if there are more than 10 components the results for one part are pulled into two or three lines (rows) to list all of the additional components (components 1-10 are the columns). This is what the SQL statement looks like from what I have done in Query Design:

SELECT ICPart.ID, ICPart_1.ID AS Component1, ICPartSubAssembly.Comp1_Qty, ICPart_2.ID AS Component2, ICPartSubAssembly.Comp2_Qty, ICPart_3.ID AS Component3, ICPartSubAssembly.Comp3_Qty, ICPart_4.ID AS Component4, ICPartSubAssembly.Comp4_Qty, ICPart_5.ID AS Component5, ICPartSubAssembly.Comp5_Qty, ICPart_6.ID AS Component6, ICPartSubAssembly.Comp6_Qty, ICPart_7.ID AS Component7, ICPartSubAssembly.Comp7_Qty, ICPart_8.ID AS Component8, ICPartSubAssembly.Comp8_Qty, ICPart_9.ID AS Component9, ICPartSubAssembly.Comp9_Qty, ICPart_10.ID AS Component10, ICPartSubAssembly.Comp10_Qty, ICPartSubAssembly.NextRecord
FROM ((((((((((((ICPart INNER JOIN ICPartSubAssembly ON ICPart.SKICPart = ICPartSubAssembly.FKICPart) INNER JOIN ICPartVendor ON ICPart.SKICPart = ICPartVendor.FKICPart) INNER JOIN APVendor ON ICPartVendor.Vend1_FKAPVendor = APVendor.SKAPVendor) INNER JOIN ICPart AS ICPart_1 ON ICPartSubAssembly.Comp1_FKICPart = ICPart_1.SKICPart) INNER JOIN ICPart AS ICPart_2 ON ICPartSubAssembly.Comp2_FKICPart = ICPart_2.SKICPart) INNER JOIN ICPart AS ICPart_3 ON ICPartSubAssembly.Comp3_FKICPart = ICPart_3.SKICPart) INNER JOIN ICPart AS ICPart_4 ON ICPartSubAssembly.Comp4_FKICPart = ICPart_4.SKICPart) INNER JOIN ICPart AS ICPart_5 ON ICPartSubAssembly.Comp5_FKICPart = ICPart_5.SKICPart) INNER JOIN ICPart AS ICPart_6 ON ICPartSubAssembly.Comp6_FKICPart = ICPart_6.SKICPart) INNER JOIN ICPart AS ICPart_7 ON ICPartSubAssembly.Comp7_FKICPart = ICPart_7.SKICPart) INNER JOIN ICPart AS ICPart_8 ON ICPartSubAssembly.Comp8_FKICPart = ICPart_8.SKICPart) INNER JOIN ICPart AS ICPart_9 ON ICPartSubAssembly.Comp9_FKICPart = ICPart_9.SKICPart) INNER JOIN ICPart AS ICPart_10 ON ICPartSubAssembly.Comp10_FKICPart = ICPart_10.SKICPart;

I certainly don't want you to spend alot of time trying to figure this out so if there is no simple solution than I will just need to think about a different approach. I really appreciate your help!

[ssanfu]

The query looks like it should work.

One thing you could do is once the part data is found, use VBA to dump the (query) data to a normalized table (maybe named "ReportData"). The report (and query) would be based on the "ReportData" table. Then you could edit the data to your heart's content (without changing the data in the other tables), then view/print the report.

[MegSteele]

Ok, I will try that.

Thank you!