Report Help: Reading Records From Same Field

[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 the value for 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 in the same line.

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 (changing the sheet, unfortunately, is
not an option), 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

[Salad]

sh***********************@yahoo.com wrote:

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 the value for 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 in the same line.

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 (changing the sheet, unfortunately, is
not an option), 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

Well, you can use Dlookup (the query will be a bit slower because it
needs to calculate the stuff).

Pieces:IIF(Not IsNull([Pcs]),[Pcs],[Qty])
LengthOf:IIF(Not IsNull([Length]),[Length],_
Dlookup("Qty","TableName","Contract = " & [Contract] & _
"And Component = 'Cable'"))

You'd also want to exclude displaying records that have a component
named "Cable"

If you are doing this in a report, you can create a field that is a
calculated field. Ex: Have a field called Pieces. In the control
source you could use the IIF statements above...or you could create a
function and enter
=GetPieces([Contract])
and write a funtion to look up/return the result.

[shaqattack1992-newsgroups]

Hello,

Thanks for the help. You've helped me out of a jam before.

To simplify my last post, I changed some of the variable names. Here
is what I changed

QTY=QTY_EXTENDED
Name of table=Cable_Table
CONTRACT=C_CONTRACT
COMPONENT remains unchanged
"CABLE" is really a part # "BBB1173"

I don't know if this makes a difference, but I wanted to give you all
the information:

C_CONTRACT-QTY_EXTENDED-----COM*PONENT--------LENGTH-------PCS
11111111 2 PartAA101 38 3
11111111 38 BBB1173
11111111 8 PartAA102 42 8
11111111 42 BBB1173
22222222 1 PartAA103 66 2
22222222 67 BBB1173
22222222 2 PartBB101
22222222 59 BBB1173

The PCS IIF statement works great, but I get #Error on the LENGTH.
Here is the IIF statement I tried (with _ inserted for line breaks
purposes-in reality, it is one long line):

=IIF(Not IsNull([LENGTH]),[LENGTH], _
Dlookup("QTY_EXTENDED","Cable_Table","C_CONTRACT = " & [C_CONTRACT] & _
"And COMPONENT = 'BBB1173'"))

I keep getting an error message and am wondering what I have done
wrong. This information is now in a table instead of a query--I tried
both ways to see if this would make a difference. Also, just for my
information, how does the DLookup know what the corresponding
CABLE/BBB1173 record is? Does it look for the record immediatley
following? The only thing that kid of ties them together is the
C_CONTRACT, but it might be used for more than one Component. I'm new
at this, so I am unsure how this works. Any additional help would be
appreciated.

Thank You,

Chad

[jimfortune]

sh***********************@yahoo.com wrote:

=IIF(Not IsNull([LENGTH]),[LENGTH], _
Dlookup("QTY_EXTENDED","Cable_Table","C_CONTRACT = " & [C_CONTRACT] & _
"And COMPONENT = 'BBB1173'")) Chad

To save Salad some time, you should try putting a space before 'And.'

" And COMPONENT..."

James A. Fortune

[shaqattack1992-newsgroups]

I just tried the space, and I still received the #Error where the
Length text box is.

Chad

[jimfortune]

sh***********************@yahoo.com wrote:

I just tried the space, and I still received the #Error where the
Length text box is.

Chad

If C_CONTRACT is a text field you'll also need "C_CONTRACT = " &
Chr(34) & [C_CONTRACT] & Chr(34) & _

or "C_CONTRACT = '" & [C_CONTRACT] & "' And COMPONENT...

James A. Fortune

[Salad]

ji********@compumarc.com wrote:

sh***********************@yahoo.com wrote:

I just tried the space, and I still received the #Error where the
Length text box is.

Chad

If C_CONTRACT is a text field you'll also need "C_CONTRACT = " &
Chr(34) & [C_CONTRACT] & Chr(34) & _

or "C_CONTRACT = '" & [C_CONTRACT] & "' And COMPONENT...

James A. Fortune

Looking at his initial post, C_Contract looks to be a string and not a
numeric field. Your catch on that should correct the problem.

[shaqattack1992-newsgroups]

I changed the data type and that solved my #Error problem. However, I
am getting the wrong data. The data table looks like this:

C_CONTRACT QTY_EXTENDED COMPONENT LENGTH PCS
111111 2 PartAA101 106 2
111111 105.8333 BBB1173
111111 1 PartBB101
111111 59 BBB1173
222222 2 PartAA102 106 2
222222 105.8333 BBB1173
222222 1 PartBB110
222222 59 BBB1173

The report should look like this

C_CONTRACT COMPONENT LENGTH PCS
111111 PartAA101 106 2
111111 PartBB101 59 1
222222 PartAA102 106 2
222222 PartBB110 59 1

What I get is this:

C_CONTRACT COMPONENT LENGTH PCS
111111 PartAA101 106 2
111111 BBB1173 105.8333 105.8333
111111 PartBB101 105.8333 1
111111 BBB1173 105.8333 59
222222 PartAA102 106 2
222222 BBB1173 105.8333 105.8333
222222 PartBB110 105.8333 1
222222 BBB1173 105.8333 59

I imagine I can filter or hide the BBB1173 row, but I wanted to show
you all the results.

I'm using these 2 IIF statements Salad gave me in 2 text boxes on my
report:

Pieces:IIF(Not IsNull([PCS]),[PCS],[QTY_EXTENDED])
LengthOf:IIF(Not IsNull([LENGTH]),[LENGTH],_
Dlookup("QTY_EXTENDED","Cable_Table","C_CONTRACT = " & [C_CONTRACT] &
"And COMPONENT = 'BBB1173'"))

Thanks for all the help guys. I hate to keep bothering you guys.

Chad

[jimfortune]

sh***********************@yahoo.com wrote:

I changed the data type and that solved my #Error problem. However, I
am getting the wrong data. The data table looks like this:

C_CONTRACT QTY_EXTENDED COMPONENT LENGTH PCS
111111 2 PartAA101 106 2
111111 105.8333 BBB1173
111111 1 PartBB101
111111 59 BBB1173
222222 2 PartAA102 106 2
222222 105.8333 BBB1173
222222 1 PartBB110
222222 59 BBB1173

The report should look like this

C_CONTRACT COMPONENT LENGTH PCS
111111 PartAA101 106 2
111111 PartBB101 59 1
222222 PartAA102 106 2
222222 PartBB110 59 1

What I get is this:

C_CONTRACT COMPONENT LENGTH PCS
111111 PartAA101 106 2
111111 BBB1173 105.8333 105.8333
111111 PartBB101 105.8333 1
111111 BBB1173 105.8333 59
222222 PartAA102 106 2
222222 BBB1173 105.8333 105.8333
222222 PartBB110 105.8333 1
222222 BBB1173 105.8333 59

I imagine I can filter or hide the BBB1173 row, but I wanted to show
you all the results.

I'm using these 2 IIF statements Salad gave me in 2 text boxes on my
report:

Pieces:IIF(Not IsNull([PCS]),[PCS],[QTY_EXTENDED])
LengthOf:IIF(Not IsNull([LENGTH]),[LENGTH],_
Dlookup("QTY_EXTENDED","Cable_Table","C_CONTRACT = " & [C_CONTRACT] &
"And COMPONENT = 'BBB1173'"))

Thanks for all the help guys. I hate to keep bothering you guys.

Chad

Salad's idea of using DLookup is sound since it's equivalent to using a
subquery. Try the following subquery to see if it gets you what you
are looking for:

SELECT TABLE_CABLE.C_CONTRACT, TABLE_CABLE.COMPONENT, IIf(Not
Left([COMPONENT],6)='PartBB',[LENGTH],(SELECT QTY_EXTENDED FROM
TABLE_CABLE AS A WHERE A.ID = TABLE_CABLE.ID + 1)) AS theLength,
IIf(Not Left([COMPONENT],6)='PartBB',[PCS],[QTY_EXTENDED]) AS thePCS
FROM TABLE_CABLE WHERE (((Left([Component],4))='Part')) GROUP BY
TABLE_CABLE.C_CONTRACT, TABLE_CABLE.COMPONENT, TABLE_CABLE.ID,
TABLE_CABLE.LENGTH, TABLE_CABLE.PCS, TABLE_CABLE.QTY_EXTENDED;

I use the field names corresponding to where you say, "The data table
looks like this:"

I got the numbers you wanted but I'm not sure if it handles all the
cases. Also, this query relies on the existence of an AutoNumber field
called ID in TABLE_CABLE. If possible, change your database design so
that it doesn't rely anywhere on the order of the records in a table.

James A. Fortune

[Salad]

ji********@compumarc.com wrote:

sh***********************@yahoo.com wrote:

I changed the data type and that solved my #Error problem. However, I
am getting the wrong data. The data table looks like this:

C_CONTRACT QTY_EXTENDED COMPONENT LENGTH PCS
111111 2 PartAA101 106 2
111111 105.8333 BBB1173
111111 1 PartBB101
111111 59 BBB1173
222222 2 PartAA102 106 2
222222 105.8333 BBB1173
222222 1 PartBB110
222222 59 BBB1173

The report should look like this

C_CONTRACT COMPONENT LENGTH PCS
111111 PartAA101 106 2
111111 PartBB101 59 1
222222 PartAA102 106 2
222222 PartBB110 59 1

What I get is this:

C_CONTRACT COMPONENT LENGTH PCS
111111 PartAA101 106 2
111111 BBB1173 105.8333 105.8333
111111 PartBB101 105.8333 1
111111 BBB1173 105.8333 59
222222 PartAA102 106 2
222222 BBB1173 105.8333 105.8333
222222 PartBB110 105.8333 1
222222 BBB1173 105.8333 59

I imagine I can filter or hide the BBB1173 row, but I wanted to show
you all the results.

I'm using these 2 IIF statements Salad gave me in 2 text boxes on my
report:

Pieces:IIF(Not IsNull([PCS]),[PCS],[QTY_EXTENDED])
LengthOf:IIF(Not IsNull([LENGTH]),[LENGTH],_
Dlookup("QTY_EXTENDED","Cable_Table","C_CONTRA CT = " & [C_CONTRACT] &
"And COMPONENT = 'BBB1173'"))

Thanks for all the help guys. I hate to keep bothering you guys.

Chad

Salad's idea of using DLookup is sound since it's equivalent to using a
subquery. Try the following subquery to see if it gets you what you
are looking for:

SELECT TABLE_CABLE.C_CONTRACT, TABLE_CABLE.COMPONENT, IIf(Not
Left([COMPONENT],6)='PartBB',[LENGTH],(SELECT QTY_EXTENDED FROM
TABLE_CABLE AS A WHERE A.ID = TABLE_CABLE.ID + 1)) AS theLength,
IIf(Not Left([COMPONENT],6)='PartBB',[PCS],[QTY_EXTENDED]) AS thePCS
FROM TABLE_CABLE WHERE (((Left([Component],4))='Part')) GROUP BY
TABLE_CABLE.C_CONTRACT, TABLE_CABLE.COMPONENT, TABLE_CABLE.ID,
TABLE_CABLE.LENGTH, TABLE_CABLE.PCS, TABLE_CABLE.QTY_EXTENDED;

I use the field names corresponding to where you say, "The data table
looks like this:"

I got the numbers you wanted but I'm not sure if it handles all the
cases. Also, this query relies on the existence of an AutoNumber field
called ID in TABLE_CABLE. If possible, change your database design so
that it doesn't rely anywhere on the order of the records in a table.

James A. Fortune

Another thing to check on is copying the Dlookups to the Debug window
and running them. Ex:
? Dlookup(.....)
and seeing what the results are for specific instances. Perhaps there
are more than 1 record with the same contract/componenct with multiple
BBB1173s. I think that by looking at the records that product the
incorrect results and by "debugging" by going into debug and creating
temp queries to view the table sorted different ways to ensure there is
a 1-1 correspondence and not a 1-many correspondence somewhere the error
will be revealed.

[shaqattack1992-newsgroups]

I pasted the code into a new query and got the following error:

Syntax error (missing operator) in query expression
'(((Left([Component],4))='Part'-))'.

My table is as I described with all fields numbers except for
COMPONENT. I also inserted an auto-number field called ID into the
TABLE_CABLE table.

I agree depending on order is not the best way to do things, but I'm
stuck with these kind of problems with one of our customers--they send
us certain spreadsheets and we have to try to find a way to streamline
things. I really appreciate both of you helping me. Sorry to be so
much trouble, but when I run into something like this, I get stumped.
I have a lot to learn about access. Thank You.

Chad

[shaqattack1992-newsgroups]

Actually, before that, I'm going to see if I can get ahold of some of
the original spreadsheets from a different department to see if I can
find another way to link the data.

Chad

[shaqattack1992-newsgroups]

Actually, before that, I'm going to see if I can get ahold of some of
the original spreadsheets from a different department to see if I can
find another way to link the data.

Chad

[jimfortune]

sh***********************@yahoo.com wrote:

I pasted the code into a new query and got the following error:

Syntax error (missing operator) in query expression
'(((Left([Component],4))='Part'-))'.

My table is as I described with all fields numbers except for
COMPONENT. I also inserted an auto-number field called ID into the
TABLE_CABLE table.

I agree depending on order is not the best way to do things, but I'm
stuck with these kind of problems with one of our customers--they send
us certain spreadsheets and we have to try to find a way to streamline
things. I really appreciate both of you helping me. Sorry to be so
much trouble, but when I run into something like this, I get stumped.
I have a lot to learn about access. Thank You.

Chad

I think the - is not part of what I originally typed. Sometimes Google
adds an extra hyphen when it shouldn't.

James A. Fortune

Note: I'm leaving right now for Florida for the week. I'll try to
check this NG from there if possible.

[shaqattack1992-newsgroups]

I wanted to see if there was any data not getting to me, so I checked
with another department to see the original spreadsheet. I think I
have found another field that might provide a relationship between the
records. I have provided this example table.

KEY C_CONTRACT COMPONENT LENGTH PCS
003001 111111 PartAA101 106 2
003001002 111111 BBB1173 105.8333 105.8333
003002 111111 PartBB101 105.8333 1
003002002 111111 BBB1173 105.8333 59
005001 222222 PartAA102 106 2
005001002 222222 BBB1173 105.8333 105.8333
005002 222222 PartBB110 105.8333 1
005002002 222222 BBB1173 105.8333 59

I left out the QTY_EXTENDED column due to space constraints. As you can
see, there is a KEY for each line. For example, the key for the first
bart is 003001, and the corresponding key is 003001002---those 2 go
together. It looks like the KEYs do repeat only when the contract
number changes, so the next Part**** on the contract will be 003002
followed by 003002002. The first number is always followed by the
first number followed by 002. When the contract number changes, the
next KEY might be005001, 007001...and so on or it might be 003001
again. The second numbers would be 003001002, 005001002, 007001002.

So I guess now it would be:

If the Part number starts with PartBB and has a KEY of 007001 for
example, look at the QTY_EXTENDED of the 007001 for the PCS and the
QTY_EXTENDED of the 007001002 line for the LENGTH on the same
C_CONTRACT number----Otherwise, use the LENGTH and PCS of the 007001
line of that contract.

I'm going to do further research as to why the numbers aren't on the
same line, and see if we can get the numbers always on the same line.
I swear I'm not messing with you guys. After I get this problem
solved, I'll put this topic to bed. Also, is there any resources you
would recommend to help me understand the SQL code better? I've kind
of been thrown into these Access databases and don't have any formal
training. Are there any books I could look at for beginners that would
ease me into it? I don't want to have to keep bugging you guys to
death. I can usually find an answer to a question on Google Groups,
but when I have a situation where I can't find a similar example, I get
stumped. Any information would be appreciated, and thanks again for
all your help.

Thanks,

Chad

[shaqattack1992-newsgroups]

Hello Again,

After I found the KEY field, this is what I tried:

I ran 2 queries on my imported spreadsheet--One for the Part**** line
and the other for the BBB1173 line. I then ran a third query that had
the other 2 queries joined by C_CONTRACT and another field which is the
first 6 numbers of the KEY (joined inside the query only). That put
everything on the same line. I then based a report on this query and
used IIf statements to tell it what fields to use as a source based on
if the part did or did not start with PartBB. That is probably not a
very eloquent way of doing it, but it seems to be giving me the right
results after running several spreadsheets through it.

Chad