Records Disappear in Blank Query

I have a database with a few relationships in it. The patient and their 
dependants are in two separate tables.  I have been attempting to create 
report from these tables by using a query, however after much trial and error 
I have found that entire records are missing from the query.  If I run a 
query with all the information needed, but with no parameters these records 
are still missing.  Any ideas?
0
Utf
12/14/2009 9:14:01 PM
access.reports 4434 articles. 0 followers. Follow

3 Replies
956 Views

Similar Articles

[PageSpeed] 47

It appears to have something to do with my dependants table.  When I add the 
info from that table these particular items disappear. The ones that 
disappear do not have dependants, however other records without dependants 
appear on the query.  

"Caryn" wrote:

> I have a database with a few relationships in it. The patient and their 
> dependants are in two separate tables.  I have been attempting to create 
> report from these tables by using a query, however after much trial and error 
> I have found that entire records are missing from the query.  If I run a 
> query with all the information needed, but with no parameters these records 
> are still missing.  Any ideas?
0
Utf
12/14/2009 9:43:01 PM
You need a outer join between the two tables.
In the query builder right click on the joining line between the two tables.
Select the option that sais something like "Include ALL records from 
'Patient' and only those records from 'Depentant' where the joins are 
equal."
Do not specify any criteria on the Depentant table, or if you do you must 
add something like "criteria Or Is Null".

Adam



"Caryn" <Caryn@discussions.microsoft.com> wrote in message 
news:376CFB79-8A7C-4450-8BDD-74D6815A8300@microsoft.com...
> It appears to have something to do with my dependants table.  When I add 
> the
> info from that table these particular items disappear. The ones that
> disappear do not have dependants, however other records without dependants
> appear on the query.
>
> "Caryn" wrote:
>
>> I have a database with a few relationships in it. The patient and their
>> dependants are in two separate tables.  I have been attempting to create
>> report from these tables by using a query, however after much trial and 
>> error
>> I have found that entire records are missing from the query.  If I run a
>> query with all the information needed, but with no parameters these 
>> records
>> are still missing.  Any ideas? 


0
Adam
12/15/2009 1:24:16 PM
That fixed it! Thanks so much!

"Adam Simpson" wrote:

> You need a outer join between the two tables.
> In the query builder right click on the joining line between the two tables.
> Select the option that sais something like "Include ALL records from 
> 'Patient' and only those records from 'Depentant' where the joins are 
> equal."
> Do not specify any criteria on the Depentant table, or if you do you must 
> add something like "criteria Or Is Null".
> 
> Adam
> 
> 
> 
> "Caryn" <Caryn@discussions.microsoft.com> wrote in message 
> news:376CFB79-8A7C-4450-8BDD-74D6815A8300@microsoft.com...
> > It appears to have something to do with my dependants table.  When I add 
> > the
> > info from that table these particular items disappear. The ones that
> > disappear do not have dependants, however other records without dependants
> > appear on the query.
> >
> > "Caryn" wrote:
> >
> >> I have a database with a few relationships in it. The patient and their
> >> dependants are in two separate tables.  I have been attempting to create
> >> report from these tables by using a query, however after much trial and 
> >> error
> >> I have found that entire records are missing from the query.  If I run a
> >> query with all the information needed, but with no parameters these 
> >> records
> >> are still missing.  Any ideas? 
> 
> 
> .
> 
0
Utf
12/15/2009 5:06:01 PM
Reply:

Similar Artilces:

entering parameter criteria MS Query
Hope someone can help. I’m trying to extract data from a SQL Server 2005 into Excel, using a parameter criteria in MS Query. If I use criteria in a field with an integer data type it works fine. If I use a field with an NVARCHAR data type (which is the one I want) I get a “Syntax Error” message. There seems to be no way of entering criteria into this type of field which MS Query will accept. If I enter james it defaults to N’james‘, which works as syntax criteria in SQL Server Management Studio 2005, but not in MS Query. What am I doing wrong?! ...

Query on a text file..help needed
I am trying to query a large text file and I'm beginning to wonder if it is just impossible to do. I used the excel query wizard but because I am working with a fixed length file I am not coming up with the columns I need. The wizard picks up each row as a solid block of information. Is there a way to pick out certain information from the text file into the query? so if my text line reads: 0110172006johndoe234street excel could pick out 01 10/17/2006 John Doe 234 street I know someone ran a query on my file and put it into the most beautiful excel spread sheet but it just isn't ...

Including all tables into a query
Hi, I'm not sure whether this is the right group. If not please point me to the right one. I have a database containing a table for each month and each table contains information on the articles in the stock. I would now like to make a query which goes across the tables and draws the same number for every table. The tables all contain a product for each row and the properties of it in the columns (eg. type, number on stock, number out, number in, weight,...). All table come from the same source so they all have the same design except that a product might be in one table and not in an ot...

