By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

How do I create a query to solve this problem?

P: n/a

Hi
I have a table which Contains entries with RefCode field containing
INVP or INVPD

Common fields in each entry would be InvoiceNo, Total and PurTyp for
example.

You could have 1001 500.50 INVP 1
1001 500.50 INVPID 1
1002 123.00 INVP 1
1002 123.00 INVPID 2
1003 324.00 INVP 1
1003 324.00 INVPID 1
For historic reasons some of the pairs have a 2 in the PurType field
which needs to be changed to a 1.

How can I isolate the pairs where the PurType fields are not matched
as per the second pair above?

I have several thousand records with possible mismatches.

Thanks
Patrick

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Should every entry in this field have a 1 at the end if there is currently a
2? If so, use an update query. Find values of this field that have 2 at the
end and update the field to a 1 at the end. This won't check for "pairs", it
will simply change all entries that end in 2 to end in 1. If you need other
possible changes, you'll need to be more specific.

Example:
UPDATE Table1 SET Table1.Field1 = Left([Field1],Len([Field1])-1) & "1"
WHERE ((Right([Field1],1)="2"));

--
Wayne Morgan
MS Access MVP
"Patrick Fisher" <in**@psoftuk.com> wrote in message
news:pv********************************@4ax.com...

Hi
I have a table which Contains entries with RefCode field containing
INVP or INVPD

Common fields in each entry would be InvoiceNo, Total and PurTyp for
example.

You could have 1001 500.50 INVP 1
1001 500.50 INVPID 1
1002 123.00 INVP 1
1002 123.00 INVPID 2
1003 324.00 INVP 1
1003 324.00 INVPID 1
For historic reasons some of the pairs have a 2 in the PurType field
which needs to be changed to a 1.

How can I isolate the pairs where the PurType fields are not matched
as per the second pair above?

I have several thousand records with possible mismatches.

Thanks
Patrick

Nov 13 '05 #2

P: n/a
Thanks for your response

The problem is that there are hundreds of INVPs that currently have 2
in the PurType field but the only ones that need to be changed to a 1
are those that pair up with INVPIDs.

There are hundreds more INVPs that pair up with other codes that do
not need changing.

I need to be able to isolate the the INVPs that Pair up with INPIDs
only, then of course I can run a simple update query to deal with
them.

Hope this makes it clearer.
Patrick
On Mon, 18 Oct 2004 22:52:24 GMT, "Wayne Morgan"
<co***************************@hotmail.com> wrote:
Should every entry in this field have a 1 at the end if there is currently a
2? If so, use an update query. Find values of this field that have 2 at the
end and update the field to a 1 at the end. This won't check for "pairs", it
will simply change all entries that end in 2 to end in 1. If you need other
possible changes, you'll need to be more specific.

Example:
UPDATE Table1 SET Table1.Field1 = Left([Field1],Len([Field1])-1) & "1"
WHERE ((Right([Field1],1)="2"));


Nov 13 '05 #3

P: n/a
Patrick Fisher wrote:
Thanks for your response

The problem is that there are hundreds of INVPs that currently have 2
in the PurType field but the only ones that need to be changed to a 1
are those that pair up with INVPIDs.

There are hundreds more INVPs that pair up with other codes that do
not need changing.

I need to be able to isolate the the INVPs that Pair up with INPIDs
only, then of course I can run a simple update query to deal with
them.

Hope this makes it clearer.
Patrick
Create a query of invoice #, amount for type = 1 (or whatever the
criteria is) for INVPS and type.
Create a query of invoice #, amountfor INVPIDS and the type.

Create aquery and link the invoice#, amount together. You should end up
with a 1 to 1 match. Then update where the types don't match.

You might have to stretch it out to creating a query where you group on
the invoice number and get an INVPS count and INVPIDS count and the
count is 1 for each. Then you know where there are pairs. If you need
this kind of breakdown, you'll probably have to do a MakeTable with the
final results. Use the maketable and link to the current table for the
update.

You should make a copy of the tables before you do the update.



On Mon, 18 Oct 2004 22:52:24 GMT, "Wayne Morgan"
<co***************************@hotmail.com> wrote:

