HELP! 2 Tables, Want To Show Dissimilar Records Only

Oct 12, 2004

Boy, I thought this would be easy! It's simple to show common records, but I can't seem to be able to withdraw uncommon records. They are the ones I want. I can do it the long way by bouncing one record against all the records of the other with an Iif statement, then creating another query that groups all those results (thousands) and eliminates the erroneous data by using a min or max statement as a filter. This results in very, very long calculation times. There has to be an easier way. Please help, because I'm goining to have to do this several levels deep, and it's bad at the first level already. Thanks in advance.

View Replies


ADVERTISEMENT

Show Records Based On Duplicates Between 2 Tables

Aug 20, 2007

Ok.. I have been racking my brain for hours trying to figure out how to do this. Maybe someone here can help me figure this out.

I have 2 tables. "AR94" & "RPIN"
I need a query to look at the "Provider_PIN" field of both tables in order to find duplicates between that field in both separate tables at which point I need it to say If a duplicate provider_pin exists in the AR94 table & the RPIN table Then remove that provider_pin from the "RPIN" table.

The final result should be that the only records showing in the "RPIN" table would be those provider_pins' that were NOT found in both tables.

Starting Example (Before query):
AR94 Table:
-----------
1234
12345
123456
1234567
12345678

RPIN Table:
-----------
1234
4567
45678
12345678
456789

Results that should be achieved (after running query):
RPIN Table:
4567
45678
456789

Thereby deleting the 1234 & 12345678 values from the RPIN table leaving only records that were NOT found in both tables.

Please help me as I am trying to get this done for a project and can't figure it out..:confused: Thanks.

View 7 Replies View Related

Queries :: Joining Two Tables - Show All Records

May 31, 2013

I have a query joining two tables. TableInvoice in the query is Product number and Date purchased. TablePrices contains the same fields and contains prices (quarterly) over the past five years.

Joined by PONumber, I want the query to find the price charged for that date. Right now, if the Product number (TableInvoice) is not listed in TablePrices, it won't show. So, 100 Records might return only 80 if TablePrices does not have all of the Product numbers.

Is there a better way to return all 100 records in TableInvoice and show (Blank) price data if the item is not in TablePrices?

View 1 Replies View Related

Qry Results Show Duplicate Records, Records Are Not Dup In Table.

Nov 16, 2004

I have built a qry that initially shows the correct information. For example.

tblContent has 289 records with a Type = Class.

I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.

The SQL Statement is below

SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));

The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?

http://briefcase.yahoo.com/turnerbkgabrobins

Thank you in advance for your assistance.

View 1 Replies View Related

Show Last 5 Records

Apr 26, 2006

Hi,

I have a table with an autonumber incremental ID field using the format 0000, 0001, 0002 all the way to 1530.
I'm trying to create a query that returns the last 5 records in decending order, so...

1530
1529
1528
1527
1526

any ideas?
Thanks.

View 2 Replies View Related

Only Show 10 Records In A Subform

Nov 8, 2007

Hi all

I have a subform which loads the items from a table the form is non editable just for viewing the items, the problem is how can i
A. limit the subform to only show the first say 10 items in the subform
B, to have a command button on the form where the the subform is on (command button not in the form) and when the user clicks it it goes to the next set of records say from 11 to 21 and displays that in the form.

i tried doing record cound but somehow record count is not there when selecting, also would the code go in the subfrom on load or on the form load where the subform is based?

any help on this would be appreciated.

thanks.

View 7 Replies View Related

Show Only Records From Last 7 Days?

Sep 14, 2005

This is fairly simple i think although i cant do it right i have a field in a query named; "shiftdates" which uses the format: DD/MM/YYYY, how can i set the criteria for this to only show records from the past 7 days?

i think i need something like: date()-7 (this doesnt work though), anyone help me out?, :o

View 4 Replies View Related

Show Number Of Records

Jul 26, 2006

Hi everyone

My problem goes as followed:

As you can see in the picture there are 175 records currently showed. What I want to achieve is that this number is displayed in a Text Box.



Thanks in advance.

View 4 Replies View Related

How To Show Several Records In One Field

May 21, 2012

I have a table: Complaint

Within the table there is a field: CustomerID