How do I add a record counter to my form
I would like to add my own record counter, like "Record # of #", to my form. How can I do this? Private Sub Form_Load() DoCmd.GoToRecord , , acNext DoCmd.GoToRecord , , acFirst End Sub Private Sub Form_Current() Me.Caption = "Record " & CurrentRecord & " Of " & RecordsetClone.RecordCount & " Records" End Sub This code places the info in the form's title area. To place it in a Label, substitute Me.LableName.Caption for Me.Caption. To place it in a TextBox, substitute Me.TextBoxName.Va...

Record recording
I need help in creating an automatic record form a file to an individual record. Example: You would make a work order for three different employees the first employees work order enter name, date, location, time, cost, and then this information would automatically be recorded to a permanent work sheet record specific to that employee. I have tried to manipulate VLOOKUP for this but to no avail. Any clue or help would be greatly appreciated. -- cdixon ------------------------------------------------------------------------ cdixon's Profile: http://www.excelforum.com/member.php?action=ge...

Help with sql which counts records
Could someone help me to extend this sql to include: 1. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 2. a count of txtmulti where the field is a YES/NO field and I want a count of where the answer is YES 3. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 4. a count of txtnbrparts where the field is a number field and I want a count of where the answer greater than 1 I think I need to extend the WHERE statement? SELECT tblhvdealspt1.txtablhybrid, Count(*) AS totals, tblhvdealspt...

