One Field Returns Wrong Information

I have a query using multiple tables and multiple primay keys that is 
returning the wrong information for one field that is not part of a multiple 
primary key.  I have set relationships between the tables and joins are in 
place in the query.  Can someone please advise what might be causing the 
problem?
0
Utf
3/7/2008 1:44:01 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
663 Views

Similar Articles

[PageSpeed] 53

On Thu, 6 Mar 2008 17:44:01 -0800, ridgerunner
<ridgerunner@discussions.microsoft.com> wrote:

>I have a query using multiple tables and multiple primay keys that is 
>returning the wrong information for one field that is not part of a multiple 
>primary key.  I have set relationships between the tables and joins are in 
>place in the query.  Can someone please advise what might be causing the 
>problem?

Yes; some error in the query.

For help finding the error, please post the SQL view of the query and an
example of the error (the table field involved, its datatype and value in the
table, and the value returned by the query).
-- 
             John W. Vinson [MVP]'
0
John
3/7/2008 2:25:59 AM
Thank you John.  Below is a copy of the SQL view of of the query.  "Store 
Numbers" is a table containing the store numbers and their 'names' which 
refer to their location.  The "DM Checklist Table" contains only the store 
number in each record.  The store number is correct in the query but the 
store name is not.  The store name field data type is text.  The data that is 
returned for the store name, is the name in the next record instead of the 
name that should be pulled.

SELECT [DM Checklist Table].[Store Number], [Store Numbers].[Store Name], 
[DM Checklist Table].Date, [DM Checklist Table].[Point Value and Question], 
[DM Checklist Table].Score, [DM Category Table].[Control Sort], [DM Checklist 
Table].[DM Category], [Sales vs Goals YTD].[Store Sales vs Goal YTD:], [Sales 
vs Goals YTD].[Textile Sales vs Goal YTD:], [Sales vs Goals YTD].[Wares Sales 
vs Goal YTD:], [DM Category Comments Table].[Category Comments]
FROM ((([DM Checklist Table] LEFT JOIN [Sales vs Goals YTD] ON ([DM 
Checklist Table].[DM Category] = [Sales vs Goals YTD].[DM Category]) AND ([DM 
Checklist Table].Date = [Sales vs Goals YTD].Date) AND ([DM Checklist 
Table].[Store Number] = [Sales vs Goals YTD].[Store Number])) LEFT JOIN 
[Store Numbers] ON [DM Checklist Table].[Store Number] = [Store 
Numbers].[Store Number]) LEFT JOIN [DM Category Comments Table] ON ([DM 
Checklist Table].[Store Number] = [DM Category Comments Table].[Store 
Number]) AND ([DM Checklist Table].Date = [DM Category Comments Table].Date) 
AND ([DM Checklist Table].[DM Category] = [DM Category Comments Table].[DM 
Category])) LEFT JOIN [DM Category Table] ON [DM Checklist Table].[DM 
Category] = [DM Category Table].[DM Category]
ORDER BY [DM Checklist Table].[Store Number], [DM Checklist Table].Date, [DM 
Category Table].[Control Sort];


"John W. Vinson" wrote:

> On Thu, 6 Mar 2008 17:44:01 -0800, ridgerunner
> <ridgerunner@discussions.microsoft.com> wrote:
> 
> >I have a query using multiple tables and multiple primay keys that is 
> >returning the wrong information for one field that is not part of a multiple 
> >primary key.  I have set relationships between the tables and joins are in 
> >place in the query.  Can someone please advise what might be causing the 
> >problem?
> 
> Yes; some error in the query.
> 
> For help finding the error, please post the SQL view of the query and an
> example of the error (the table field involved, its datatype and value in the
> table, and the value returned by the query).
> -- 
>              John W. Vinson [MVP]'
> 
0
Utf
3/7/2008 1:51:02 PM
On Fri, 7 Mar 2008 05:51:02 -0800, ridgerunner
<ridgerunner@discussions.microsoft.com> wrote:

>Thank you John.  Below is a copy of the SQL view of of the query.  "Store 
>Numbers" is a table containing the store numbers and their 'names' which 
>refer to their location.  The "DM Checklist Table" contains only the store 
>number in each record.  The store number is correct in the query but the 
>store name is not.  The store name field data type is text.  The data that is 
>returned for the store name, is the name in the next record instead of the 
>name that should be pulled.

I wonder if you might have a corrupt index! Try Tools... Database Utilities...
Compact and Repair; if that doesn't help, break the relationship between DM
Checklist Table and Stores; remove any indexes on the Store number fields in
both tables; compact again; and recreate the relationship.
-- 
             John W. Vinson [MVP]
