Query Not Displaying All Records

I have a query that will not display records from fields that aren't required 
but are related to a parent table.  For example:

The "TitleID" field relates to the "Title" table's primary key.  This field 
is optional and if a user selects nothing than the default value is zero.  
However, with referential intregy deselected the form accepts the zero but 
when I run a query it will not report the record back to me.  When I go into 
my table I can see it but when I run the query I can't.  I've also manually 
gone into the table and changed the foreign ID to 1 and then run the query 
and it works.

Help!
0
Utf
3/4/2008 9:08:00 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1344 Views

Similar Articles

[PageSpeed] 39

Open the query in design view. Double click on the line between the two 
tables until a dialog box shows. Click on the second option. Run the query 
and see what happens. If it isn't right, try the third option.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Droogie" wrote:

> I have a query that will not display records from fields that aren't required 
> but are related to a parent table.  For example:
> 
> The "TitleID" field relates to the "Title" table's primary key.  This field 
> is optional and if a user selects nothing than the default value is zero.  
> However, with referential intregy deselected the form accepts the zero but 
> when I run a query it will not report the record back to me.  When I go into 
> my table I can see it but when I run the query I can't.  I've also manually 
> gone into the table and changed the foreign ID to 1 and then run the query 
> and it works.
> 
> Help!
0
Utf
3/4/2008 9:30:03 PM
Foreign keys should almost never have a default value.  Normally, I set 
all foreign keys to be null.

As noted else thread, you should be able to show your records from the 
Title table if you change the type of join.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Droogie wrote:
> I have a query that will not display records from fields that aren't required 
> but are related to a parent table.  For example:
> 
> The "TitleID" field relates to the "Title" table's primary key.  This field 
> is optional and if a user selects nothing than the default value is zero.  
> However, with referential intregy deselected the form accepts the zero but 
> when I run a query it will not report the record back to me.  When I go into 
> my table I can see it but when I run the query I can't.  I've also manually 
> gone into the table and changed the foreign ID to 1 and then run the query 
> and it works.
> 
> Help!
0
John
3/5/2008 12:07:07 AM
John,

What do you mean by "null"?  The fields that aren't required in my database 
have a default value of  "0".  Would that be considered null?  Please explain.



"John Spencer" wrote:

> Foreign keys should almost never have a default value.  Normally, I set 
> all foreign keys to be null.
> 
> As noted else thread, you should be able to show your records from the 
> Title table if you change the type of join.
> 
> '====================================================
>   John Spencer
>   Access MVP 2002-2005, 2007-2008
>   Center for Health Program Development and Management
>   University of Maryland Baltimore County
> '====================================================
> 
> 
> Droogie wrote:
> > I have a query that will not display records from fields that aren't required 
> > but are related to a parent table.  For example:
> > 
> > The "TitleID" field relates to the "Title" table's primary key.  This field 
> > is optional and if a user selects nothing than the default value is zero.  
> > However, with referential intregy deselected the form accepts the zero but 
> > when I run a query it will not report the record back to me.  When I go into 
> > my table I can see it but when I run the query I can't.  I've also manually 
> > gone into the table and changed the foreign ID to 1 and then run the query 
> > and it works.
> > 
> > Help!
> 
0
Utf
3/5/2008 3:31:03 PM
Null is no value at all.  (looks like the field is blank)

Zero is a value.

I was not saying that you should not have zero as a default.  You can have a 
default value for a field UNLESS the field is part of a relationship.  In 
that case, it is ALMOST always a bad idea to have a default value (at least 
in my opinion).


-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Droogie" <Droogie@discussions.microsoft.com> wrote in message 
news:5FAD232B-9F75-4AAB-B5EF-B8D14C5237AE@microsoft.com...
> John,
>
> What do you mean by "null"?  The fields that aren't required in my 
> database
> have a default value of  "0".  Would that be considered null?  Please 
> explain.
>
>
>
> "John Spencer" wrote:
>
>> Foreign keys should almost never have a default value.  Normally, I set
>> all foreign keys to be null.
>>
>> As noted else thread, you should be able to show your records from the
>> Title table if you change the type of join.
>>
>> '====================================================
>>   John Spencer
>>   Access MVP 2002-2005, 2007-2008
>>   Center for Health Program Development and Management
>>   University of Maryland Baltimore County
>> '====================================================
>>
>>
>> Droogie wrote:
>> > I have a query that will not display records from fields that aren't 
>> > required
>> > but are related to a parent table.  For example:
>> >
>> > The "TitleID" field relates to the "Title" table's primary key.  This 
>> > field
>> > is optional and if a user selects nothing than the default value is 
>> > zero.
>> > However, with referential intregy deselected the form accepts the zero 
>> > but
>> > when I run a query it will not report the record back to me.  When I go 
>> > into
>> > my table I can see it but when I run the query I can't.  I've also 
>> > manually
>> > gone into the table and changed the foreign ID to 1 and then run the 
>> > query
>> > and it works.
>> >
>> > Help!
>> 