Should every entry in this field have a 1 at the end if there is currently a
2? If so, use an update query. Find values of this field that have 2 at the
end and update the field to a 1 at the end. This won't check for "pairs", it
will simply change all entries that end in 2 to end in 1. If you need other
possible changes, you'll need to be more specific.

Example:
UPDATE Table1 SET Table1.Field1 = Left([Field1],Len([Field1])-1) & "1"
WHERE ((Right([Field1],1)="2"));


Nov 13 '05 #4

P: n/a
Patrick,

In that case, let's modify Salad's option a little.

Create 2 queries, one for INVPID and one for INVP. Create a "calculated
field" in each one leaving off the last character by using the Left()
function. Link the 2 queries on this field in an Update query (total of 3
queries). Update the INVP fields last character to a 1 as previously
indicated.

Query1 (Fix1)
SELECT Table17.Field1, Val([Field1]) AS Expr1
FROM Table17
WHERE (((InStr([Field1],"INVPID"))=True));

Query2 (Fix2)
SELECT Table17.Field1, Val([Field1]) AS Expr1
FROM Table17
WHERE (((InStr([Field1],"INVP"))=True) AND
((InStr([Field1],"INVPID"))=False));

Query3
UPDATE Fix1 INNER JOIN Fix2 ON Fix1.Expr1 = Fix2.Expr1 SET Fix1.Field1 =
Left([Fix1.Field1],Len([Fix1.Field1])-1) & "1"
WHERE (((Right([Fix1.Field1],1))="2"));

--
Wayne Morgan
MS Access MVP
"Patrick Fisher" <in**@psoftuk.com> wrote in message
news:64********************************@4ax.com...
Thanks for your response

The problem is that there are hundreds of INVPs that currently have 2
in the PurType field but the only ones that need to be changed to a 1
are those that pair up with INVPIDs.

There are hundreds more INVPs that pair up with other codes that do
not need changing.

I need to be able to isolate the the INVPs that Pair up with INPIDs
only, then of course I can run a simple update query to deal with
them.

Hope this makes it clearer.
Patrick
On Mon, 18 Oct 2004 22:52:24 GMT, "Wayne Morgan"
<co***************************@hotmail.com> wrote:
Should every entry in this field have a 1 at the end if there is currently
a
2? If so, use an update query. Find values of this field that have 2 at
the
end and update the field to a 1 at the end. This won't check for "pairs",
it
will simply change all entries that end in 2 to end in 1. If you need
other
possible changes, you'll need to be more specific.

Example:
UPDATE Table1 SET Table1.Field1 = Left([Field1],Len([Field1])-1) & "1"
WHERE ((Right([Field1],1)="2"));

Nov 13 '05 #5

P: n/a
PS, I made a modification to what I was doing after I typed the top part of
the previous message. I used VAL instead of Left. This will return just the
number portion at the front of the field to compare the two values on. We
can't compare on the text also because one is INVPID and one is INVP, so
they aren't equal if that part is included.

--
Wayne Morgan
MS Access MVP
Nov 13 '05 #6

P: n/a
Dennis
The 1 and the 2 are separate fields to confirm the purchase type.

Thank you all for your answers.

Patrick

On Wed, 20 Oct 2004 06:30:25 GMT, Dennis Lee Bieber
<wl*****@ix.netcom.com> wrote:
On Mon, 18 Oct 2004 20:49:58 GMT, Patrick Fisher <in**@psoftuk.com>
declaimed the following in comp.databases.ms-access:
Common fields in each entry would be InvoiceNo, Total and PurTyp for
example.

You could have 1001 500.50 INVP 1
1001 500.50 INVPID 1
1002 123.00 INVP 1
1002 123.00 INVPID 2
1003 324.00 INVP 1
1003 324.00 INVPID 1

I have to presume your "INVP 1" is a single field (in
which case it is poorly, to my mind, laid out -- I'd either split it
into a type-category [INVP, INVPID] and a code [1, 2, ...]; or get rid
of the spaces)[if it is already a split field, you'll have to play with
the subselect WHERE clause below to handle (t#.f1 = xxx and t#.f2 =
yyy)]

For historic reasons some of the pairs have a 2 in the PurType field
which needs to be changed to a 1.

How can I isolate the pairs where the PurType fields are not matched
as per the second pair above?

I added a few other lines for test purposes: a 2-2 pair, and a
3-2 mismatch; neither of them should be changed if I understand your
criteria.