0
John
3/7/2008 10:56:03 PM
Thank you John.  I found the problem.  The 'Store Number' field in the DM 
Checklist Table was a "lookup field" into the Store Numbers Table and there 
was an error in the Select statement for the field.  After I found and fixed 
that, the correct store name is returned.

"John W. Vinson" wrote:

> On Fri, 7 Mar 2008 05:51:02 -0800, ridgerunner
> <ridgerunner@discussions.microsoft.com> wrote:
> 
> >Thank you John.  Below is a copy of the SQL view of of the query.  "Store 
> >Numbers" is a table containing the store numbers and their 'names' which 
> >refer to their location.  The "DM Checklist Table" contains only the store 
> >number in each record.  The store number is correct in the query but the 
> >store name is not.  The store name field data type is text.  The data that is 
> >returned for the store name, is the name in the next record instead of the 
> >name that should be pulled.
> 
> I wonder if you might have a corrupt index! Try Tools... Database Utilities...
> Compact and Repair; if that doesn't help, break the relationship between DM
> Checklist Table and Stores; remove any indexes on the Store number fields in
> both tables; compact again; and recreate the relationship.
> -- 
>              John W. Vinson [MVP]
> 
0
Utf
3/9/2008 2:20:01 AM
Reply:

Similar Artilces:

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Copying data from one chart to another
I have many graphs - all plotting on similar scales but using different data. Is there any way I can simply copy one set of data from one graph and paste it into another graph so that I can avoind going through all the hassle plotting each curve again? I want to have graphs showing different combinations of the same data and have hundreds of curves to plot so this could be a huge timesaver... Cheers. -- Alan_Partridge ------------------------------------------------------------------------ Alan_Partridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29295 V...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Simple Question-How to create more than one transaction on the Acc
If there is a question already posted let me know. The question is: I created a bank account information on the Account list icon and want to have more than (one)transactions listed and see each payee displayed separately on each page so i could have all the months posted with due dates and total listed. Thank you. In microsoft.public.money, a.j. wrote: >If there is a question already posted let me know. The question is: I created >a bank account information on the Account list icon and want to have more >than (one)transactions listed and see each payee displayed separately ...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Public folder issue, "messaging interface has returned an unknown error"
I'm running a SBS2003 domain and just added a public folder and a customized form for entry into the public folder. All of our clients (all XP Pro, Outlook 2003 with all updates) can get to that folder and use the form without any difficulty except for one. On one computer I get the classic "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." error message. I have tried a repair install, no change. I have tried making a new profile, no change. I have tried adding / removing his PST file (currently he's not even using a pst file...

Word 2007: word count wrong?
Hello, I have an issue with some .doc files when opening in Word 2007. In some cases the word count in the status bar is different of the word count of the "Word Count"- window (CTRL+SHIFT+G) or the word count in Word 2003 Example file: http://go.microsoft.com/fwlink/?LinkId=79595 Word 2007 (status bar) show 61019 words Word 2007 (Word count window) show 61010 words Word 2003 also show 61010 words This issue I have not with all documents, but only with some files and it seems that I have this problem only with .doc files but not with .docx files. (installed ve...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Change of field separator
When I open CSV files in Excel all data is put in one column. Can anyone tell me, where I change my set up, so I get another field separator? Please be specific, because my Excel is a Danish version, and sometimes I have a hard time following the English instructions. Thanks! Jane Hi you specify this in the Windows regional settings. Another idea: - rename your *.csv file to a *.txt file - now open it with excel. The Import wizard should appear and should allow you to specify a different delimiter "Janepige" wrote: > When I open CSV files in Excel all data is put in one co...

Retrieving Current User Information from JScript (OnChange)
I am adding functionality to one of my client's OnChange scripts to append the Current User Name into a memo field with date and action information. I scoured the web but can not find a way to properly retrieve the Current User information. Any information would be incredibly appreciated. ...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

Office 207 One Note
My One Note has been working fine for at least 2 years. Then a couple of days ago two pages of my most important information in my main workbook just disappeared. I'm not sure but none of the other pages look the same. Does anyone have any idea what might have happened? Have had no other computer problems recently. -- Rockymtnwoman -------- Original-Nachricht -------- > My One Note has been working fine for at least 2 years. Then a couple of days > ago two pages of my most important information in my main workbook just > disappeared. > I'm not sure b...

How do add another code to a current one?
I have this following code to make the rows changed based on the critea in column 16, and I need add A "Red, Yellow, Green" for status to only one column 30 at the end of the spreadsheet. How do I add another code? I keep getting an error.. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, clr As Long For Each c In Target.Cells If c.Column = 16 Then Select Case c.Value Case "Analyze": clr = RGB(204, 255, 255) Case "Build ": clr = RGB(204, 255, 255) Case ...

Annoying problem with lookup field
On a client, I have a MOSS 2007 portal with two lists which are not wroking as expected. One contains people and the other one contains a lookup field based on data from the first list. When someone which hasn't got administrative rights logs in and tries to see the properties of any element in the second list (on DispForm.aspx), they can't see the lookup field value, and if they try to edit the element, the dropdown control used to choose a person from the first list appears empty. Administrator don't have this problem. The users having the problem have collaboration rig...

UDF that returns a Hyperlink
Is it possible in Excel 2007 using VBA to create a UDF that returns a hyperlink? Ideally I want a hyperlink where the display name is different to the linked URL. Any ideas? TIA First, if you mean you want the tooltip to show a different address, then I don't think that it's possible. Second, if you meant you want to see something in the cell that isn't the address of the link, then... There's an =hyperlink() worksheet function that may do exactly what you want: =hyperlink("http://www.microsoft.com","Click me!") Third, If you wan...

Bulk sending of emails in Outlook being rejected if one address is wrong.
I am sending bulk emails in BCC and they are all being returned to me because one or more of the email addresses is incorrect. Is there a way to setup Outlook so that the emails go to the correct addresses and the incorrect addresses are bounced back to me? I use the Mail Merge feature and send out a newsletter with over 1000 e-mail addresses (who signed up for the newsletter so it is not spam). My service provider, Comcast, just put a limit of 10 at a time. It's real fun sending these out monthly!! >-----Original Message----- >I am sending bulk emails in BCC and they a...

How to change the FROM field in email campaign activity?
How to change the FROM field in email campaign activity? I would like to create an email campaign activity and change the TO field of the email to be abother name instead of mine I would also like to add attachments I can't find a way to do this Any help is very much appreciated Thank you Stella Hi Stella, There are many ways to send marketing emails from CRM: Quick Campaigns Quick Campaigns with mail merge using Microsoft Word E-mail Campaign Activities E-mail Campaign Activities with mail merge using Microsoft Word Direct e-mail using templates Send a workflow e-mail Integrate...

How to return the primary key from a combo box
I have a table containing two columns Column 1: Primary key Column 2: Name. I want to create an unbound combo box that shows the name but returns the PK. I have these properties set: Control source: <is empty> Row source type: table/query Bound column: 1 Row source: <I have a select that returns the desired records> In cboClient_AfterUpdate() I look at debug.print cboClient.Value and I see the name. I need the PK so I can construct the query for the next combo box. How do I get the PK Thanks for the help -- Message posted via http://www.accessmons...

pvt table field settings!
hi! i am receiving data every day from 10 different places as under.! for example: DATE 11.01.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.01.2009 PLACE "B" TOTAL NUMBER OF RECEIPTS 50 TOTAL OF RECEIPTS AMOUNT 1500 DATE 11.02.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 15 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.03.2009 PLACE "C" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 -likewise i'm receiving daily data from all the 10 places..! what i want is a pivot table report on a...

one company doesn't show $ amounts in payables for one user
I have a user who has access to multiple companies. The access for all companies is the same. In one company when she goes to Inquiry>purchasing>trx by vendor, the display shows $0 for all transactions. Other companies, she sees the correct $ amount. There are no modified windows and again her security is the same for all companies. Can anyone tell me why this is happening? TIA I am having this exact problem and would appreciate a fix. I have to change users to have the payables inquiry amounts show. I only have this problem in one company and I am the only user in our of...

how to convert a CString variable to unsigned short array one?
Hi, In my unicode VC6 app, how to convert a CString variable to unsigned short array? ....... CString strRst; unsigned short strOleChars[100]; strOleChars = strRst; //??? Thank you. Is ConvertStringToBSTR() what you are looking for? AliR. "David" <David_Wang_Xian@hotmail.com> wrote in message news:e6gbA3avFHA.720@TK2MSFTNGP15.phx.gbl... > Hi, > In my unicode VC6 app, how to convert a CString variable to unsigned short > array? > ...... > CString strRst; > unsigned short strOleChars[100]; > strOleChars = strRst; //??? > > Thank you. > > ...

Export PDF information into Microsoft Excel
What do you do with your data that's stuck in your PDF forms? FDF Decoder from Spotted Dingo Inc., gives you the ability to quickly turn your useless PDF information into a data mine of valuable information. FDF Decoder is a desktop application which creates csv files from your pdf interactive forms (acroforms) in individual or batch file mode. You then can easily import your information into Microsoft Excel to create intelligent spread sheets which can then be entered into databases and aid you in your statistical analysis. FDF Decoder works with the PDF's exported Form Data (FDF...