0
John
3/5/2008 4:45:07 PM
Reply:

Similar Artilces:

Unable to display the folder.
Unable to display the folder. Microsoft OUtlook could not access the specified folder location. The requested information store could not be found in the active profile. This is the error I get. How can i fix it? Carl S. ...

In excel, column headings displaying as numbers
All of a sudden my column headings are displaying as numbers instead of the "A, B, C" that I am used to. How can I change them back? Tools>options>general>settings>r1c1 -- Don Guillett SalesAid Software donaldb@281.com "millguy" <millguy@discussions.microsoft.com> wrote in message news:7934D0CC-8DE9-480E-A75A-36DDA559254D@microsoft.com... > All of a sudden my column headings are displaying as numbers instead of the > "A, B, C" that I am used to. How can I change them back? Hi tools / options / general - untick R1C1 reference style ...

show select records in report
Shows every record: Private Sub Report_Open(Cancel As Integer) Dim stDocName As String Dim strInvoicing As String Dim strSites As String strInvoicing = "InvPercent" strSites = "Base List.Subform!SiteComplete" Shows one page of report but fields are blank.... Private Sub Report_Open(Cancel As Integer) Dim stDocName As String Dim strInvoicing As String Dim strSites As String strInvoicing = "InvPercent" strSites = "Base List.Subform!SiteComplete" stDocName = "Open Projects&...

Timer for Queries?
Does anyone know of a macro or add-on that I could use as a simple timer for queries? I don't like to use the CPU time in the Task Manager because of it's inaccuracy when not receiving full CPU usage. THanks for any suggestions -- cmungs Exactly what are you trying to accomplish? Are you trying to cause a query to run automatically every so often? If so, you will need to use the timer event on a form to do that. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "cmungs" <cmungs@discussions.microsoft.com> wrote in message news:88EC7019-045F-4EF...

Erratic results from query criteria
I am getting different results from running the same query with the same selections. One moment it is all behaving as expected, the next it has gone haywire. (I have done what appears to me to be EXACTLY what I have done in another database, where it works perfectly every single time.) In a query I have, amongst others, the following fields: Category SubCategory Company I want to be able to select any OR ALL of the relevant fields. I have the following criteria: Like "*"&[Type Category otherwise leave blank for ALL]&"*" Like "*"&a...

Update Query ?
Is there a way to 1) Assign events when adding in data thru an update query for example if you append a bunch of data into a table could you then do some kind of an update query to say if there is an initial event and no others tag this one Renewal 1 etc.. the only real data you have to go on would be Customer A and the total amt .. the event, renewal event and the delta would all need to be added in example: Say Customer A comes in with an initial deal then Customer A renews their deal So, the data would look like when you're done.: Year Custo...

