Find Matched Query

Sep 5, 2007

Hello all,
could annyone help me with this?
i have a database with two tables in it, table x and table y,, i would like to find those records in table x that are not in table y, both tables contain the same ACTID key,, i have used the find un-matched query,, i'm just looking for a finid matched query if one exists???
many thanks
;)

View Replies


ADVERTISEMENT

Find Matched Records

Jun 27, 2007

I have 2 tables. I want to find all the records between the two that match and then replace the contents of another field with a value such as "*" to signal the matched records.

If I were using dBase I would set a relationship between the two and repl all "fieldname" with "*" for seal = seal.

I have no idea where to start with this in Access. I was thinking it may be a query but I am not sure. It seems that if there is an Unmatched Query there may be a matched query. The replace seems to be more what I am trying to figure out how to do. I have searched on Replace but all I come up with is the basic Find/Replace.

Any help would be greatly appreciated.

Heather

View 5 Replies View Related

Update Matched Query

Oct 23, 2006

Hi Team I'm wanting to build an update query based on the below criteria:

Table 01 - Cost Centres

cost_centre
C0123AA
C0123AB...
C0987AA
C0987AB...

Table 02 - Conversions

store | new_store
0123 | 1234
0987 | 9876

Where all records in table 01 will be updated if they have a matching record in table 02. Eg the query should transform the C0123AA record to C1234AA. Any help would be appreciated as it's fair to say this has been doing my head in...

Thanks!

View 2 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Queries :: Ranking Matched Records In A Query

Aug 18, 2013

I have a question regarding ranked matches in a query. My data is set up as follows:

Item, Customer, Shipping Plant
65432, 104321, 221
65433, 104321, 221 etc.

For some items and customer combinations, there are multiple plants that can provide the same item item to a customer. I would like to create a ranked column that puts a 1,2,3 and so on for these occurrences. So if a customer get one item from only one plant, the rank would be 1, but if a customer gets the same item from two plants, the first would be rank 1 and second, rank 2 (order doesn't matter).

View 1 Replies View Related

How To Return Both Matched And Unmatched

Aug 3, 2006

Hello Forum --
I have written a query that uses one table and one query. As you would expect, it returns records where there is a match. I want to be able to return all the records that match the records in the table AS WELL as the records from the table that have NO match in order to come up with the sums that are zero

As a word of explanation, this DB is for budgetary purposes. I need to generate reports that include sums of actual expenses as well as $0.00 when no actual expenses have yet occured. But, I'm not at all clear how to write the underlying query so as to get both.

Any help the forum can provide would be fantastic.

Thanks,
Gordon

View 7 Replies View Related

Queries :: Identifying And Combining Rows Based On Matched Fields?

Jul 10, 2013

I have two tables. One table is a list of classes with the number of enrolled students:

Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15

And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):

Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2

Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:

Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19

View 7 Replies View Related

UNION Query MS Jet Database Engine Cannot Find The Input Table Or Query.

Oct 28, 2005

Hello All,

I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".

Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.

I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.

SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID

Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)

View 9 Replies View Related

Using Query To Find A Record

Jun 29, 2005

The strSql string should find the record that I have already saved in my admin table and then compare it with combusername, where is my mistake.


Private Sub Command7_Click()


'Dim db As Database
Dim strSql As String
'Set db = CurrentDb()


strSql = "Select adminpass From admin"


If ((Nz(txtpassword, "") = "") Or (Nz(combusername, "") = "")) Then

MsgBox "Please Enter A Valid Access", vbOKOnly, "Error"

ElseIf (txtpassword = "admin" And combusername = strSql) Then

DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"


ElseIf (txtpassword = "user" And combusername = "user") Then

DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"

Else

MsgBox "Wrong Password, Please Try Again", vbOKOnly, "Error"
combusername = ""
txtpassword = ""
combusername.SetFocus


End If

End Sub

View 3 Replies View Related

How Do I Run A Query To Find Duplicates??

Jun 6, 2007