Append Query w/ 'next number'
Hello Gurus, Scenario: I have an oracle database connected thru ODBC. I want to append data to Table- A that has following in it: Col1 (Integer), Col2 (text) -------------- --------- 10 txt1 12 txt2 9 txt3 I have a local table Table-B w/ the following Values: Col1 (text) ---------- txt5 txt6 txt7 The result I want in Table-A after appending from TAble-B is: Col1 (Integer), Col2 (text) -------------- --------- 10 txt1 12 txt2 9 txt3 13 txt5 14 txt6 15 txt7 Notice the Col1 values 13, 14 and 15. What I want is to get the max of Col1 in existing table-A (which ...

Blank Page Enternet Explorer
Blank Page Internet Explorer keeps popping up on my screen, Can someone tell me how to stop this, I am running Windows Vista ...

Selecting specific records
Is it possible to create a form with a "select" field (yes/no box), to select several items (say employees) and then output this information to a report with only the fields selected? -- Lori A. Pong How about multi-select list box? HTH -- -Larry- -- "Lori" <lpong@sternconstruction.com> wrote in message news:CAE5922D-92E5-4A01-83B2-6484024725C5@microsoft.com... > Is it possible to create a form with a "select" field (yes/no box), to select > several items (say employees) and then output this information to a report > with only the fields sele...

Multiple records in table to display single records
My table has 3 columns, A is account numbers, B is first name, C is last name. If an account has 2 owners, the account number is listed twice in C1, like this: A B C 123 Hank Jones 123 Ann Jones 231 Mike Smith 321 Jim John I want to query this table to display the names side by side if there is more than one owner, so that my data appears like this: A B C D E 123 Hal Jones Ann Jones 231 Tim Smith 321 Jim John Help please! Sorry, got the names mixed up! "Hurrikane4" wrote: > My table has 3 columns, A is account numb...

How do I trigger an event on opening a record
I have an Access 2003 form where I would like to take an action (e.g. via an event procedure or macro) whwnever the user opens a record for editing (e.g. via the arrowed record selector buttons). Effectively what I want is something like an On Enter or On Open control but relating to a whole record. It seems that such a control is only available for (e.g.) a text box, which relates to a single field in a record. I thought there would be something in the Detail section of the form as I thought that section related to whole records (?) but there isn't such a control there. Any idea...

Disappearing Email #4
O.k. I know that there has been a lot of post about this subject, but my case is somewhat different. Here is the setting: OS: Windows 2000 OL ver.: 2002 Environment: Exchange Server, Roaming profiles What happened: One user signs in to another PC, opens up his outlook, however, by default OL2002 is set to deliver mail to personal folder rather than to his exchange mailbox. Right before his eyes he sees his email disappear one by one. My Action plan: Go to the PC that he signed on where his email disappeared. Look at his profile folder which is C:\Documents and Settings\username\Local Settin...

Update Query and Too Many Rows
I have 700 records I want to update. When I view the the data sheet view of the query, it looks correct, but when I actually run the query, I get a message that I am about to update 800 rows. Why is it going to update 100 more rows than I specified? On Tue, 23 Feb 2010 14:34:05 -0800, mwilsonh <mwilsonh@discussions.microsoft.com> wrote: >I have 700 records I want to update. When I view the the data sheet view of >the query, it looks correct, but when I actually run the query, I get a >message that I am about to update 800 rows. Why is it going to update 100 ...

Object Source / Record Source
Hello I have created a database regarding the School System. I have created three forms (one form and two subforms) one main form is (Students) where i enter student's name, father's name, phone, address etc. now on the main Student form i want to place a subform. But the thing i want to know is: I want to make two buttons on my main form (Fees) and (Progress).. because i have two subforms (sbffees and sbfprogress) now the record source of subformbox should be controled with these two buttons. when i click the ( Fee) button the Source Object should be (sbf-fee) and when i c...

parameters and crosstab queries
I have the following crosstab query: TRANSFORM Nz(Sum(MSF07.[Share Total]),0) AS [SumOfShare Total] SELECT MSF07.Aircraft, Sum(MSF07.[Share Total]) AS [Total Of Share Total] FROM MSF07 GROUP BY MSF07.Aircraft PIVOT MSF07.Account; I want to be able to set a parameter to choose a month. Everytime I try to do this it gives me errors since the [month] column isnt part of the query. Thanks Grant You can use a parameter in a crosstab, as long as you declare it. Go to Parameters on the Query menu, and enter the parameter there. Don't use the name Month though. That's a reserved word in...

Outlook client 'View in CRM' does not open the record
My 'View in CRM' button is not opening the CRM record. Pop-up blocker is turned off. Any other ideas? Thank you very much! Suzy ...

Record Count 10-02-07
Experts, I'm trying to dynamically stored the record count from a subform into a field on my form. Now I have to look at the record count on the subform and manually entered the count into a field on form. There must be a better way. Please help! In the Control Source property of a text box on the main form: =[SubFormControlName].[Form].[recordset].[RecordCount] Where SubFormControlName is the name of the subform control, Not the name of the form that is the Source Object of the subform control. -- Dave Hargis, Microsoft Access MVP "Shiller" wrote: > Experts, >...

MCI recording silence, but Sound Recorder records OK.
Hello everybody, This code using MCI is working fine in many PCs, but one (in which only a long silence is recorded): // // OPEN // sMsg.Format("open new type waveaudio alias %s", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("status %s mode wait", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("set %s format tag PCM", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("set %s channels 1", m_sWavName); mciE...

Append Query problem
I have created an append query in vba code which is inside a For...Next loop. I create an SQL statement to find matching related records in one table, and then insert these matching records into a temporary table. I repeat for each value of unique primary key that is provided (in an array). The issue I have is that the append query only works for the first matching record, not the subsequent records. I have verified that correct SQL statements are generated during each iteration of the loop, but for some reason no inserts occur after the first one. The temp table is cleared at the beginning o...

Control the number of records shown in a report/subreport.
I've posted this question before and when I follow the advice, I keep getting a circular reference error. So I'm hoping that if I post some more info, I may get insight as to what is going wrong. I have a report (BartS1report) it uses a query (BartS1Report). This report as a subreport (BartS1Sub2) that uses the query (BartS1). The report lists the customer name, address, and systems serviced. The subreport lists the systems serviced. The reports are connected through the Master/Child links Customer ID and the ServiceAddress. When there are more than 20 systems per ...

New Record
I have a form/subform. I have an event that opens a popup. I select from a list on the popup that copies the value onto a txtbox on the Main form and the popup then automatically closes. In the same code I place the focus in the subform to begin adding data to a new record. The problem I 'm having is that when the focus is set in the sub, there are two lines. The top line, which is the one I'm entering data in, does not automatically put the autonumber in for the ID that links the tables, but the record below it does, which is one that I'm not using. When I fill in the...

What determines the order in which records are viewed
Hi all What determines the order in which records are viewed in a table If I import data or create a new table from a query regards, Garry ...

Outlook 2003 print a blank calendar?
Is there a way to print the calendar out of Outlook that DOESN'T show any appointments? One of my users just wants to print out a calendar. Ernie You can create additional calendars if you want to. Ernie wrote: > Is there a way to print the calendar out of Outlook that DOESN'T show any > appointments? One of my users just wants to print out a calendar. > > Ernie Create a new calendar folder to use for printing blank calendars or create a custom view that shows no appointments (ie, Category = sdfggft) then print. -- Diane Poremsky [MVP - Outlook]...

Database Deleting Record Problem
Just launched a new database. Using an Autonumber field to generate document numbers (sequence is not important - just need a unique identifier). There are a number of fields that are required fields based on an option group selection. Problem; If all required fields (they are required fields programmatically) are not completed and you attempt to exit the form, a message box appears asking " Do you want to save changes". If you say "Yes" the form closes, does not save, and the record is deleted - but users are not aware of this deletion - and they think the recor...

Recording last modification of records
I have found out how to record the date and time of the last modification to a record, which is great. Can I also record the user that made the changes? If so,how? Something like this can give you the network login name. http://www.mvps.org/access/api/api0008.htm -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "splitz" wrote: > I have found out how to record the date and time of the last modification to > a record, which is great. Can I also record the user that made the changes? > If so,how? ...