design query match anywhere?
In the design query I want to match the letters "ABC" anywhere in the column 'description'. I could not get instr to work by using: InStr(description, "ABC")) > 0 How else can I match ABC (upper and lowercase) to anywhere in my 'description' column? Thanks! Are you saying you put that InStr bit as a criteria under your Description field in the query designer? Try putting Like "*ABC*" as the criteria instead. Alternatively, you could add a computed field to the query InStr([Description, "ABC") and then put >0 as the ...

Unexpected Error- Running Advance Find Query
I am getting a message that says "Unexpected Error An Error has occurred" when I am running an advanced find query on two custom entities. It seems to be when I have certain custom fields included in my view that the query errors out. Any help on this would be greatly appreciated. Thanks so much. Just wanted to add that I seem to get errors when there is a 0.00 amount in these fields... "TJ" wrote: > I am getting a message that says "Unexpected Error An Error has occurred" > when I am running an advanced find query on two custom entities. It se...

Move through records with self made buttons only
I'm currently busy with the following: I have made the navigation buttons on my forms invisible, and added my own buttons on the form instead. Future users (without any knowledge of access) can scroll between forms with these buttons. The problem I'm facing now: I can still navigate between records without using my buttons, using the scroll wheel on the mouse, as well as the tab-button on my keyboard. This can be distracting for future users of the form. My question: How do I make sure that navigating between records is ONLY possible using my self made buttons? Assumin...

pass through query
If a combobox is populated by a pass-through query, does it requery every time the form is closed and reopened? Thanks, Sam Yes. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Sam wrote: > If a combobox is populated by a pass-through query, does it requery every > time the form is closed and reopened? > Thanks, > Sam ...

Journal Activities Recorded in Contacts
When I am in a Contact, and pull up Activities/All Items, I am assuming these are being pulled from the journal? If so, why is it that the only folder being accessed for email messages is the Sent Folder? Is there a way to view all activities - Phone calls, Documents, and emails residing in the Inbox, Sent Folders and Personal Folders. Also, if I delete these emails from their 'home' folders, will they be retained in the journal/activities? If so, does this mean they are still on the Exchange Server? Thanks. Activities are lists of contacts in the Contacts field (Links c...

a count field in a query?
Hello, I have a query in which I would like to create a field which increments by 1 for each record selected, so if there are 10 records selected by the query, this column would show numbers 1 to 10. Ideally I would like to have some text in front of each number, say invoice1, invoice2 etc. From a previous posting I realise that this is frowned upon but it would be the simplest solution for my problem! How can I do this? Thanks Geoff On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote: >Hello, > >I have a query in which I would like to create a field which >...

Saving Username and Pass with Web Queries
I am pulling data from a website that requires you to provide a usernam and password everytime you visit the site. Excel has cachin capabilities, but everytime you close excel and try and open up th spreadsheet again, it can't login. What is the fix for this? Thanks, Hend -- Message posted from http://www.ExcelForum.com Bump! --- Message posted from http://www.ExcelForum.com/ ...

why does Word display my field codes upon opening?
I am using Office 2007, and version 11 of Reference Manager. When I open my document, both the Reference Manager fields and the page numbers display with the cryptic words in [brakets]. I must Select All and 'toggle field codes' every time. Can I have Word open with the fields already 'toggled'? Office Button>Word Options>Advanced then uncheck the "Show field codes instead of their values" in the Show document content section of the Word Options dialog. -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unle...

updating records in subform
Hi, I have my main form frm_bundlelog with a subform frm_bundledocs linked by the BundleNo and Bundle# fields. I have a button btnupdate on my form that when I click on it, it would update the AssignedToSub field on my subform with the value of the AssignedTo field that is on the main form. I have teh following code: Private Sub btnupdate_Click() On Error GoTo Err_btnupdate_Click Forms!frm_bundlelog!AssignedTo = Forms!frm_bundledocs!AssignedToSub Exit_btnupdate_Click: Exit Sub Err_btnupdate_Click: MsgBox Err.Description Resume Exit_btnupdate_Click End Sub This is...

Birthday query for many children in one record
I have a database for the church, it has one main flat table only. Each record for the parents have field for child1 name, child1birthday, child2 name, child2birthday, child3 name, child3birthday - up to 5 children. I am having difficulty trying to get a report to list the birthdays of the children like this parent name, child 1 name, child 1 birthday month etc. so you get something like this:- Parent John Smith, child Mary Smith January 3 Parent Fred Jones, child Bert Jones March 4 Parent John Smith, child Jane Smith March 6 Parent Harry Brown, child Jane Brown March 6 Parent Bert Taylor,...

Unresponsive Query Wizard
Hi there, I have an Excel 2003 user who is using a spreadsheet that pulls data from our SQL server. When he opens it and refreshes the data it is fine, it pulls the current data, but when he tries to edit the query, the Query Wizard does not appear, he can see it on the task bar, but when he switches focus using task manager everything is greyed out and nothing works. I have tested the file from my computer and it is fine, which suggests a problem with his machine or installation. Does anyone have any ideas? Cheers, Steve Dooza wrote: > Hi there, > I have an Excel 2003 user who...

Another simple query
Dear all, I have a table that contains first names and last names in different fields. Is there any way to write a query that will give as a result the first name and last name in the same field? I hope this makes sense. Thanks in advance! SELECT [last_name_field] & ", " & [first_name_field] AS whole_name FROM [your_name_table] Change the field and table names to whatever your actual fields and tables are. Cheers, Jason Lepack On Apr 23, 9:47 am, Homer <peter.a.r...@googlemail.com> wrote: > Dear all, > > I have a table that contains first names an...

Query query
I am trying to create a new field in which it shows the sum total of 10 fields (Charge Time, Charge Time 2, Charge Time 3, etc.); what is the easiest way to do this? -- Joe NewFieldName: [Charge Time] + [Charge Time 2] + [Charge Time 3] .... etc -- Good Luck BS"D "joet5402" wrote: > I am trying to create a new field in which it shows the sum total of 10 > fields (Charge Time, Charge Time 2, Charge Time 3, etc.); what is the easiest > way to do this? > -- > Joe I tried this, and it doesn't work. Is there something I'm forgetting? -- Joe ...

group by query trouble
I'm trying to run a query that counts the number of people with hemoglobin of type SS, type SC, and then all others. There are about 12 different kinds of types. Basically I want a count for SS, a count for SC, and then a count that totals all the others into one count. Hopefully that makes sense. I'm using group by for the SS and SC which works great, but I'm not sure how to tell it to add up the others. I'm not a big fan of nested IIf statements, but this should work after you put in the proper field and table names: SELECT IIf([Hemoglobin]="SC","SC&...

Can't display message
Over night outlook (xp) has started diplaying a message 'this html message contains a script which outlook cannot display. This may affect how the message appears.' when I try and view my Daily Dilbert e-mail. Can anyone give me any suggestion why this is as I can't seem to make it work. I have now solved the problem by doing a repair and re-setting to default settings. The error message doesn't come up anymore, which is quite bizzare as the default security settings are higher than what they were at when I couldn't get the email. "Vanguard" <rztqf6v02-NIX...

CRM 3.0: Can't display changes in "incidents" view.
Hi, I want to add columns to the incident view. So far, I customized the view in the parameter section, published the changes, but I'm still unable to view the new columns in the "incidents" view. I did an iisreset, even rebooted the server, without success. Any idea? Did I miss something? Thanks for any help. Jeff Which particular view? There are lots of "incidnet" views, perhaps you customised one and are looking at another. You don't need to publish view customisations (unless I'm completely mistaken..), certainly no need for an iisreset or reboot, ...

Excel 2007: Lines not displaying on Scatter Plots #2
I have equipment that spits out system variables every minute for weeks/months at a time. There are numerous gaps in the data when the system is not "sampling", from minutes to days at a time, though I maintain continuity of the Date/Time stamp that I use as the x-axis. So the data sets I use are enormous, maximizing the capabilities of Excel (i.e. 30,000 points per data series and typically no more than 6 data series per plot). The chart data references multiple worksheets, so a Scatter Plot is the only appropriate option. I have recently installed Office 2007, though som...

To Query or not to Query??
Hello all - I'm a newbe to Access but have made great progress. I'm using Acess 2003. The db is up and running prefectly. Now I need to tackle the reporting. I have seen posts about reports and queries and am a little confused. Do I need to create a query to run my reports off of? I have a tabbed subform in the main form. There are seven tabs breaking up all the services we provide. Like "Promo Items", "Leads", "Client Printed Material","Internal Print Material" and so on. The main form has the User, Branch and Marketing Rep. I need to...

Mail sent using MailMessage class displays "TO" and "CC" recepients in the message body when sent to large number of recepients
I'm using System.Web.Mail to send an email message from an ASP.NET web page. This is the main section of the C# code. //************* start code ***************************** string[] mailHost = (System.Configuration.ConfigurationSettings.AppSettings.GetValues("mailserver")); //Get data from page string toAddress = ((tbTo.Text==null)||(tbTo.Text.Equals(String.Empty)))?String.Empty:tbTo.Text.Trim(); string ccAddress = ((tbCC.Text==null)||(tbCC.Text.Equals(String.Empty)))?String.Empty:tbCC.Text.Trim(); string subjectLine = ((tbSubject.Text==null)||(tbSubject.Text.Equals(String.Em...