hi all, im trying to make a query so that when someone updates a field in a form it searches a table (Companytable) which is a list of company names. If it exists i want it to then display a msgbox " already exists" sort of thing! trying to do this in the criteria field on the query. anyone have any ideas or useful resources, ive not done this for a while and cant remember much of IF THEN sort of stuff. mainly not sure if im barking up the right tree, i can search dependent on a set value but not sure how to do it dependant on whats been typed into a form..any pointers would be appreciated! thanks! James

View 2 Replies View Related

Query To Find Duplicates

Jun 15, 2007

Hi,

Our Contacts database has around 4000 individual contacts (and growing), which have been pulled together from four or five seperately maintained databases. As such there are bound to be duplicate records.

What would be a good way of querying the database to find duplicate entries (i.e. entries where the first name and surname are the same)? Short of putting everything in alphabetical order and manually scanning them to see if there are two names the same next to each other I don't know of a way to do it. Also, the manually-scanning method won't work if the names are spelled a little differently (Davies and Davis).

Any ideas?

View 1 Replies View Related

Totals Query To Find %

Aug 9, 2007

I have a DB that has lots of queries, I'm trying to find the percentage of trainees that are employed from the number of trainees in my database. For testing purposes, I have three trainees from NCN and two of those trainees are employed.
Now, i made a query to find the total number of NCN-trainees employed which has three tables and seven fields (Fname, Lname, Group[NCN],Employee Status[full-time, part-time,un-employed],employer number, and employer). three records in total
The other query I made is to find the total number NCN-Trainees. This query is based on the same three tables but only has 4 fields (Fname, Lname, group[NCN], and employee status) two records in total
The trouble I'm have is which field to put the totals under.

Any suggestions??

View 1 Replies View Related

Find Duplicates Query

Jan 4, 2008

I know I have duplicate records in a table and therefore I used the "Find duplicates Query Wizard" to identify the duplicates.

I am positive - there are duplicates and the query does not perform the way it should be.

Any suggestion will be appreciated.

TKS
JLA

View 9 Replies View Related

Find Out Everything Is Based On A Query

Jan 19, 2005

Ms Access 2003. Is there any way to find out what is using a certain query?
I need to know if qryPFP is used by other reports or forms before changing it. I don't want to mess up anything else somewhere else. Does this make sense? We have a LOT of forms, queries and reports and it's hard to plow through everything.

View 1 Replies View Related

Query To Find Last Date

Jan 16, 2015

I'm trying to query to find the latest date of a file that has multiple dates. I need the criteria to be the last date, and not just display the last date so I can find the transaction. I thought Dmax may work, but I don't know what my criteria would be. I put an example below. I'm looking for the last date, which would be 5/5/2014. I can't put in the date as the criteria since I am using this for a large amount of files, and the dates differ.

File #
setupdate
transaction

100001
1/1/2014
1

100001
3/5/2014
2

100001
5/5/2014
3

View 1 Replies View Related

Query To Find Rows With Only 5 Characters

Aug 30, 2006

How do a write a query to find those rows in a field that have only 5 characters?

View 2 Replies View Related

Find First Word In Field From Query

Feb 15, 2007

Can anyone help me in creating a field from a query which will pull the first word only from a specific field.



Thanks.

View 2 Replies View Related

Query To Find Missing Records

Aug 13, 2007

Hi.

i.e..............

Table A has records 1,2,3,4,5,6,7,8,9,10

Table B has records 1,2,3,4,5,6

How do I create a query that returns values 7,8,9,10 for Table B when I compare Table A and Table B?

What sort of query do I need? I tried the "Unmatched query" but this did not get the result I wanted.

Thanks for reading.....

Frank.

View 6 Replies View Related

Find Latest Record With Query

Aug 22, 2007

I'm fairly new to SQL and have a problem finding the latest record.

Lets say I have a development project that has many disbursal payments to be sent. I want to find the latest disbursal record created for that one project instead of having all of the disbursal records show on the query.

I'm trying to work out how to do this in a query but haven't got a clue where to start.

Please, if anyone can help I'd be very grateful!:D