InvoiceNumber Total PurTyp
1001 $500.50 INVP 1
1001 $500.50 INVPID 1
1002 $123.00 INVP 1
1002 $123.00 INVPID 2
1003 $324.00 INVP 1
1003 $324.00 INVPID 1
1010 $129.00 INVP 2
1010 $129.00 INVPID 2
1011 $838.00 INVP 3
1011 $838.00 INVPID 2

UPDATE Table1
SET
PurTyp = 'INVPID 1'
WHERE
InvoiceNumber IN
(SELECT t1.InvoiceNumber
FROM Table1 AS t1
INNER JOIN Table1 AS t2
ON
(t1.InvoiceNumber=t2.InvoiceNumber)
AND (t1.Total=t2.Total)
WHERE
t1.PurTyp = 'INVPID 2'
AND t2.PurTyp = 'INVP 1')
AND PurTyp = 'INVPID 2';

The subselect (self) inner join matches up invoice/total pairs,
the subselect where clause restricts the set to only those where the
first alias has INVPID 2 and the second alias has INVP 1. The subselect
returns the InvoiceNumber (s) for only those pairs. The Update where
clause then matches any record with an InvoiceNumber from the subselect
AND where it is an INVPID 2 record. Changes that, then, to INVPID 1

InvoiceNumber Total PurTyp
1001 $500.50 INVP 1
1001 $500.50 INVPID 1
1002 $123.00 INVP 1
1002 $123.00 INVPID 1
1003 $324.00 INVP 1
1003 $324.00 INVPID 1
1010 $129.00 INVP 2
1010 $129.00 INVPID 2
1011 $838.00 INVP 3
1011 $838.00 INVPID 2

If your records have a unique (autonumber) field, the outermost
"AND PurTyp = 'INVPID 2'" could be dropped, and the subselect changed
from "InvoiceNumber IN (SELECT t1.InvoiceNumber..." to "uniqueID IN
(SELECT t1.uniqueID...", since the t1 record has already been isolated
to an "INVPID 2" record.


Nov 13 '05 #7

P: n/a
On Wed, 20 Oct 2004 06:30:25 GMT, Dennis Lee Bieber
<wl*****@ix.netcom.com> declaimed the following in
comp.databases.ms-access:
I have to presume your "INVP 1" is a single field (in
which case it is poorly, to my mind, laid out -- I'd either split it
into a type-category [INVP, INVPID] and a code [1, 2, ...]; or get rid
of the spaces)[if it is already a split field, you'll have to play with
the subselect WHERE clause below to handle (t#.f1 = xxx and t#.f2 =
yyy)]
Assuming they are separate, I tested today with...


InvoiceNumber Total RefCode PurTyp
1001 $500.50 INVP 1
1001 $500.50 INVPID 1
1002 $123.00 INVP 1
1002 $123.00 INVPID 2 <====
1003 $324.00 INVP 1
1003 $324.00 INVPID 1
1010 $129.00 INVP 2
1010 $129.00 INVPID 2
1011 $838.00 INVP 3
1011 $838.00 INVPID 2
1020 $987.00 INVPID 2 <====
1020 $987.00 INVP 1

UPDATE Table1
SET
PurTyp = '1'
WHERE
InvoiceNumber IN
(SELECT t1.InvoiceNumber
FROM
Table1 AS t1
INNER JOIN
Table1 AS t2
ON
(t1.InvoiceNumber=t2.InvoiceNumber)
AND (t1.Total=t2.Total)
WHERE
(t1.RefCode = 'INVPID' AND t1.PurTyp =
2)
AND (t2.RefCode = 'INVP' AND t2.PurTyp =
1 ))
AND RefCode = 'INVPID';

InvoiceNumber Total RefCode PurTyp
1001 $500.50 INVP 1
1001 $500.50 INVPID 1
1002 $123.00 INVP 1
1002 $123.00 INVPID 1 <====
1003 $324.00 INVP 1
1003 $324.00 INVPID 1
1010 $129.00 INVP 2
1010 $129.00 INVPID 2
1011 $838.00 INVP 3
1011 $838.00 INVPID 2
1020 $987.00 INVPID 1 <====
1020 $987.00 INVP 1

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.