There could be anywhere from 1 to 10 records with the same CustomerID.

I would like to show all the complaints for a specific CustomerID on a form in one field.

Right now I have the following Control Source Code:

=[Complaint.PartNumber] & ", ," & [Complaint.PNdescrip] & ", ," & [Complaint.Complaint]

This code will only display 1 record.

I would like the text box to look like this: (All from the same CustomerID number)

Part Number A PN Description A Complaint A
Part Number B PN Description B Complaint B

View 2 Replies View Related

Need Querry To Show Records That Are 3 Months Old

Aug 27, 2005

I have a querry designed to show loans that have a start date and an end date. I want my querry to show loans that have a start date, no end date, and that are 3 months old. Here is what I have so far but I can't figure out how to increment the 3 months on the start date:

SELECT Detail.CustomerID, Detail.TransactionID, Loan.LoanAmount, Loan.StartDate, Loan.EndDate, Loan.LoanLender
FROM Detail INNER JOIN Loan ON Detail.CustomerID = Loan.TransactionID
WHERE (((Loan.EndDate) Is Null))
WHERE ((( Loan.StartDate)) Is


Please help finish this line.

raydan

View 5 Replies View Related

Null Combo Box; Show All Records

Feb 11, 2006

Hello All,

I’m using a combo box for the criteria for a saved query. Is there any way to show all the records if the combo box value is either “0” or null?

Thanks,

View 4 Replies View Related

Show Records Only Where Date Is Within The Next Week?

May 26, 2006

Dear All, I have a field named: “reminder date” in a query and would like to show only records where the reminder date is within the next week whenever the query is run (ie if todays date is 26th may 2006 and I run the query now It would only show records that include the following dates 27,28,29,30,31, july and 1,2 June)

Ive tried the below bit of code but this shows to many records beyond one week.
>=DateAdd("d",+7,Date())

Anybody have any suggestions?

Regards

Jim

View 8 Replies View Related

Show Records With Unique Field

Jun 8, 2006

Hi,
I have two tables:
tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.

What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.

I've attached a screenshot and here is the sql:
SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. *
FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID
WHERE tblTransmittedDocs.DrawingNum="32-35554"

I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.

Thank you,
RCurtin

View 1 Replies View Related

Show Records By Week Per Month

Feb 20, 2007

Good day!

I'm planning to create a form that would show total number of sales transaction per week: If today's month is February, i would like users to see records like this: (Week should start on a Monday and ends on Saturday)

GroupNo______Week1_____Week2_____Week3____Week4___ _Week5___MTD
GroupA________12_________2__________10________11__ _____15_____50
GroupB_________8_________5___________7_________1__ _____21_____42

I can't find a sample code for this particular date criteria. :-(

Thanks in advance!


Sheila

View 6 Replies View Related

Query To Show Missing Records

Jan 24, 2008

I have a table for stock with two fields, KEY and DESCRIPTION; a table for manufacturers with two fields, KEY and NAME; and a third table which links to them both with a many-to-one join with three fields, KEY, STOCK-KEY and MFR-KEY. What I am trying to do is write a query that for a given manufacturer (entered via a parameter) shows a single line for all stock records that are NOT linked to it via the third table. I am sure it should be simple but all my attempts fail to exclude stock linked to the manufacturer if it is also linked to another manufacturer. Any ideas?

View 8 Replies View Related

Only Show Records Older Than 6 Months

Mar 3, 2008

Is there an expression to show records in a query that are older than 6 months?

At the moment I have to go into the database every month and change the criteria date ie:

<#01/09/2007#

It would be much easier if it could do this automatically with an expression.

Can anyone tell me if ones exists?

Thanks

View 9 Replies View Related

How Do I Only Show Current Records Image?

Oct 12, 2005

Hi

Just wondering if anyone could help me with my little problem :)

I am making a register (as in a class register of students) I have a table with the lessons on and one with students on. I have made a relationship between them etc.

I have a form where the parent allows you to choose which lesson you want to view, and the subform displays the students for that lesson. This all works fine.

My problem is that I also want a picture to be displayed for the student that is currently selected, I have managed to update the picture by using

Private Sub Form_Current()
[student_picture].Picture = [student_image]
End Sub

