Showing the recods with Lower Price if "almost" duplicate found

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a query that makes a table here is the sql view of it:

SELECT tbl_AcctPricing.BLACCT, tbl_AcctPricing.BNAME,
tbl_AcctPricing.BLRECT, tbl_AcctPricing.[BLREC#], tbl_AcctPricing.BLPLIST,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.Item,tbl_NewPriceList.Item) AS Item1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.INAME,tbl_NewPriceList.INAME) AS INAME1,
tbl_NewPriceList.ICLAS1, tbl_NewPriceList.IPRCCD, IIf(tbl_AcctPricing.BLRECT
Like "I",qry_ItemPRiceList.LNAME,tbl_NewPriceList.LNAME) AS LNAME1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$LIST#],tbl_NewPriceList.[$LIST#]) AS [$LIST#1],
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$P1],tbl_NewPriceList.[$P1]) AS [$P11] INTO tbl_test
FROM qry_ItemPRiceList RIGHT JOIN (tbl_NewPriceList RIGHT JOIN
tbl_AcctPricing ON (tbl_NewPriceList.[$LIST#] = tbl_AcctPricing.BLPLIST) AND
(tbl_NewPriceList.ICLAS1 = tbl_AcctPricing.[BLREC#])) ON
(qry_ItemPRiceList.[$LIST#] = tbl_AcctPricing.BLPLIST) AND
(qry_ItemPRiceList.Item = tbl_AcctPricing.[BLREC#]);

Now this newly created table may have item1 that can have a duplicate but
with the different BLRECT and $P1, what I need to make is show only one of
those records that has a lower value in $P1.

Let me know if I need to explain the process in more details. Thanks in
advance for the help.
 
Dear Kontra:

Below, I have reproduced your query with some changes that make it easier
for me to read, hopefully without any functional changes. I have added
aliases and reformatted:

SELECT AP.BLACCT, AP.BNAME, AP.BLRECT, AP.[BLREC#],
AP.BLPLIST, IIf(AP.BLRECT Like "I", IP.Item, NP.Item) AS Item1,
IIf(AP.BLRECT Like "I", IP.INAME, NP.INAME) AS INAME1,
NP.ICLAS1, NP.IPRCCD,
IIf(AP.BLRECT Like "I", IP.LNAME, NP.LNAME) AS LNAME1,
IIf(AP.BLRECT Like "I", IP.[$LIST#], NP.[$LIST#]) AS [$LIST#1],
IIf(AP.BLRECT Like "I", IP.[$P1], NP.[$P1]) AS [$P11]
INTO tbl_test
FROM qry_ItemPRiceList IP
RIGHT JOIN (tbl_NewPriceList NP
RIGHT JOIN tbl_AcctPricing AP
ON NP.[$LIST#] = AP.BLPLIST AND NP.ICLAS1 = AP.[BLREC#])
ON IP.[$LIST#] = AP.BLPLIST AND IP.Item = AP.[BLREC#])

A bit of comment on what we have so far.

The use of Like() without any wildcard is surprising. You could just have
well used "=" here, if that's your intention. No big thing, but using each
feature in its simplest form usually makes things easier to read, and
therefore, easier to maintain in the future.

Given the way the tables are joined, the central table of this query is
AcctPricing. Is it the case that you might have a row in AcctPricing
without any JOINed row in NewPriceList, and that you still want that
represented in the query results as a row with all the values from the other
two tables being NULLs? (What I may be driving at here is NOT to use RIGHT
JOINs except when explicitly necessary. Use INNER JOINs when they will
suffice. Again, the target is to minimize the complexity in reading the
query.)

To show only one row from one of the other two tables (NewPriceList or
ItemPRiceList), and thereby eliminate multiple rows you do not want to
generate, you must select some unique column or set of columns from the
table with multiple rows and filter the query with that using a correlated
subquery. I believe you could add (to the version I show above):

WHERE IP.$P1 = (SELECT MIN($P1) FROM tblItemPRiceList IP1
WHERE IP1.[$LIST#] = AP.BLPLIST AND IP1.Item = AP.[BLREC#])

If, however, you have multiple rows in ItemPRiceList with the same values
for $LIST#, Item, and $P1 then there could occur multiple rows matching the
above criteria. That is, if you have 2 rows in ItemPRiceList with the value
$LIST# = "A" and Item = 1234 and both have $P1 = 3, and if 3 is the smallest
value of $P1 in all rows with $LIST# = "A"and Item = 1234, then you will
still have two rows being contributed from ItemPRiceList. This will be
exactly because you did not specify a necessarily unique way of selecting a
single row from ItemPriceList. Obviously, if there are multiple rows in
ItemPRiceList with the same minimum value in $P1 (and matching the other two
joined values) then each one could contribute different values of INAME and
LNAME in your query. All Access can do is report all of them.

It could be that you were referencing duplicates caused though the
AcctPricing table rather than or in addition to the ItemPRiceList table.
Your original question did not specify from which, or both JOINed tables
this problem could be contributed. You could still try the above code and
let me know if it helped, and we can add more or alter it as needed.

Note: I dropped the ";" at the end of your code, making it possible to add
the code I generated. This new code also uses the aliases I added, so it
won't work against your posted code without modifications, and moving the
position of the ";".

Tom Ellison


kontra said:
Hi,

I have a query that makes a table here is the sql view of it:

SELECT tbl_AcctPricing.BLACCT, tbl_AcctPricing.BNAME,
tbl_AcctPricing.BLRECT, tbl_AcctPricing.[BLREC#], tbl_AcctPricing.BLPLIST,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.Item,tbl_NewPriceList.Item) AS Item1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.INAME,tbl_NewPriceList.INAME) AS INAME1,
tbl_NewPriceList.ICLAS1, tbl_NewPriceList.IPRCCD,
IIf(tbl_AcctPricing.BLRECT
Like "I",qry_ItemPRiceList.LNAME,tbl_NewPriceList.LNAME) AS LNAME1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$LIST#],tbl_NewPriceList.[$LIST#]) AS [$LIST#1],
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$P1],tbl_NewPriceList.[$P1]) AS [$P11] INTO
tbl_test
FROM qry_ItemPRiceList RIGHT JOIN (tbl_NewPriceList RIGHT JOIN
tbl_AcctPricing ON (tbl_NewPriceList.[$LIST#] = tbl_AcctPricing.BLPLIST)
AND
(tbl_NewPriceList.ICLAS1 = tbl_AcctPricing.[BLREC#])) ON
(qry_ItemPRiceList.[$LIST#] = tbl_AcctPricing.BLPLIST) AND
(qry_ItemPRiceList.Item = tbl_AcctPricing.[BLREC#]);

Now this newly created table may have item1 that can have a duplicate but
with the different BLRECT and $P1, what I need to make is show only one of
those records that has a lower value in $P1.

Let me know if I need to explain the process in more details. Thanks in
advance for the help.
Click to expand...
 
First of all let me thank you for taking time to explain so much. Now let me
first answer your questions and repond to the comments:

1. as far as to 'like' I was under impression that you use that with the
string fields and '=' is for number fields. That is why I put 'like' with out
the wildcard becuase that is a string field containing one of 2 values C for
Class and I for Item
2. AP table is the main table that I am suppose to base the query on, since
Account would be the primary in the outcome of this transaction. If the
record is in the AP table then it will defenetly have a corresponding record
in the NP and/or IP tables. NP talbe contains One Class type and its Items
with tiered prices and IP table contains all items with their classes and
tiered pricing. Ofcourse the eaisest way would be to Combine the 2 item
tables, but the trick is that in AP table Class and Item are values in
Promotion Field, and I don't seem to be able to have the query to connect by
2 fields at one time. I hope I am making sence.

I will go ahead and try you suggestion. Thank you again.
--
If at first you don''''t succeed, destroy all evidence that you tried.


Tom Ellison said:
Dear Kontra:

Below, I have reproduced your query with some changes that make it easier
for me to read, hopefully without any functional changes. I have added
aliases and reformatted:

SELECT AP.BLACCT, AP.BNAME, AP.BLRECT, AP.[BLREC#],
AP.BLPLIST, IIf(AP.BLRECT Like "I", IP.Item, NP.Item) AS Item1,
IIf(AP.BLRECT Like "I", IP.INAME, NP.INAME) AS INAME1,
NP.ICLAS1, NP.IPRCCD,
IIf(AP.BLRECT Like "I", IP.LNAME, NP.LNAME) AS LNAME1,
IIf(AP.BLRECT Like "I", IP.[$LIST#], NP.[$LIST#]) AS [$LIST#1],
IIf(AP.BLRECT Like "I", IP.[$P1], NP.[$P1]) AS [$P11]
INTO tbl_test
FROM qry_ItemPRiceList IP
RIGHT JOIN (tbl_NewPriceList NP
RIGHT JOIN tbl_AcctPricing AP
ON NP.[$LIST#] = AP.BLPLIST AND NP.ICLAS1 = AP.[BLREC#])
ON IP.[$LIST#] = AP.BLPLIST AND IP.Item = AP.[BLREC#])

A bit of comment on what we have so far.

The use of Like() without any wildcard is surprising. You could just have
well used "=" here, if that's your intention. No big thing, but using each
feature in its simplest form usually makes things easier to read, and
therefore, easier to maintain in the future.

Given the way the tables are joined, the central table of this query is
AcctPricing. Is it the case that you might have a row in AcctPricing
without any JOINed row in NewPriceList, and that you still want that
represented in the query results as a row with all the values from the other
two tables being NULLs? (What I may be driving at here is NOT to use RIGHT
JOINs except when explicitly necessary. Use INNER JOINs when they will
suffice. Again, the target is to minimize the complexity in reading the
query.)

To show only one row from one of the other two tables (NewPriceList or
ItemPRiceList), and thereby eliminate multiple rows you do not want to
generate, you must select some unique column or set of columns from the
table with multiple rows and filter the query with that using a correlated
subquery. I believe you could add (to the version I show above):

WHERE IP.$P1 = (SELECT MIN($P1) FROM tblItemPRiceList IP1
WHERE IP1.[$LIST#] = AP.BLPLIST AND IP1.Item = AP.[BLREC#])

If, however, you have multiple rows in ItemPRiceList with the same values
for $LIST#, Item, and $P1 then there could occur multiple rows matching the
above criteria. That is, if you have 2 rows in ItemPRiceList with the value
$LIST# = "A" and Item = 1234 and both have $P1 = 3, and if 3 is the smallest
value of $P1 in all rows with $LIST# = "A"and Item = 1234, then you will
still have two rows being contributed from ItemPRiceList. This will be
exactly because you did not specify a necessarily unique way of selecting a
single row from ItemPriceList. Obviously, if there are multiple rows in
ItemPRiceList with the same minimum value in $P1 (and matching the other two
joined values) then each one could contribute different values of INAME and
LNAME in your query. All Access can do is report all of them.

It could be that you were referencing duplicates caused though the
AcctPricing table rather than or in addition to the ItemPRiceList table.
Your original question did not specify from which, or both JOINed tables
this problem could be contributed. You could still try the above code and
let me know if it helped, and we can add more or alter it as needed.

Note: I dropped the ";" at the end of your code, making it possible to add
the code I generated. This new code also uses the aliases I added, so it
won't work against your posted code without modifications, and moving the
position of the ";".

Tom Ellison


kontra said:
Hi,

I have a query that makes a table here is the sql view of it:

SELECT tbl_AcctPricing.BLACCT, tbl_AcctPricing.BNAME,
tbl_AcctPricing.BLRECT, tbl_AcctPricing.[BLREC#], tbl_AcctPricing.BLPLIST,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.Item,tbl_NewPriceList.Item) AS Item1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.INAME,tbl_NewPriceList.INAME) AS INAME1,
tbl_NewPriceList.ICLAS1, tbl_NewPriceList.IPRCCD,
IIf(tbl_AcctPricing.BLRECT
Like "I",qry_ItemPRiceList.LNAME,tbl_NewPriceList.LNAME) AS LNAME1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$LIST#],tbl_NewPriceList.[$LIST#]) AS [$LIST#1],
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$P1],tbl_NewPriceList.[$P1]) AS [$P11] INTO
tbl_test
FROM qry_ItemPRiceList RIGHT JOIN (tbl_NewPriceList RIGHT JOIN
tbl_AcctPricing ON (tbl_NewPriceList.[$LIST#] = tbl_AcctPricing.BLPLIST)
AND
(tbl_NewPriceList.ICLAS1 = tbl_AcctPricing.[BLREC#])) ON
(qry_ItemPRiceList.[$LIST#] = tbl_AcctPricing.BLPLIST) AND
(qry_ItemPRiceList.Item = tbl_AcctPricing.[BLREC#]);

Now this newly created table may have item1 that can have a duplicate but
with the different BLRECT and $P1, what I need to make is show only one of
those records that has a lower value in $P1.

Let me know if I need to explain the process in more details. Thanks in
advance for the help.
Click to expand...
Click to expand...
 
Dear Kontra:

Equals (=) works the same for string and numeric values. LIKE allows wild
cards, and is for strings only. For an experienced programmer (me? perhaps)
seeing LIKE where = would be the same thing just makes me wonder what is
meant or whether there is some mistake. But what you have would work well
enough. It's just a style question.

Another style question involves the joins. This is much less clear what is
best. Now, as AP is the central table in the query, I would list it first.
My rule here (again, a style thing) is to use only LEFT JOINs, which puts
the table in a dependent hierarchy that is top-down. This is a personal
preference. I don't know if any other programmers agree with that approach.
So, if you don't agree, if this doesn't seem natural to you, then feel free
to ignore my preferences.

Please let me know if this works for you and if you require any other
assistance.

Tom Ellison


kontra said:
First of all let me thank you for taking time to explain so much. Now let
me
first answer your questions and repond to the comments:

1. as far as to 'like' I was under impression that you use that with the
string fields and '=' is for number fields. That is why I put 'like' with
out
the wildcard becuase that is a string field containing one of 2 values C
for
Class and I for Item
2. AP table is the main table that I am suppose to base the query on,
since
Account would be the primary in the outcome of this transaction. If the
record is in the AP table then it will defenetly have a corresponding
record
in the NP and/or IP tables. NP talbe contains One Class type and its Items
with tiered prices and IP table contains all items with their classes and
tiered pricing. Ofcourse the eaisest way would be to Combine the 2 item
tables, but the trick is that in AP table Class and Item are values in
Promotion Field, and I don't seem to be able to have the query to connect
by
2 fields at one time. I hope I am making sence.

I will go ahead and try you suggestion. Thank you again.
--
If at first you don''''t succeed, destroy all evidence that you tried.


Tom Ellison said:
Dear Kontra:

Below, I have reproduced your query with some changes that make it easier
for me to read, hopefully without any functional changes. I have added
aliases and reformatted:

SELECT AP.BLACCT, AP.BNAME, AP.BLRECT, AP.[BLREC#],
AP.BLPLIST, IIf(AP.BLRECT Like "I", IP.Item, NP.Item) AS Item1,
IIf(AP.BLRECT Like "I", IP.INAME, NP.INAME) AS INAME1,
NP.ICLAS1, NP.IPRCCD,
IIf(AP.BLRECT Like "I", IP.LNAME, NP.LNAME) AS LNAME1,
IIf(AP.BLRECT Like "I", IP.[$LIST#], NP.[$LIST#]) AS [$LIST#1],
IIf(AP.BLRECT Like "I", IP.[$P1], NP.[$P1]) AS [$P11]
INTO tbl_test
FROM qry_ItemPRiceList IP
RIGHT JOIN (tbl_NewPriceList NP
RIGHT JOIN tbl_AcctPricing AP
ON NP.[$LIST#] = AP.BLPLIST AND NP.ICLAS1 = AP.[BLREC#])
ON IP.[$LIST#] = AP.BLPLIST AND IP.Item = AP.[BLREC#])

A bit of comment on what we have so far.

The use of Like() without any wildcard is surprising. You could just
have
well used "=" here, if that's your intention. No big thing, but using
each
feature in its simplest form usually makes things easier to read, and
therefore, easier to maintain in the future.

Given the way the tables are joined, the central table of this query is
AcctPricing. Is it the case that you might have a row in AcctPricing
without any JOINed row in NewPriceList, and that you still want that
represented in the query results as a row with all the values from the
other
two tables being NULLs? (What I may be driving at here is NOT to use
RIGHT
JOINs except when explicitly necessary. Use INNER JOINs when they will
suffice. Again, the target is to minimize the complexity in reading the
query.)

To show only one row from one of the other two tables (NewPriceList or
ItemPRiceList), and thereby eliminate multiple rows you do not want to
generate, you must select some unique column or set of columns from the
table with multiple rows and filter the query with that using a
correlated
subquery. I believe you could add (to the version I show above):

WHERE IP.$P1 = (SELECT MIN($P1) FROM tblItemPRiceList IP1
WHERE IP1.[$LIST#] = AP.BLPLIST AND IP1.Item = AP.[BLREC#])

If, however, you have multiple rows in ItemPRiceList with the same values
for $LIST#, Item, and $P1 then there could occur multiple rows matching
the
above criteria. That is, if you have 2 rows in ItemPRiceList with the
value
$LIST# = "A" and Item = 1234 and both have $P1 = 3, and if 3 is the
smallest
value of $P1 in all rows with $LIST# = "A"and Item = 1234, then you will
still have two rows being contributed from ItemPRiceList. This will be
exactly because you did not specify a necessarily unique way of selecting
a
single row from ItemPriceList. Obviously, if there are multiple rows in
ItemPRiceList with the same minimum value in $P1 (and matching the other
two
joined values) then each one could contribute different values of INAME
and
LNAME in your query. All Access can do is report all of them.

It could be that you were referencing duplicates caused though the
AcctPricing table rather than or in addition to the ItemPRiceList table.
Your original question did not specify from which, or both JOINed tables
this problem could be contributed. You could still try the above code
and
let me know if it helped, and we can add more or alter it as needed.

Note: I dropped the ";" at the end of your code, making it possible to
add
the code I generated. This new code also uses the aliases I added, so it
won't work against your posted code without modifications, and moving the
position of the ";".

Tom Ellison


kontra said:
Hi,

I have a query that makes a table here is the sql view of it:

SELECT tbl_AcctPricing.BLACCT, tbl_AcctPricing.BNAME,
tbl_AcctPricing.BLRECT, tbl_AcctPricing.[BLREC#],
tbl_AcctPricing.BLPLIST,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.Item,tbl_NewPriceList.Item) AS Item1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.INAME,tbl_NewPriceList.INAME) AS INAME1,
tbl_NewPriceList.ICLAS1, tbl_NewPriceList.IPRCCD,
IIf(tbl_AcctPricing.BLRECT
Like "I",qry_ItemPRiceList.LNAME,tbl_NewPriceList.LNAME) AS LNAME1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$LIST#],tbl_NewPriceList.[$LIST#]) AS [$LIST#1],
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$P1],tbl_NewPriceList.[$P1]) AS [$P11] INTO
tbl_test
FROM qry_ItemPRiceList RIGHT JOIN (tbl_NewPriceList RIGHT JOIN
tbl_AcctPricing ON (tbl_NewPriceList.[$LIST#] =
tbl_AcctPricing.BLPLIST)
AND
(tbl_NewPriceList.ICLAS1 = tbl_AcctPricing.[BLREC#])) ON
(qry_ItemPRiceList.[$LIST#] = tbl_AcctPricing.BLPLIST) AND
(qry_ItemPRiceList.Item = tbl_AcctPricing.[BLREC#]);

Now this newly created table may have item1 that can have a duplicate
but
with the different BLRECT and $P1, what I need to make is show only one
of
those records that has a lower value in $P1.

Let me know if I need to explain the process in more details. Thanks in
advance for the help.
Click to expand...
Click to expand...
Click to expand...
 
I tried it and it didn't work , I don't think I clearly understand the MIN
function you've added.

Here is the example of what I have in those tables:

tbl_AP
BLACCT, BLRECT, BLREC#, BLPLIST,
650, I, amepaper, 2L
650, C, CO, 1L

tbl_NP
Item, INAME, ICLAS1, $LIST#, P1
amepaper, amer 48x54, CO, 1L 4.06
amepaper, amer 48x54, CO, 2L 3.82

tbl_IP
Item, INAME, $LIST#, P1
amepaper, amer 48x54, 1L 4.06
amepaper, amer 48x54, 2L 3.82

Now, maybe you can see where I am a little confused, since my AP tbl can
store infor from both IP and NP, I am not sure how to show the lowest only.

Also if I do only inner join then I do not get any records at all, so here I
have taken you code and what I had and combined it, however I was not able to
understand the MIN and I was not able to insert it:

SELECT tbl_AP.BLACCT, tbl_AP.BNAME, tbl_AP.BLRECT, tbl_AP.[BLREC#],
tbl_AP.BLPLIST,
IIf(tbl_AP.BLRECT Like "I",tbl_IP.Item,tbl_NP.Item) AS Item1,
IIf(tbl_AP.BLRECT Like "I",tbl_IP.INAME,tbl_NP.INAME) AS INAME1,
tbl_NP.ICLAS1, tbl_NP.IPRCCD,
IIf(tbl_AP.BLRECT Like "I",tbl_IP.LNAME,tbl_NP.LNAME) AS LNAME1,
IIf(tbl_AP.BLRECT Like "I",tbl_IP.[$LIST#],tbl_NP.[$LIST#]) AS
[$LIST#1],
IIf(tbl_AP.BLRECT Like "I",tbl_IP.[$P1],tbl_NP.[$P1]) AS [$P11] INTO
tbl_PL
FROM (tbl_AP
LEFT JOIN tbl_IP
ON (tbl_AP.[BLREC#] = tbl_IP.Item) AND (tbl_AP.BLPLIST =
tbl_IP.[$LIST#])) LEFT JOIN tbl_NP
ON (tbl_AP.BLPLIST = tbl_NP.[$LIST#]) AND (tbl_AP.[BLREC#] =
tbl_NP.ICLAS1) ;


Thanks
--
If at first you don''''t succeed, destroy all evidence that you tried.


Tom Ellison said:
Dear Kontra:

Below, I have reproduced your query with some changes that make it easier
for me to read, hopefully without any functional changes. I have added
aliases and reformatted:

SELECT AP.BLACCT, AP.BNAME, AP.BLRECT, AP.[BLREC#],
AP.BLPLIST, IIf(AP.BLRECT Like "I", IP.Item, NP.Item) AS Item1,
IIf(AP.BLRECT Like "I", IP.INAME, NP.INAME) AS INAME1,
NP.ICLAS1, NP.IPRCCD,
IIf(AP.BLRECT Like "I", IP.LNAME, NP.LNAME) AS LNAME1,
IIf(AP.BLRECT Like "I", IP.[$LIST#], NP.[$LIST#]) AS [$LIST#1],
IIf(AP.BLRECT Like "I", IP.[$P1], NP.[$P1]) AS [$P11]
INTO tbl_test
FROM qry_ItemPRiceList IP
RIGHT JOIN (tbl_NewPriceList NP
RIGHT JOIN tbl_AcctPricing AP
ON NP.[$LIST#] = AP.BLPLIST AND NP.ICLAS1 = AP.[BLREC#])
ON IP.[$LIST#] = AP.BLPLIST AND IP.Item = AP.[BLREC#])

A bit of comment on what we have so far.

The use of Like() without any wildcard is surprising. You could just have
well used "=" here, if that's your intention. No big thing, but using each
feature in its simplest form usually makes things easier to read, and
therefore, easier to maintain in the future.

Given the way the tables are joined, the central table of this query is
AcctPricing. Is it the case that you might have a row in AcctPricing
without any JOINed row in NewPriceList, and that you still want that
represented in the query results as a row with all the values from the other
two tables being NULLs? (What I may be driving at here is NOT to use RIGHT
JOINs except when explicitly necessary. Use INNER JOINs when they will
suffice. Again, the target is to minimize the complexity in reading the
query.)

To show only one row from one of the other two tables (NewPriceList or
ItemPRiceList), and thereby eliminate multiple rows you do not want to
generate, you must select some unique column or set of columns from the
table with multiple rows and filter the query with that using a correlated
subquery. I believe you could add (to the version I show above):

WHERE IP.$P1 = (SELECT MIN($P1) FROM tblItemPRiceList IP1
WHERE IP1.[$LIST#] = AP.BLPLIST AND IP1.Item = AP.[BLREC#])

If, however, you have multiple rows in ItemPRiceList with the same values
for $LIST#, Item, and $P1 then there could occur multiple rows matching the
above criteria. That is, if you have 2 rows in ItemPRiceList with the value
$LIST# = "A" and Item = 1234 and both have $P1 = 3, and if 3 is the smallest
value of $P1 in all rows with $LIST# = "A"and Item = 1234, then you will
still have two rows being contributed from ItemPRiceList. This will be
exactly because you did not specify a necessarily unique way of selecting a
single row from ItemPriceList. Obviously, if there are multiple rows in
ItemPRiceList with the same minimum value in $P1 (and matching the other two
joined values) then each one could contribute different values of INAME and
LNAME in your query. All Access can do is report all of them.

It could be that you were referencing duplicates caused though the
AcctPricing table rather than or in addition to the ItemPRiceList table.
Your original question did not specify from which, or both JOINed tables
this problem could be contributed. You could still try the above code and
let me know if it helped, and we can add more or alter it as needed.

Note: I dropped the ";" at the end of your code, making it possible to add
the code I generated. This new code also uses the aliases I added, so it
won't work against your posted code without modifications, and moving the
position of the ";".

Tom Ellison


kontra said:
Hi,

I have a query that makes a table here is the sql view of it:

SELECT tbl_AcctPricing.BLACCT, tbl_AcctPricing.BNAME,
tbl_AcctPricing.BLRECT, tbl_AcctPricing.[BLREC#], tbl_AcctPricing.BLPLIST,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.Item,tbl_NewPriceList.Item) AS Item1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.INAME,tbl_NewPriceList.INAME) AS INAME1,
tbl_NewPriceList.ICLAS1, tbl_NewPriceList.IPRCCD,
IIf(tbl_AcctPricing.BLRECT
Like "I",qry_ItemPRiceList.LNAME,tbl_NewPriceList.LNAME) AS LNAME1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$LIST#],tbl_NewPriceList.[$LIST#]) AS [$LIST#1],
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$P1],tbl_NewPriceList.[$P1]) AS [$P11] INTO
tbl_test
FROM qry_ItemPRiceList RIGHT JOIN (tbl_NewPriceList RIGHT JOIN
tbl_AcctPricing ON (tbl_NewPriceList.[$LIST#] = tbl_AcctPricing.BLPLIST)
AND
(tbl_NewPriceList.ICLAS1 = tbl_AcctPricing.[BLREC#])) ON
(qry_ItemPRiceList.[$LIST#] = tbl_AcctPricing.BLPLIST) AND
(qry_ItemPRiceList.Item = tbl_AcctPricing.[BLREC#]);

Now this newly created table may have item1 that can have a duplicate but
with the different BLRECT and $P1, what I need to make is show only one of
those records that has a lower value in $P1.

Let me know if I need to explain the process in more details. Thanks in
advance for the help.
Click to expand...
Click to expand...
 
Tom,

I am not experienced programmer :) by asking questions like that, I do not
mean that is not natural to me or I desagree( it can't be natural to me and a
can not disagree, since I don't fully understand it), it is just a way for me
to understand it better and to learn, so I don't have to ask the same
question again when I will need to create something where that knowledge can
be applied.

P.S. I posted my reply " didn't work" before I've seen your responce.

--
If at first you don''''t succeed, destroy all evidence that you tried.


Tom Ellison said:
Dear Kontra:

Equals (=) works the same for string and numeric values. LIKE allows wild
cards, and is for strings only. For an experienced programmer (me? perhaps)
seeing LIKE where = would be the same thing just makes me wonder what is
meant or whether there is some mistake. But what you have would work well
enough. It's just a style question.

Another style question involves the joins. This is much less clear what is
best. Now, as AP is the central table in the query, I would list it first.
My rule here (again, a style thing) is to use only LEFT JOINs, which puts
the table in a dependent hierarchy that is top-down. This is a personal
preference. I don't know if any other programmers agree with that approach.
So, if you don't agree, if this doesn't seem natural to you, then feel free
to ignore my preferences.

Please let me know if this works for you and if you require any other
assistance.

Tom Ellison


kontra said:
First of all let me thank you for taking time to explain so much. Now let
me
first answer your questions and repond to the comments:

1. as far as to 'like' I was under impression that you use that with the
string fields and '=' is for number fields. That is why I put 'like' with
out
the wildcard becuase that is a string field containing one of 2 values C
for
Class and I for Item
2. AP table is the main table that I am suppose to base the query on,
since
Account would be the primary in the outcome of this transaction. If the
record is in the AP table then it will defenetly have a corresponding
record
in the NP and/or IP tables. NP talbe contains One Class type and its Items
with tiered prices and IP table contains all items with their classes and
tiered pricing. Ofcourse the eaisest way would be to Combine the 2 item
tables, but the trick is that in AP table Class and Item are values in
Promotion Field, and I don't seem to be able to have the query to connect
by
2 fields at one time. I hope I am making sence.

I will go ahead and try you suggestion. Thank you again.
--
If at first you don''''t succeed, destroy all evidence that you tried.


Tom Ellison said:
Dear Kontra:

Below, I have reproduced your query with some changes that make it easier
for me to read, hopefully without any functional changes. I have added
aliases and reformatted:

SELECT AP.BLACCT, AP.BNAME, AP.BLRECT, AP.[BLREC#],
AP.BLPLIST, IIf(AP.BLRECT Like "I", IP.Item, NP.Item) AS Item1,
IIf(AP.BLRECT Like "I", IP.INAME, NP.INAME) AS INAME1,
NP.ICLAS1, NP.IPRCCD,
IIf(AP.BLRECT Like "I", IP.LNAME, NP.LNAME) AS LNAME1,
IIf(AP.BLRECT Like "I", IP.[$LIST#], NP.[$LIST#]) AS [$LIST#1],
IIf(AP.BLRECT Like "I", IP.[$P1], NP.[$P1]) AS [$P11]
INTO tbl_test
FROM qry_ItemPRiceList IP
RIGHT JOIN (tbl_NewPriceList NP
RIGHT JOIN tbl_AcctPricing AP
ON NP.[$LIST#] = AP.BLPLIST AND NP.ICLAS1 = AP.[BLREC#])
ON IP.[$LIST#] = AP.BLPLIST AND IP.Item = AP.[BLREC#])

A bit of comment on what we have so far.

The use of Like() without any wildcard is surprising. You could just
have
well used "=" here, if that's your intention. No big thing, but using
each
feature in its simplest form usually makes things easier to read, and
therefore, easier to maintain in the future.

Given the way the tables are joined, the central table of this query is
AcctPricing. Is it the case that you might have a row in AcctPricing
without any JOINed row in NewPriceList, and that you still want that
represented in the query results as a row with all the values from the
other
two tables being NULLs? (What I may be driving at here is NOT to use
RIGHT
JOINs except when explicitly necessary. Use INNER JOINs when they will
suffice. Again, the target is to minimize the complexity in reading the
query.)

To show only one row from one of the other two tables (NewPriceList or
ItemPRiceList), and thereby eliminate multiple rows you do not want to
generate, you must select some unique column or set of columns from the
table with multiple rows and filter the query with that using a
correlated
subquery. I believe you could add (to the version I show above):

WHERE IP.$P1 = (SELECT MIN($P1) FROM tblItemPRiceList IP1
WHERE IP1.[$LIST#] = AP.BLPLIST AND IP1.Item = AP.[BLREC#])

If, however, you have multiple rows in ItemPRiceList with the same values
for $LIST#, Item, and $P1 then there could occur multiple rows matching
the
above criteria. That is, if you have 2 rows in ItemPRiceList with the
value
$LIST# = "A" and Item = 1234 and both have $P1 = 3, and if 3 is the
smallest
value of $P1 in all rows with $LIST# = "A"and Item = 1234, then you will
still have two rows being contributed from ItemPRiceList. This will be
exactly because you did not specify a necessarily unique way of selecting
a
single row from ItemPriceList. Obviously, if there are multiple rows in
ItemPRiceList with the same minimum value in $P1 (and matching the other
two
joined values) then each one could contribute different values of INAME
and
LNAME in your query. All Access can do is report all of them.

It could be that you were referencing duplicates caused though the
AcctPricing table rather than or in addition to the ItemPRiceList table.
Your original question did not specify from which, or both JOINed tables
this problem could be contributed. You could still try the above code
and
let me know if it helped, and we can add more or alter it as needed.

Note: I dropped the ";" at the end of your code, making it possible to
add
the code I generated. This new code also uses the aliases I added, so it
won't work against your posted code without modifications, and moving the
position of the ";".

Tom Ellison


Hi,

I have a query that makes a table here is the sql view of it:

SELECT tbl_AcctPricing.BLACCT, tbl_AcctPricing.BNAME,
tbl_AcctPricing.BLRECT, tbl_AcctPricing.[BLREC#],
tbl_AcctPricing.BLPLIST,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.Item,tbl_NewPriceList.Item) AS Item1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.INAME,tbl_NewPriceList.INAME) AS INAME1,
tbl_NewPriceList.ICLAS1, tbl_NewPriceList.IPRCCD,
IIf(tbl_AcctPricing.BLRECT
Like "I",qry_ItemPRiceList.LNAME,tbl_NewPriceList.LNAME) AS LNAME1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$LIST#],tbl_NewPriceList.[$LIST#]) AS [$LIST#1],
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$P1],tbl_NewPriceList.[$P1]) AS [$P11] INTO
tbl_test
FROM qry_ItemPRiceList RIGHT JOIN (tbl_NewPriceList RIGHT JOIN
tbl_AcctPricing ON (tbl_NewPriceList.[$LIST#] =
tbl_AcctPricing.BLPLIST)
AND
(tbl_NewPriceList.ICLAS1 = tbl_AcctPricing.[BLREC#])) ON
(qry_ItemPRiceList.[$LIST#] = tbl_AcctPricing.BLPLIST) AND
(qry_ItemPRiceList.Item = tbl_AcctPricing.[BLREC#]);

Now this newly created table may have item1 that can have a duplicate
but
with the different BLRECT and $P1, what I need to make is show only one
of
those records that has a lower value in $P1.

Let me know if I need to explain the process in more details. Thanks in
advance for the help.
Click to expand...
Click to expand...
Click to expand...
 
Dear Kontra:

Did we make any progress on your main question? How is all this going?

Tom Ellison


kontra said:
Tom,

I am not experienced programmer :) by asking questions like that, I do not
mean that is not natural to me or I desagree( it can't be natural to me
and a
can not disagree, since I don't fully understand it), it is just a way for
me
to understand it better and to learn, so I don't have to ask the same
question again when I will need to create something where that knowledge
can
be applied.

P.S. I posted my reply " didn't work" before I've seen your responce.

--
If at first you don''''t succeed, destroy all evidence that you tried.


Tom Ellison said:
Dear Kontra:

Equals (=) works the same for string and numeric values. LIKE allows
wild
cards, and is for strings only. For an experienced programmer (me?
perhaps)
seeing LIKE where = would be the same thing just makes me wonder what is
meant or whether there is some mistake. But what you have would work
well
enough. It's just a style question.

Another style question involves the joins. This is much less clear what
is
best. Now, as AP is the central table in the query, I would list it
first.
My rule here (again, a style thing) is to use only LEFT JOINs, which puts
the table in a dependent hierarchy that is top-down. This is a personal
preference. I don't know if any other programmers agree with that
approach.
So, if you don't agree, if this doesn't seem natural to you, then feel
free
to ignore my preferences.

Please let me know if this works for you and if you require any other
assistance.

Tom Ellison


kontra said:
First of all let me thank you for taking time to explain so much. Now
let
me
first answer your questions and repond to the comments:

1. as far as to 'like' I was under impression that you use that with
the
string fields and '=' is for number fields. That is why I put 'like'
with
out
the wildcard becuase that is a string field containing one of 2 values
C
for
Class and I for Item
2. AP table is the main table that I am suppose to base the query on,
since
Account would be the primary in the outcome of this transaction. If the
record is in the AP table then it will defenetly have a corresponding
record
in the NP and/or IP tables. NP talbe contains One Class type and its
Items
with tiered prices and IP table contains all items with their classes
and
tiered pricing. Ofcourse the eaisest way would be to Combine the 2 item
tables, but the trick is that in AP table Class and Item are values in
Promotion Field, and I don't seem to be able to have the query to
connect
by
2 fields at one time. I hope I am making sence.

I will go ahead and try you suggestion. Thank you again.
--
If at first you don''''t succeed, destroy all evidence that you tried.


:

Dear Kontra:

Below, I have reproduced your query with some changes that make it
easier
for me to read, hopefully without any functional changes. I have
added
aliases and reformatted:

SELECT AP.BLACCT, AP.BNAME, AP.BLRECT, AP.[BLREC#],
AP.BLPLIST, IIf(AP.BLRECT Like "I", IP.Item, NP.Item) AS Item1,
IIf(AP.BLRECT Like "I", IP.INAME, NP.INAME) AS INAME1,
NP.ICLAS1, NP.IPRCCD,
IIf(AP.BLRECT Like "I", IP.LNAME, NP.LNAME) AS LNAME1,
IIf(AP.BLRECT Like "I", IP.[$LIST#], NP.[$LIST#]) AS [$LIST#1],
IIf(AP.BLRECT Like "I", IP.[$P1], NP.[$P1]) AS [$P11]
INTO tbl_test
FROM qry_ItemPRiceList IP
RIGHT JOIN (tbl_NewPriceList NP
RIGHT JOIN tbl_AcctPricing AP
ON NP.[$LIST#] = AP.BLPLIST AND NP.ICLAS1 = AP.[BLREC#])
ON IP.[$LIST#] = AP.BLPLIST AND IP.Item = AP.[BLREC#])

A bit of comment on what we have so far.

The use of Like() without any wildcard is surprising. You could just
have
well used "=" here, if that's your intention. No big thing, but using
each
feature in its simplest form usually makes things easier to read, and
therefore, easier to maintain in the future.

Given the way the tables are joined, the central table of this query
is
AcctPricing. Is it the case that you might have a row in AcctPricing
without any JOINed row in NewPriceList, and that you still want that
represented in the query results as a row with all the values from the
other
two tables being NULLs? (What I may be driving at here is NOT to use
RIGHT
JOINs except when explicitly necessary. Use INNER JOINs when they
will
suffice. Again, the target is to minimize the complexity in reading
the
query.)

To show only one row from one of the other two tables (NewPriceList or
ItemPRiceList), and thereby eliminate multiple rows you do not want to
generate, you must select some unique column or set of columns from
the
table with multiple rows and filter the query with that using a
correlated
subquery. I believe you could add (to the version I show above):

WHERE IP.$P1 = (SELECT MIN($P1) FROM tblItemPRiceList IP1
WHERE IP1.[$LIST#] = AP.BLPLIST AND IP1.Item = AP.[BLREC#])

If, however, you have multiple rows in ItemPRiceList with the same
values
for $LIST#, Item, and $P1 then there could occur multiple rows
matching
the
above criteria. That is, if you have 2 rows in ItemPRiceList with the
value
$LIST# = "A" and Item = 1234 and both have $P1 = 3, and if 3 is the
smallest
value of $P1 in all rows with $LIST# = "A"and Item = 1234, then you
will
still have two rows being contributed from ItemPRiceList. This will
be
exactly because you did not specify a necessarily unique way of
selecting
a
single row from ItemPriceList. Obviously, if there are multiple rows
in
ItemPRiceList with the same minimum value in $P1 (and matching the
other
two
joined values) then each one could contribute different values of
INAME
and
LNAME in your query. All Access can do is report all of them.

It could be that you were referencing duplicates caused though the
AcctPricing table rather than or in addition to the ItemPRiceList
table.
Your original question did not specify from which, or both JOINed
tables
this problem could be contributed. You could still try the above code
and
let me know if it helped, and we can add more or alter it as needed.

Note: I dropped the ";" at the end of your code, making it possible
to
add
the code I generated. This new code also uses the aliases I added, so
it
won't work against your posted code without modifications, and moving
the
position of the ";".

Tom Ellison


Hi,

I have a query that makes a table here is the sql view of it:

SELECT tbl_AcctPricing.BLACCT, tbl_AcctPricing.BNAME,
tbl_AcctPricing.BLRECT, tbl_AcctPricing.[BLREC#],
tbl_AcctPricing.BLPLIST,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.Item,tbl_NewPriceList.Item) AS Item1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.INAME,tbl_NewPriceList.INAME) AS INAME1,
tbl_NewPriceList.ICLAS1, tbl_NewPriceList.IPRCCD,
IIf(tbl_AcctPricing.BLRECT
Like "I",qry_ItemPRiceList.LNAME,tbl_NewPriceList.LNAME) AS LNAME1,
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$LIST#],tbl_NewPriceList.[$LIST#]) AS
[$LIST#1],
IIf(tbl_AcctPricing.BLRECT Like
"I",qry_ItemPRiceList.[$P1],tbl_NewPriceList.[$P1]) AS [$P11] INTO
tbl_test
FROM qry_ItemPRiceList RIGHT JOIN (tbl_NewPriceList RIGHT JOIN
tbl_AcctPricing ON (tbl_NewPriceList.[$LIST#] =
tbl_AcctPricing.BLPLIST)
AND
(tbl_NewPriceList.ICLAS1 = tbl_AcctPricing.[BLREC#])) ON
(qry_ItemPRiceList.[$LIST#] = tbl_AcctPricing.BLPLIST) AND
(qry_ItemPRiceList.Item = tbl_AcctPricing.[BLREC#]);

Now this newly created table may have item1 that can have a
duplicate
but
with the different BLRECT and $P1, what I need to make is show only
one
of
those records that has a lower value in $P1.

Let me know if I need to explain the process in more details. Thanks
in
advance for the help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top