simple query not working

Hi:
Trying to set up a simple query, and can't figure out why its not
working. Hope someone can help me think a little more clearly.
Trying to link two tables:
1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
Join is 'SvcCode'
Query design is to show ClientID, DateofSvc, SvcCode, and
SvcDescription.
SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
tblCodes.SvcCode, tblCodes.Description
FROM tblSERVICES INNER JOIN tblCodes ON tblSERVICES.SvcCode =
tblCodes.SvcCode
WHERE (((SERVICES.[ClientID#])=964));
For some reason that I can't figure out, this query only gives ONE
result, even though it should show three services for this particular
client.
Suggestions for correction are appreciated!
Richard
0
richaluft
2/16/2008 4:42:16 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
629 Views

Similar Articles

[PageSpeed] 16

Temporarily remove tblCodes from the query.
Does it show 3 rows for client 964 now?
The 2 rows that did not show before -- is the SvcCode blank?

If so, you need to use an outer join in your query. See:
    The Query Lost My Records! (Nulls)
at:
    http://allenbrowne.com/casu-02.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<richaluft@cs.com> wrote in message
news:cfcb81e8-a996-4537-94d9-ec47aaa863a7@62g2000hsn.googlegroups.com...
>
> Trying to set up a simple query, and can't figure out why its not
> working. Hope someone can help me think a little more clearly.
> Trying to link two tables:
> 1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
> 2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
> Join is 'SvcCode'
> Query design is to show ClientID, DateofSvc, SvcCode, and
> SvcDescription.
> SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
> tblCodes.SvcCode, tblCodes.Description
> FROM tblSERVICES INNER JOIN tblCodes 
> ON tblSERVICES.SvcCode = tblCodes.SvcCode
> WHERE (((SERVICES.[ClientID#])=964));
> For some reason that I can't figure out, this query only gives ONE
> result, even though it should show three services for this particular
> client.
0
Allen
2/16/2008 4:53:35 AM
On Feb 15, 11:53 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
> Temporarily remove tblCodes from the query.
> Does it show 3 rows for client 964 now?
> The 2 rows that did not show before -- is the SvcCode blank?
>
> If so, you need to use an outer join in your query. See:
>     The Query Lost My Records! (Nulls)
> at:
>    http://allenbrowne.com/casu-02.html
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> <richal...@cs.com> wrote in message
>
> news:cfcb81e8-a996-4537-94d9-ec47aaa863a7@62g2000hsn.googlegroups.com...
>
>
>
> > Trying to set up a simple query, and can't figure out why its not
> > working. Hope someone can help me think a little more clearly.
> > Trying to link two tables:
> > 1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
> > 2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
> > Join is 'SvcCode'
> > Query design is to show ClientID, DateofSvc, SvcCode, and
> > SvcDescription.
> > SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
> > tblCodes.SvcCode, tblCodes.Description
> > FROM tblSERVICES INNER JOIN tblCodes
> > ON tblSERVICES.SvcCode = tblCodes.SvcCode
> > WHERE (((SERVICES.[ClientID#])=964));
> > For some reason that I can't figure out, this query only gives ONE
> > result, even though it should show three services for this particular
> > client.

Alan:
Since I'm using tblCodes.SvcCode, if I remove tblCodes from the query
I get NO Codes showing ( but I do show 3 events)
If I includetblServices.SvcCode, then of course I get all (3) items
returned.
I've tried outer joins before, but they return the same problem.
For example, join as follows:
"tblSERVICES LEFT JOIN tblCodes ON tblSERVICES.SvcCode =
tblCodes.SvcCode" yields three rows, only one of which shows the
SvcCode!
If I do a right join, then I only get a single row returned, complete
with SvcCode.

Any other suggestions?
0
richaluft
2/16/2008 1:54:20 PM
Your original post says that tblServices has a field named SvcCode.

I suggest you locate the 2 other rows that should be showing for client 964, 
and look at what is in SvcCode.

Either tblServices.SvcCode is blank in those 2 rows, or else it contains 
invalid codes (i.e. codes that don't match any of the entries on 
tblCodes.SvcCode.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<richaluft@cs.com> wrote in message
news:f3418bc0-88b1-439d-8eb3-bb928b1c2eae@v3g2000hsc.googlegroups.com...
> On Feb 15, 11:53 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
> wrote:
>> Temporarily remove tblCodes from the query.
>> Does it show 3 rows for client 964 now?
>> The 2 rows that did not show before -- is the SvcCode blank?
>>
>> If so, you need to use an outer join in your query. See:
>>     The Query Lost My Records! (Nulls)
>> at:
>>    http://allenbrowne.com/casu-02.html
>>
>> <richal...@cs.com> wrote in message
>>
>> news:cfcb81e8-a996-4537-94d9-ec47aaa863a7@62g2000hsn.googlegroups.com...
>>
>> > Trying to set up a simple query, and can't figure out why its not
>> > working. Hope someone can help me think a little more clearly.
>> > Trying to link two tables:
>> > 1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
>> > 2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
>> > Join is 'SvcCode'
>> > Query design is to show ClientID, DateofSvc, SvcCode, and
>> > SvcDescription.
>> > SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
>> > tblCodes.SvcCode, tblCodes.Description
>> > FROM tblSERVICES INNER JOIN tblCodes
>> > ON tblSERVICES.SvcCode = tblCodes.SvcCode
>> > WHERE (((SERVICES.[ClientID#])=964));
>> > For some reason that I can't figure out, this query only gives ONE
>> > result, even though it should show three services for this particular
>> > client.
>
> Alan:
> Since I'm using tblCodes.SvcCode, if I remove tblCodes from the query
> I get NO Codes showing ( but I do show 3 events)
> If I includetblServices.SvcCode, then of course I get all (3) items
> returned.
> I've tried outer joins before, but they return the same problem.
> For example, join as follows:
> "tblSERVICES LEFT JOIN tblCodes ON tblSERVICES.SvcCode =
> tblCodes.SvcCode" yields three rows, only one of which shows the
> SvcCode!
> If I do a right join, then I only get a single row returned, complete
> with SvcCode.
>
> Any other suggestions? 

0
Allen
2/16/2008 2:05:51 PM
On Feb 16, 9:05 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> Your original post says that tblServices has a field named SvcCode.
>
> I suggest you locate the 2 other rows that should be showing for client 964,
> and look at what is in SvcCode.
>
> Either tblServices.SvcCode is blank in those 2 rows, or else it contains
> invalid codes (i.e. codes that don't match any of the entries on
> tblCodes.SvcCode.)
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> <richal...@cs.com> wrote in message
>
> news:f3418bc0-88b1-439d-8eb3-bb928b1c2eae@v3g2000hsc.googlegroups.com...
>
> > On Feb 15, 11:53 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
> > wrote:
> >> Temporarily remove tblCodes from the query.
> >> Does it show 3 rows for client 964 now?
> >> The 2 rows that did not show before -- is the SvcCode blank?
>
> >> If so, you need to use an outer join in your query. See:
> >>     The Query Lost My Records! (Nulls)
> >> at:
> >>    http://allenbrowne.com/casu-02.html
>
> >> <richal...@cs.com> wrote in message
>
> >>news:cfcb81e8-a996-4537-94d9-ec47aaa863a7@62g2000hsn.googlegroups.com...
>
> >> > Trying to set up a simple query, and can't figure out why its not
> >> > working. Hope someone can help me think a little more clearly.
> >> > Trying to link two tables:
> >> > 1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
> >> > 2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
> >> > Join is 'SvcCode'
> >> > Query design is to show ClientID, DateofSvc, SvcCode, and
> >> > SvcDescription.
> >> > SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
> >> > tblCodes.SvcCode, tblCodes.Description
> >> > FROM tblSERVICES INNER JOIN tblCodes
> >> > ON tblSERVICES.SvcCode = tblCodes.SvcCode
> >> > WHERE (((SERVICES.[ClientID#])=964));
> >> > For some reason that I can't figure out, this query only gives ONE
> >> > result, even though it should show three services for this particular
> >> > client.
>
> > Alan:
> > Since I'm using tblCodes.SvcCode, if I remove tblCodes from the query
> > I get NO Codes showing ( but I do show 3 events)
> > If I includetblServices.SvcCode, then of course I get all (3) items
> > returned.
> > I've tried outer joins before, but they return the same problem.
> > For example, join as follows:
> > "tblSERVICES LEFT JOIN tblCodes ON tblSERVICES.SvcCode =
> > tblCodes.SvcCode" yields three rows, only one of which shows the
> > SvcCode!
> > If I do a right join, then I only get a single row returned, complete
> > with SvcCode.
>
> > Any other suggestions?

Allen:
Such a simple explanation!
I forgot to update TblCodes with some new coding entries about 2 yrs
ago, and never had reason to use  these codes.
Thanks again,
Richard
0
richaluft
2/16/2008 3:01:33 PM
Reply:

Similar Artilces:

Simple graph
I have a chart with zip codes and a population rate. ZIP RATE 80001 2.34 80002 1.23 80003 1.23 I want to make a chart that has the rate on the x axis and the number of times that rate occurs on the y axis. | | | * | | * |_________________________________ | | 1.23 2.34 Thank you. I will assume the ZIP and RATE stuff is in A1:B200 (labels in row 1) Label in G1 to read RATE (but leave it empty for now), label in H2 to read COUNT Make a list of rates in G2:G20 (say) In H2 =COUNTIF($B$2:$B$200,G2) Copy down the row Select H1:G20 and make a...

Simple Simple Simple
Just started Excel (again) and need to make an easy speadsheet fo calculating square inches. Column A: Height Column B: Legnth Column C: Total Tags per 16 x 24 Column D: Cost per tag Figures I know: each sheet cost me $9.98. each sheet is 16 x 24 inches What I want to do is enter the Height, enter the Length and have th total (sum) be entered into columns C and D. I can't believe I forgot how to enter formulas. Any help will b appreciated. Thank you, Crai -- Message posted from http://www.ExcelForum.com Hi Craig, One Way, might be better ways but this'll work: List the colou...

Simple help with implementing Outlook-like GUI
Hi All. I am an experienced unix programmer who sometimes has to do something in the windows world, and always has newbie-questions. Basically I want to write an application program that looks like outlook in that it has nice icons down the left hand side that choose the content of the main area in the right hand side. So I started the MFC App wizard, and got me a SDI program with a CLeftFrame (CTreeView) and a CMainFrame (CFrameWnd). Then I prepared the following snippet of code to draw the buttons: // Create a pushbutton CBitmapButton* pmyButton; pmyButton = new CB...

scroll bar doen't work smoothly under windows XP theme
I created an application which has horizontal and vertical scroll bar . Both of them wrok correctly under windows classic theme but if I change the theme to windows XP the horizontal scroll bar does'nt work correctly. The exact problem is the scroll works for the first time i scroll then if i again scroll it does'nt move at all and nothing works on the appllication .I need to open any other appllication and close it for the scroll to work again correctly. ...

Rules And alerts does work after crash
I am SysAdmin in a company and a collegue has a crash in his machine. It has 150 rules in the pst and he worked with POP3 account. Now I set up with IMAP account after the import of the rules none of the rules was play wright. Does anyone now How I can fix this issue without to delete it all and re -create????? Beacuse I allready did this and again the rules produce error " The message could no be found. We work with KERIO Mail Server. Any Ideas?????? Thanks -- Stavros http://forums.slipstick.com You'll have to recreate the rules since the original rules no l...

Using Symbols (&/-) in In Two Dependent Drop Down Boxes. Can't get the VLOOKUP to work!!!
Hey all, I have a category list of products and then a list of brand names for each product. So, I set up a series of drop down boxes (using Data Validation) for the categories and then a second series dependent which category you pick in the first, for the brand names. HERE IS MY PROBLEM: When I set up the category list I had to create them as One-Word-Names. I tried to set up a lookup table with the real category names (multiple words and symbols), but I can't seem to get the first drop down box to reference the lookup without then screwing up the Data Validation of the second (depende...

How to preserve conditional formatting on a web query table result
I have an external database that Excel queries and returns two columns of dates. I can set up a conditional format (in one colum) so that the dates in each row of the column change colour if the corresponding columns date is different. My problem is how to COPY and PASTE the conditional formatting across all dates in the one column (so that each cell looks at the date in the corresponding cell next to it)? Any ideas or suggestions? You can just use the format painter to copy and paste formats -- Regards, Peo Sjoblom "Simon L" <Simon L@discussions.microsoft.com>...

Simple hack to get $500 to your home. 06-05-10
Simple hack to get $500 to your home at http://uknews.tk Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque.please dont tell to anyone. ...

Simple Query Wizard hangs
I am using Access 2003 When I initiate the "Simple Query Wizard", I am presented with the first screen - "What fields do you want in your query" When I click on the pulldown list to select a table, I get the following message: "The expression On Get Focus you entered as the event property setting produced the following error: the text you entered isn't an item in the list * the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. * There may have been an error evaluating the function, event, or macro&q...

I Need a Simple Sales Software
Hello, I am looking for software to keep track of my prospects, customers, and the notes of the sales process. I spend about one hour a day on sales and marketing, so I prefer a simple and inexpensive software. Maximizer, Outlook, Act, and Goldmine seem made for the full-time professional salesperson. Please let me know if what I am looking for exists. Best Regards, T.I. ...

Microsoft Office Outlook has stopped working
Hello, I just purchased a new computer with Windows 7 Premium Home (64) installed. I am working on moving my data, etc. over from the XP computer to my new computer. I have installed Office 2003 on the new computer and all programs operate fine with exception to Outlook 2003. When I first started up Outlook 2003, it wanted to go through email setup, etc. I cancelled out of the setup because I was going to restore my .pst files from a backup that I made. I can not run my Outlook backup software (Outback6) to restore the files because it needs Outlook 2003 configured ...

PreviousSibling not working
I am trying to get the PreviousSibling function work. But I keep getting the error (Error accessing XML). Here is my code: --------------------------------------------------------------------------- private i as integer private strOutput as string = "" sub page_load (obj as object, e as eventargs) dim xmldoc as new XMLDocument() try xmldoc.Load(Server.MapPath("Geo.xml")) ShowTree(xmldoc.DocumentElement) catch ex as Exception strOutput = "Error accessing XML file" end try output.Text = strOutput end sub s...

Query of a subform
I'm looking to create a query that will pull subform data into it. The subform (four of them) have data that gets manually entered. All the boxes in the subform have dropdown boxes except one. This box is used to enter amounts. Will it be possible to pull this data? I can't seem to find it when I use the Query Wizard or Query Design. Thanks for any help. The data that is entered in the subform should be being stored in the table that is the source of the subform. Query that table. -- Milton Purdy ACCESS State of Arkansas "Gdesrosiers" wrote: >...

Outlook Client v 1.2 not working in offline mode
Hello, we have installed several CRM Outlook Clients, version 1.2, on the laptops of our salespeople. They are able to work online perfectly, and when going offline, synchronization seems to work, as well. However, after going offline and synchronizing, when they try to open any of the CRM folders, they get an "Access Denied" message (this does not happen in online mode!). I have adjusted the permissions on the UNC Snapshot share on the SQL server, and the Publication Access Lists on the SQL server, and still get the same errors (identical on all of the clients). Does anyone have a...

fill does not work for me what can I do
I have 2 columns I need to fill automatically and the help directions using the fill handle just do not work for me. I've tried repeatedly with no effect. I am using Excel 2007 in Windows 7 64 bit. I checked Excel options and the drag fill options are selected. But nothing I do seems to work. Is there some other way (using menu options) to fill a series of cells without using the drag fill tool? Here is what I need to fill: - One column starting with 1 and increasing consecutively. - One adjacent column that would start with 500 and increase consecutively by 1 through the column. ...

Parameterized query in form
My form exports a query. The query needs to select some or all "MICAP"s and some or all "Project"s. My form has an option box [optMICAPS] and another option box [optProjects]. When the option radio button is cleared, a combo box is made visible for selecting a MICAP or Project (cbMICAPID, etc.) The underlying query has a "where" clause like: WHERE MICAPID = IIf([Forms]![frmLaborHistoryExtract]![optMICAPS],"*",[Forms]![frmLaborHistoryExtract]![cbMICAPID]) Similarly for the Project selection. The MICAPID is numeric, as is the ProjectID. When th...

ODBC import with Excel 2007 with Query Wizard
Really strange problem with Excel 2007 and the Query Wizard. I've done this hundreds of times with Excel 2000 and 2003. The set-up is Windows XP, Office 2007 connecting via ODBC to a SQL 2000 server via a ODBC system DSN connection. After selecting the DSN connection from Other connections, the Query Wizard asks you to choose the tables and columns. All of the tables are shown but in the columns, some are missing. Certainly the primary key and text columns. If you cancel using the column choosers and switch to entering your query manually (e.g. select * from companies) it works fi...

Query-Based Distros for Sender Restriction
Can I use a query based distribution list as a Sender Restriction? i.e., if I have a query based distribution list which is "All Email Users", can I add that to the list of allowed senders on a different distribution list to effectively block external senders from sending mail to that other distribution list? The reason I ask is that I'm tryig to write a vbscript to go through a number of distribution lists and change this but recieve an error if I attempt to set it to a query based distribution list. A regular distribution list works fine. The odd thing is that if I go into t...

Make it more simple or intuitive to do simple things
I appreciate the fact that applications are becoming more versatile and able to do things that we hardly thought possible in the past but I feel that in this added complexity you are losing sight of the need to do simple things easily without resorting to trial and error or consulting "help" which often anyhow doesn't lead one straight to the solution! An example is how to produce a chart with a series of months i.e Jan Feb Mar etc appearing on the X axis. This is no doubt something that resulted naturally in the first versions of Excel charts or in a competitor's ea...

Curious Database Query Question
When creating a "New Database Query" from another Excel workbook, when you get to the "Select Workbook" dialog, there is a small checkbox labelled "Read Only". According to the help file, it has this to say: "To prohibit updates to this file, select the Read Only check box." I don't understand why this is an issue. In my understanding, database query is a one-way transfer of data from the external source to your excel workbook...if you edit the data in your workbook, that *doesn't* change the data back in the external source...right? Am I wr...

Simple public folder permission problem
I have a public task list folder. However even though I have given everyone "Author" priviledges and full control of the directory, users cannot update the tasks (although they can create new ones). The changes to tasks get reset to their original values. Where should I look to fix this problem? Brian How did you grant Author Access, via Outlook or ESM? What do you meanyou gave them full control of the directory? "Brian Taylor" <taylorb@newsgroups.nospam> wrote in message news:e2hwumGrEHA.1160@tk2msftngp13.phx.gbl... > I have a public task list folder. Ho...

how to configure outlook to work with yahoo
i am trying to set up my outlook to work with my yahoo mail account. I know nothing about how to do it. please help do you have a paid yahoo account? If not, you can't use outlook. see http://www.slipstick.com/addins/services/online.htm for possible solutions. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join One...

Update query
Are you aloud to qualify criteria’s in an UPDATE Query? I’m trying do a UPDATE Query to import information from one table to another but I’m having trouble getting it to work. If I run the query as followed it works find. UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID = "1893" WHERE (((tblIncomeDetail.LocationID)=6) AND ((tblIncomeDetail.Date)=#1/4/2008#)); But when I qualify my criteria’s and SET , it doesn’t UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID = [Tables]![tblIncomeSum]![IncomeID] WHERE (((tblIncomeDetail.LocationID)=[Tables]![tblIncomeSum...

Merge different queries
Hello all, I have two tables where I enter my stock trades as well as foreign exchange (FX) trades. The two tables contain different fields as the trading instruments require different entries. I then created two seperated queries in order to calculate the profit from each stock and each FX trade. I need two queries as the calculations are different, too. Now, I would like to merge the two queries in order to see my overall profit and losses no matter if the trade is a stock or FX trade. That is, in the final query I would like to see the trade ID (which is the primary key in eac...

Formating Cells to Calculate Hours of Work
Can someone help with a small problem that I'm having. I'm trying to format a group of cells so I can calculate hours of work , then multiply that by my hourly wage. I'd like it to read so that I can add a list of hours and then multiply it by the hour rate of pay. (I.E. 8.30 hr + 8.30 hr + 8.30 hr = 25.30 X $10.00 = $253.00 ) (@ @) ----------o00o-(_)-o00o---------- Use real time format like 8:30, just sum the total, assume =SUM(A1:A3)*24*10 with 8:30 in all cells format as General, number or currency will return 25...