student_picture is the name of the image on the subform, the student_image is a row in my student database that contains the location of the image e.g. c:abc.jpg

and this works at updating the image that is show. The only problem is that the image is shown on all records/rows. Is there anyway of making it so that the image is only displayed on the current row?

Thank you

Neil

View 4 Replies View Related

Show Two Different Records From Same Record Source

Oct 12, 2006

Hi,

I am trying to create a textbox that will show a document number of the last record so when a user clicks add new document he/she knows what document number to enter in next. It must not involve the user looking a subform with the document numbers on it.

Thanks for your time,

Leon

View 2 Replies View Related

Show Only Certain Related Records In Combo Box

Oct 14, 2006

I have a feeling this is a common and probably easy question, but I couldn't find anything on it in the forums.

I have a main customer form with multiple subforms on it to keep track of my clients insurance policies and claims. I enter their contact data in the main form, and the policy data on one of the subforms. Then when they have a claim, I enter the policy information on the claim subform as well.

How could I have the combo boxes in the claims subform (which is storing data in a claims table) only show the policy numbers (stored in a policies table) assigned to that particular person? I would assume I need to somehow filter the policies query so it only shows records for that particular person, but I'm not sure how to do that on the fly.

View 6 Replies View Related

Query Wont Show All Records?

Jan 7, 2005

SELECT TimeFile.Component, Sum(TimeFile.Hours) AS SumOfHours
FROM TimeFile
WHERE (((TimeFile.Date) Between #12/15/2004# And #12/18/2004#) AND ((TimeFile.Project)="DSME") AND ((TimeFile.Area)="DSM-1 LIFT FRAME"))
GROUP BY TimeFile.Component;


I am having a problem trying to get all of the TimeFile.Components to show up. There are no hours in some of the TimeFile.Components, but isn't there a way to get them to all show up with a 0? Plz help.

View 2 Replies View Related

Forms :: Records Won't Show In Form

Jun 3, 2014

Once I enter records in my form and move to a new record, they won't show up in the form. For some reason there are a few records showing in the form but not all. Unsure what has happened or what I need to do to get the records showing.

If I open a the form called frmhome, I can enter information search on hours completed and even enter new towns. The table that the information gets entered into is called Crisis_support_workers. If you open this table, you will see a lot of information that has been entered. However, if you open the form that is meant to put info into that table, there is only 7 records and most of them are blank records. The form where info is entered is called Crisis_support_workers v3.

View 14 Replies View Related

Reports :: Show Differences Between Two Records

Aug 2, 2013

I'm currently working on a report to show the differences between two records (in this case, Documents). Each Document contains a number of paragraphs which are separate objects within my database. The purpose of this is to narrow down which paragraphs require reading (i.e. remove/highlight which are duplicates).

I've set up the report to show only the paragraphs from two selected documents (I don't think there is need for more than two, but how to select two or more documents for the query), and currently I have set the 'Hide duplicates' which kind of works but not the result I was hoping for - it only hides the related field and leaves the original in tact.

I am looking for a method that hides both duplicate records (not just the duplicated fields) if possible, failing that a method that will conditionally format the remaining duplicate or all duplicates.

Looks like the Hide Duplicates hasn't quite worked either - I have two fields which are clearly different and it has hidden the longer one. Likewise another later paragraph is not duplicated and it has hidden another which isn't duplicated. If it makes any difference the field is of type 'Memo'

View 7 Replies View Related

Queries :: Show Only Percentage Of Records

Nov 7, 2013

Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?

View 3 Replies View Related

Queries :: Duplicate Records Do Not Show

Feb 20, 2014

How can I have this query to show no duplicate records.

I would like to only see the first record only of each stock code.

View 2 Replies View Related

Forms :: Subform To Show Max 6 Records

Nov 8, 2013

Form invoice has a subform that can show max 6 records. Once more records are involved I want to show the last 6 including the new empty record. How to do that?

View 12 Replies View Related

Queries :: Only Show Records That Do Not Appear In Another Query

Jul 30, 2013

Currently I have a query, QRY_Test which contains a load of records under the fields "Gift_ID" and "Person_ID". I have another query, QRY_PersonList in which I would like to show all of the "Person_ID"s except the ones returned by QRY_Test.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved