Code Check Request - Query to find reused values

Dear all,

I have two tables, POs and Orders.  POs represent purchase orders from our 
client, and Orders represent internal orders.  One PO can be related to many 
orders.
Each order has a Sales Rep (data in a different table, ID in Orders as FK).  
The PO number is not a unique ID in the client order data (as one PO can 
cover many separate client orders).

Graphically, the structure looks something like this (* indicates a unique 
ID):

POs		Orders		Reps
Client Order ID*	ORDER ID*
PO Code  <--------->	PO NUM
		SALES REP ID <-------	SALES REP ID*

In theory, each PO should relate to one and only one SR.  However, it seems 
that the client may be reusing old PO numbers with different SRs.

I created the following query to identify whether this is actually happening:

SELECT PONUM, REPNUM
FROM 
    (SELECT [POs].[PO Code] AS PONUM, [Orders].[SALES REP ID] AS REPNUM
     FROM [POs] INNER JOIN [Orders] ON [POs].[PO Code] = [Orders].[PO NUM]
     GROUP BY [POs].[PO Code], [Orders].[SALES REP ID]) AS Q
GROUP BY PONUM, REPNUM
HAVING count(PONUM) > 1;

My logic:
The subquery generates unique combinations of PO Number and SR, and the 
outer query identifies any duplicates of PO Number.  (i.e. PONUM:REPNUM =/= 
1:1)

My questions:
1) Does this code actually do what I think it does?
2) Am I reinventing the wheel here?

Any advice or suggestions will be much appreciated.

Thanks,
Alex.

0
Utf
2/9/2010 1:33:01 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
762 Views

Similar Articles

[PageSpeed] 2

Your table structure looks sound. Is the combinantion of the PO NUM and SALES 
REP ID fields in the Orders table a unique index? They should be.

If they are reusing stuff, are there date fields in any of the tables where 
you could see this happening. I'd think that an OrderDate field with the 
default value of Date() or Now() might help.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tokyo Alex" wrote:

> Dear all,
> 
> I have two tables, POs and Orders.  POs represent purchase orders from our 
> client, and Orders represent internal orders.  One PO can be related to many 
> orders.
> Each order has a Sales Rep (data in a different table, ID in Orders as FK).  
> The PO number is not a unique ID in the client order data (as one PO can 
> cover many separate client orders).
> 
> Graphically, the structure looks something like this (* indicates a unique 
> ID):
> 
> POs		Orders		Reps
> Client Order ID*	ORDER ID*
> PO Code  <--------->	PO NUM
> 		SALES REP ID <-------	SALES REP ID*
> 
> In theory, each PO should relate to one and only one SR.  However, it seems 
> that the client may be reusing old PO numbers with different SRs.
> 
> I created the following query to identify whether this is actually happening:
> 
> SELECT PONUM, REPNUM
> FROM 
>     (SELECT [POs].[PO Code] AS PONUM, [Orders].[SALES REP ID] AS REPNUM
>      FROM [POs] INNER JOIN [Orders] ON [POs].[PO Code] = [Orders].[PO NUM]
>      GROUP BY [POs].[PO Code], [Orders].[SALES REP ID]) AS Q
> GROUP BY PONUM, REPNUM
> HAVING count(PONUM) > 1;
> 
> My logic:
> The subquery generates unique combinations of PO Number and SR, and the 
> outer query identifies any duplicates of PO Number.  (i.e. PONUM:REPNUM =/= 
> 1:1)
> 
> My questions:
> 1) Does this code actually do what I think it does?
> 2) Am I reinventing the wheel here?
> 
> Any advice or suggestions will be much appreciated.
> 
> Thanks,
> Alex.
> 
0
Utf
2/9/2010 2:33:01 AM
Hi Jerry,

Thanks for the response.

Unfortunately, the PO NUM and SALES REP ID is not a unique index, and I have 
no ability to make it one (ODBC Linked table controlled by a different 
department).  Also, the Orders table holds orders from clients other than the 
one I'm dealing with, and I guess there is an outside chance that a 
legitimate duplicate could occur if another client has similar PO numbers.

There are various date fields in both the client (PO) and internal (Orders) 
table.  Using the same query design as below, I've tested these fields also.

In all cases, the query returns no records.  Which means either that PO 
numbers are not being reused, or my query logic is faulty.  I hope it's the 
former <g>

Thanks again,
Alex.

"Jerry Whittle" wrote:

> Your table structure looks sound. Is the combinantion of the PO NUM and SALES 
> REP ID fields in the Orders table a unique index? They should be.
> 
> If they are reusing stuff, are there date fields in any of the tables where 
> you could see this happening. I'd think that an OrderDate field with the 
> default value of Date() or Now() might help.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Tokyo Alex" wrote:
> 
> > Dear all,
> > 
> > I have two tables, POs and Orders.  POs represent purchase orders from our 
> > client, and Orders represent internal orders.  One PO can be related to many 
> > orders.
> > Each order has a Sales Rep (data in a different table, ID in Orders as FK).  
> > The PO number is not a unique ID in the client order data (as one PO can 
> > cover many separate client orders).
> > 
> > Graphically, the structure looks something like this (* indicates a unique 
> > ID):
> > 
> > POs		Orders		Reps
> > Client Order ID*	ORDER ID*
> > PO Code  <--------->	PO NUM
> > 		SALES REP ID <-------	SALES REP ID*
> > 
> > In theory, each PO should relate to one and only one SR.  However, it seems 
> > that the client may be reusing old PO numbers with different SRs.
> > 
> > I created the following query to identify whether this is actually happening:
> > 
> > SELECT PONUM, REPNUM
> > FROM 
> >     (SELECT [POs].[PO Code] AS PONUM, [Orders].[SALES REP ID] AS REPNUM
> >      FROM [POs] INNER JOIN [Orders] ON [POs].[PO Code] = [Orders].[PO NUM]
> >      GROUP BY [POs].[PO Code], [Orders].[SALES REP ID]) AS Q
> > GROUP BY PONUM, REPNUM
> > HAVING count(PONUM) > 1;
> > 
> > My logic:
> > The subquery generates unique combinations of PO Number and SR, and the 
> > outer query identifies any duplicates of PO Number.  (i.e. PONUM:REPNUM =/= 
> > 1:1)
> > 
> > My questions:
> > 1) Does this code actually do what I think it does?
> > 2) Am I reinventing the wheel here?
> > 
> > Any advice or suggestions will be much appreciated.
> > 
> > Thanks,
> > Alex.
> > 
0
Utf
2/9/2010 2:53:01 AM
Reply:

Similar Artilces:

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

"the wizard is unable to open your query in datasheet mode, possi
"the wizard is unable to open your query in datasheet mode, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" what this message mean ,and how can i solve it Hi - Please provide more details as to what you are doing when you get the error, what your database setup is (i.e. split Fe/BE?, multiuser?, version? etc). Without more information, we can only guess. Thanks - John amr wrote: >"the wizard is unable to open your query in datasheet mode, possibly because >another user has a ...

Vb.net 2008 ContextMenuStrip logical error when running code
Greetings, I have a connectmenustrip item that when clicked runs the following code (see below) Now if the event is called by the button i.e. cmdDeleteingBooking.Click the linq query returns the appropriate value. However when called by cntMnuCancelBookingItem.Click is returns 0 even if a checkbox is of 'TRUE' value. Debugging shows the code runs exactly the same code (which loops around rows in a datagridview checking if the checkbox has been checked). Could someone explain the reasoning why the same code would return different results? Private Sub cmdDelete...

RMS Status Codes
Just wondering if anyone has a list of what the RMS Batch.Status codes 0-15 mean? I can't find them defined anywhere. I'm specifically looking at how to identify Blind Closes so I don't count them in totals until they'e been closed. Thanks! -Zim There is a Knowledge Base Article that covers the different Batch Status codes from 0 - 31. Just search for 'batch status codes' -- Robert Armstrong RMS Systems Inc. www.retail-pos.com "Zim" <Zim@discussions.microsoft.com> wrote in message news:C72515DB-AD45-4C7D-B8DE-0A18E4A6D0D0@micr...

Excel worksheet with VBE codes don't work elsewhere
Hi, Some of my excel worksheets with embedded controls and VBA codes don' work when I open it on another PC. Is there another way to make i work? Thx -- lazybea ----------------------------------------------------------------------- lazybear's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3519 View this thread: http://www.excelforum.com/showthread.php?threadid=54955 Specifically what problems are you having? Saying "don't work" means absolutely nothing. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC ww...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

Find and mark duplicates
If I have two columns with data, how do I compare these two columns to find all duplicates and put an X on a third colum next to all duplicates? Thanks. =if(a1=b1,"X","") "maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message news:139BC711-FAAC-4CD2-A533-CE49BA77291F@microsoft.com... > If I have two columns with data, how do I compare these two columns to > find > all duplicates and put an X on a third colum next to all duplicates? > Thanks. The problem is, I don't know which two cells to compare, I need to know if any o...

re post range check
hi i have a worksheet with a lot of data on it column A contains the date column B contains an area column C contains the fault column D contain a quantity A B C D 1/4/04 stores damaged 10 7/4/04 bins rust 60 -- -- --- -- -- --- - -- -- 31-12-04 floor ...

Sum amount if = 2 value's
I have a spreadsheet of payment types for which I want to sum the tota amount per type per month A B C Type Amount Month I'm able to get the total amount per type by usin =SUMIF(A:A,"TYPE",B:B), but can't work out how to get a total for eac type each month Somthing along these lines: =SUMIF((A:A,"TYPE",B:B)&C:C,"MONTH")) ???? Any idea's -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.ph...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

Run series of query -- I don't want any pop-up
I have generated 4 queries (1. empty all, 2. import data, 3. generate information, 4. Make a new table) I've created a button to run these 4 quries sequencially. ------------------------------------------------------------------------------------ Problem: MS ACCESS pops up "confirmation dialog" to ask me whether to delete/modify the table. ------------------------------------------------------------------------------------ Question: I don't want any question to be asked. I just want those queries to be executed once i press my button. -------------------------------...

Outlook Express 6 Spell Check #2
I am using Outlook Express 6 on Windows XP, and I have a problem with spell check. When you send an email, it says "spell check has been haulted, do you want to send anyway" and if I ask it to spell check, it says an error has occured in spell check. All my spelling options are correctly set, so I don't know what the problem is. Kimberly McNabb <knmcnabb@hotmail.com> wrote: > I am using Outlook Express 6 on Windows XP, and I have a > problem with spell check. Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Outlook Hanging when spell check
when ever i use the spell check when sending an email the PC hangs and i have to reboot. I could just switch off spell check but i like using it. I have tried reinstalling office, also tried using office eraser then reinstalling just outlook, nothing seams to work. Any Ideas? ...

Compare each value in a range to each value in another range
I am looking for a macro that will return a comparison of each cell in a range to each cell in another range. example. the first range would have vales of 1,2,3 and the second range would have values of 5,6,7 Thus the macro should return 9 possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 & 7, Can anybody help me with this?? Just hazarding some thoughts here .. Perhaps using formulas would suffice ? Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then Compare 1st range against 2nd range I...

Re: Paying credit card from checking account?
How can I omit certain expense transactions from my reports? Here's the problem- I pay my credit card account from my checkin account. So, if I execute a payment of $2000 towards the credit car account (which will debit my checking account), it shows as an expense transaction from my checking account (as it should); when I run report, it displays the $2000 expensed transaction (under the checkin account) but also includes all of my credit card expenses (presumabl totaling $2000) and therefore creates duplicates in my total spendin report. First, does my explanation make sense? Second, ...

Finding and naming duplicates
Hi All, not sure if anyone can help with this. I have a column called VNTR24 which has 10+ digit numbers - these are not unique and we want to be able to identify people with matching VNTR24 numbers to cluster them. In excel I've used the following formula: =COUNTIF(A:A,A2)>1 to return in the next column a TRUE or FALSE based on whether two records are matched on VNTR24 number. I need to do the same in Access 2007, returning either a "TRUE" or "FALSE" if the records are clustered/matched on this VNTR24 or not in an UPDATE query!? Can anyone h...

Find two different, unknown, names in list
I have a football spreadsheet with different picks listed in columns. For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc. How can I have both names displayed at the bottom of the column: Ala and Tex? The first name is easy, simply select the top pick in the column. The second pick is the "other" name. How can this be done? Thanks, Ed M. On Dec 29, 6:32=A0am, Ed_M <edmto...@gmail.com> wrote: > I have a football spreadsheet with different picks listed in columns. > For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc. > How...

Value (Y) axis crosses between categories and Secondary Value (Y) Axis
Hi all - I have 7 series on my chart Series 1 is column chart - may be area chart eventually (2007 data) Series 2 is column chart 2008 data Series 3 is column chart 2007 avg Series 4 is a column chart 2008 avg Series 5 is a line chart (32,0 benchmark) Series 6 is a line chart (points to column chart for 2007 avg, I then add text to right to explain the column) Series 7 is a line chart (points to column chart for 2008 avg, I then add text to right to explain the column) I would like the column charts for 2007 and 2008 data to be flush to the origin (12 data points each year) I would like the ...

Paste Special, Values, Transpose
Is there a way in Access 2007 to use the paste special features available in Excel? I have data in Excel that I will be copying and pasting into a form in Access. Importing would be tricky because not all tickets on the spreadsheet need to be copied. The issue is I receive the spreadsheet from another workgroup and they've merged cells and have the data going down one column, 19 rows. I can copy, paste special within Excel to get the data formatted right, then copy it into Access. I'm hoping there's an easier way. Any suggestions? Thanks! Jim Jim - ...

Clusted stacked column chart
I'm posting this (after banging my head against a brick wall for several hours) in case anyone else should run into the following, and I would be very interested to know if anyone knows of potential reasons for the problem. I made use of Jon Peltier's technique for creating a clustered-stacked column chart (http://peltiertech.com/WordPress/clustered-stacked- column-charts/). This worked fine the first time I used it. However, thereafter at the step: "The month names are not centered correctly under the clustered columns. Format the scale of the secondary category axis (B throug...

Inventory Reconcile SQL program code
I have been informed by Microsoft that the IV Reconcile procedure uses Dexterity to perform the calculations and inventory quantity updates -- and not SQL stored procedures. We have a large implementation of Great Plains with 650,000+ Inventory titles and use Mfg. The IV reconcile process is taking multiple days to run, and is cumbersome because it locks users out of Sales Trx entry/POP and IV. Has anyone made an attempt to write the SQL scripts necessary to update the IV tables for allocated quantities, qty on backorder, qty on order, etc? btw, we also have Multi-bin installed. ...

Find and replace #3
I have a table with 6 columns, A to F. Column A contains unique item numbers. Column B contains descriptions of those items. Columns C-F contain a random listing of the numbers from column A. I want to replace the random listings of items in C-F with the descriptions of the items from column B. There are 1300 items. Can I do this without running 1300 Find and Replaces? I would be happy with four new columns added that have the descriptions for the random items in C-F in the same order (not exactly a replace). Thanks! Al -- ovid962 -----------------------------------------------------...