View 2 Replies View Related

Find And Replace Query Or Macro

Dec 3, 2007

ne1 help me with a find and replace query or macro. What I'm trying to do is shorten the path to external PDF files after I inport them into field TxtPDFPath.

Example; after inport of files path reads
c:w2kdatapdffiles2005somepdf.pdf
c:w2kdatapdffiles2005januarysomeother.pdf
c:w2kdatapdffiles2006another.pdf

I would like to shorten all imported paths to
pdffileswhatever

Example;
pdffiles2005somepdf.pdf
pdffiles2005januarysomeother.pdf
pdffiles2006another.pdf
and so on

View 13 Replies View Related

Query To Find Ee's With Training Not Completed

Jan 28, 2008

I am working with a training database. I am a fresh newbie to access (more of an excel user).

I need to find employee's who have never completed a certain training (DPW Medication Training), which is mandatory.

I have the following tables:
Employee data TBL - Contains active ee's names
CompletedTrainings - Contains all trainings completed by individual
Events - name of all the trainings, including mandatory trainings

I have the following queries:
DPW Med Training - Lists all who have completed the training
DPW-Med-Last - Lists when each employee last had the training
Med_01 - Lists those who have had the training, but need it renewed

Any thoughts?

View 6 Replies View Related

Unable To Find Solution On Query

Apr 29, 2008

I have unique situation not sure how to achieve this in Query.

For understanding I have attached mdb has following tables
'PR_RPT'
'SYS_RPT'
'TIME_RPT'
Final_PR_RPT

'PR_RPT' and 'TIME_RPT' has unique ID called as 'PTUNID', My Final goal is Final_PR_RPT.

I have created a Query1, which Look into 'PR_RPT' and 'TIME_RPT' and does a match on 'PTUNID' and populates all the 'SUM HR' Field Values from 'TIME RPT' table to matching 'PTUNID' in Column 'SUM HRS' similar to Final_PR_RPT,

In Addition to that what I am trying to do is

1- Look for PTUNID from PR_RPT to TIME_RPT if that doesnot match then
2- take Unmatching 'PTUNID', Look for which 'Project LEAD' owns the 'system ID' From 'SYS_RPT' Table then
3- Populate the unmatched 'PTUNID' 'SUM HRs' from 'TIME_RPT' Table against that 'Project LEAD' which Owns that unmatched system and has same ProjectID in PR_RPT in new column as Wrong SUM HR and WRONG SYS ID

Final_PR_RPT, shows how the result should stored.

I tried using IIF Statement but I believe I am not doing it right.


Thanks
Niri

View 4 Replies View Related

Queries :: Need A Query To Find Records That Contains No

May 29, 2015

I have a table 45 fields. There is a field ID. I would like to get the records where fields contains "No". I would not like to create 45 queries for each field check. how can i generate a table which shows ID, Field Name that contains value "No".

View 4 Replies View Related

Query For Find And Replace Function?

May 16, 2012

How do you use a query for the find and replace function???

View 7 Replies View Related

SQL Query In VBA Won't Find Records After Certain Date?

Jul 11, 2013

I'm having a very strange problem with access. In vba I have an sql statement "select StudentID, CourseID from tblAttendance where AttendanceDate = #Whatever Date#".

Now this works for all dates (as far as I can see) before 01/07/2013 but after that it just doesn't find any records. I have manually added dates and checked and it didn't work.

How this could be possible? The date field is not in any relationship and it is Date/Time format. I don't even know how to go about troubleshooting this as it makes no sense to me.

It thinks these records have a date below 1/7/2013 and it can find dates after 1/12/2013.

View 8 Replies View Related

Find Last Query That Updated A Table?

Jun 19, 2011

I've inherited a rather large Access DB with many tables,forms, reports, and queries. I need to duplicate a process, but I'm having trouble finding the update qry that creates new records in a table.

I could write my own, but I am trying to create an exact duplicate so I'd really like to see the code that creates the new records for a specific table.

View 3 Replies View Related







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