S
shaqattack1992-newsgroups
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
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