Report Help: Reading Records From Same Field

[chaddiesel]

Hello Everyone,

Let me explain my problem.

I have included 2 dashes between each pair of records to make it easier to see what goes
together. In reality, it is just a long list of results from my query.


Contract--------QTY--------COMPONENT--------LENGTH-------PCS
11111111 2 PartAA101 38 3
11111111 38 CABLE
--
11111111 8 PartAA102 42 8
11111111 42 CABLE
--
22222222 1 PartAA103 66 2
22222222 67 CABLE
--
22222222 2 PartBB101
22222222 59 CABLE
--
22222222 2 PartAA109 52 3
22222222 61 CABLE


I need to make a report that looks something like this:

Contract------Component------Length------PCS
11111111 PartAA101 38 3
11111111 PartAA102 42 8
22222222 PartAA103 66 2
22222222 PartBB101 59 2

Pretty simple, but here is the hard part. Look at the fourth set of records:

Contract--------QTY--------COMPONENT--------LENGTH-------PCS
22222222 2 PartBB101
22222222 59 CABLE

Notice that there is no LENGTH or PCS. This is the case with all parts that start with "PartBB". In this case, I need to use the 59 as the LENGTH and the 2 as the PCS. How do I tell Access to look at a field in the next line for the information? The COMPONENT record is always followed another record called CABLE. The QTY values are from previous orders. They may be similar to the LENGTH and PCS, but they are not the same.

This is oversimplifying the problem, but I need Access to do the following:

If COMPONENT begins with "PartBB" then use the QTY value in the same line as PCS and the QTY value in the next line that says CABLE as the LENGTH--Otherwise, if the part does not start with "PartBB" use the LENGTH and PCS that are given.

My problem is transfering this into code. The reason I am doing things this way is one of our customers sends us a spreadsheet each week that has the information give to us this way. That is the way they are going to continue to do things, and I am trying to automate things by importing the spreadsheet, querying the appropriate data, and printing out a report of what is needed. This will save a lot of time. I have little experience writing code, so examples would be helpful. Again, these are just a list of records. Is there a way to tell Access to look at another line? Thanks for reading my problem, and any help would be appreciated.

Thank You,

Chad

 

[Regor]

I am not certain I completely understand where these records come from (same fields and tables???), but can't you concatenate the two records into a calculated field within the query itself instead? You could insert whatever characters in-between the two to facilitate parsing afterward in the report.