My query is linking two NLs from different tables. I used Jet about 8 years ago and am very rusty. I want to include a Location Code in my report but it is not available from the Customer table (it comes from the Value Entries table). I am using a Rows NL and Sum NL on the first line of the report. I need to ascertain which Location Code belongs to which Customer (these are filtered in the Rows NL). I have tried putting a Rows NL on the second line but can’t remember how to link the two Rows NLs so that the Location Code (from the Rows NL on the second line) relates to the Customer (in the Rows NL) on the first line. I have tried the Link function, but it didn't work because Jet/Excel crashed twice – I think it was returning too many records. I’d be grateful for any help.
1 comment
-
Jet Reports Historic Posts Let me see if I understand the question -
You are using an NL Rows command to get a customer number on the first line, so if you were using only that code, your report would look something like this:
Customer A
Customer B
Customer C
Now you want to find the locations that pertain to each of these customers, and I'm assuming there are multiple locations for each customer, which is why a second Rows command, so your output woudl be like this:
Customer A
Loc 1A
Loc 2A
Customer B
Loc 2A
Loc 3A
and so forth…
so to do this, what you need is for your first Rows command to be "Rows=2" (to encompass the row below)
and your second Rows command is just "Rows"
I find it easiest if your filter for the second Rows command is actually finding a value on the same row, so I just repeat the Customer from the previous row like this:
C3 = first Rows Commany
C4 = C3
D4 = second rows command, with using C3 as filter
please let us know if this helps, or if I've gone in the wrong direction :)