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 Replies
ADVERTISEMENT
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 1 Replies
View Related
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
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
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
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
Apr 27, 2005
Hi
I'm have created a video database with a genre field. Is there a way that when i click a button a list is displayed of all the records which are the same genre?
Thanks
Aden
View 1 Replies
View Related
Jul 31, 2007
How would one go about comparing 2 tables and finding those records that are not common to both tables? For instance, I have a table that I suspect some records have been accidently deleted by the user. I would like to compare that table to an older version of the table, and list only those records that exist only in the older version of the table.
Thanks.
View 2 Replies
View Related
Mar 23, 2006
Feeling stupid today...how can I query a table to find the last five unique records?
Table1:
PrimaryKey
Field1
Field1 may contain duplicates. I need the five latest entered unique records in Field1, and am struggling to write a query for this.
Many thanks to anyone who can help out.
[Still using Access 97]
View 1 Replies
View Related
May 5, 2006
Iv'e looked through several date functions but cant figure out which one to use or how. I want to produce all records for a period of 1 year, for 1 year ago. i.e if I run the query during any month of the 'current year' (obvously) if its after August it will show me the previous year of August to following July. My Start date is always August 1st of any year through to the following July 31st. It is relatively straight forward (I think)if it's from January to December because year part is the same but this becomes more complicated when you cover two different Year parts. I dont want to have to use a parameter Query of 'start date' and 'end date' I want to be able to run the query based on the system date now(). Any help would be appreciated thanks.
View 7 Replies
View Related
Oct 5, 2006
Hi, I have a table contains 5 fields are shown as below, how I can write a query to find out the duplicate of the records? I have created a couple of queries by using Find Duplicates Query Wizard to do it. But the results are not accurate enough.
Field 1: ID
Field 2: Trading Name
Field 3: Location No
Field 4: Location Street
Field 5: Location Suburb
IDTrading Name Location NoLocation StreetLocation Suburb
41350Arabica Café Grey St
263Arabica Cafe` 1GREY STREET
41294Arashi Japanese Resturant41Courtenay PL
150Arashi Kushi Yaki Bar41COURTENARY PLACE
From the data sample, I could like to find out the duplicated records. One of the queries is shown as below. But they cannot pick up these records. Any better way to find the duplicated records? Please help.
SELECT DISTINCTROW [C&B-will].[trading name], [C&B-will].[location street], [C&B-will].Field1, [C&B-will].[location no]
FROM [C&B-will]
WHERE ((([C&B-will].[trading name]) In (SELECT [trading name] FROM [C&B-will] As Tmp GROUP BY [trading name],[location street] HAVING Count(*)>1 And [location street] = [C&B-will].[location street])))
ORDER BY [C&B-will].[trading name], [C&B-will].[location street];
View 2 Replies
View Related
Jan 7, 2005
I want a report to tell me a percentage of records. i have a report that records "records that are past date". I want the report to tell me the percentage of total records that are past date.
I have a query that shows only past date records
and a query that shows all records
how do i do this?
View 1 Replies
View Related
Apr 22, 2014
How to find the first 2 max numbers from set of records.
View 1 Replies
View Related
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
Jan 18, 2008
Is there a way, aside from using auto number, to find a records postion. I am using a list and want to be able to double click an entry and have it open a form and go to that record. However I can't use auto number because I allow for the deleting of records, and once deleted the auto number no longer corrilates to the records position.
If anyone knows of a different sytem that renumbers after deleting a record that would be great if you could share it with me. Or if there is a way to open a form and use goto record but instead of using record postion use where statement.
If anyone has any other ideas on this please share. Thanks.
View 6 Replies
View Related
Mar 14, 2006
Hi all,
I thought this would be relatively simple task to complete but its proving tricky.
I have two tables with the same data: E1 and E2
E1 has two columns and is as follows
A 1
B 2
C 3
D 4
E2 is similar except for one record '5'
A 1
B 2
C 5
D 4
Now the task is to show those records that do not match...in this instance
E1.Field1 E1.Field2 E2.Field2
C 3 5
Now I've used the find unmatched wizard and this is the SQL:
SELECT E1.Field1, E1.Field2, E2.Field2
FROM E1 LEFT JOIN E2
ON E1.Field2 = E2.Field2
WHERE (((E2.Field2) Is Null));
this is giving me partially the right answer as in
E1.Field1 E1.Field2 E2.Field2
C 3
Anyone else think of some other way?
View 2 Replies
View Related
May 24, 2006
Hi hope you can help me out,
I have a query which combines several linked tables, the query has about 10 columns, I need to show only the records which have one or more empty fields.
Some records may only have one missing field others may have several.
Any ideas?
Many thanks,
Ed
View 1 Replies
View Related
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
Aug 14, 2006
Hello,
I am trying to generate some query result on a form.
My DB consists of the following fields : tradeID and profit/loss.
How do i make a query to find the Maximum consecutive number of wins and losses in the database and display the results on a form?
Would really appreciate it if any of you have tried to sort and display results for finding consecutive number of events in any database!
Thanks a lot
View 2 Replies
View Related
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
Aug 3, 2013
I have the two tables in my access DB:
Property Information
Lease Information
I need to write a query which shows where a property does not have an associated lease, or where there is no current lease associated with it.
Property Info:
PropertyID
Address
Lease Info:
LeaseID
LeaseStartDate
LeaseEndDate
PropertyID (linked field to property table)
How to show expired / not current leases, but can't seem to show where there has never been a lease on a property.
View 4 Replies
View Related
Apr 2, 2014
I've got 2 tables, same structure, one [T-temp-Target] holds number of training units split by module a trainee needs to finish the course, the other [T-temp-Actual] holds what they've completed so far.
Both tables have structure
TRAINEEID
MODCODE
CountOfUnits
I'm trying to find the modules that they've not done yet so I can add up the units for them, only modules that have been started are recorded in the table of what they've done [T-temp-Actual], modules they haven't started yet aren't included in it. Here's the SQL
Code:
SELECT
[T-temp-Target].TRAINEEID,
[T-temp-Target].MODCODE,
[T-temp-Actual].MODCODE
FROM [T-temp-Actual] INNER JOIN [T-temp-Target] ON
([T-temp-Actual].TRAINEEID = [T-temp-Target].TRAINEEID) AND
([T-temp-Actual].MODCODE = [T-temp-Target].MODCODE)
WHERE ((([T-temp-Actual].MODCODE) Is Null));
View 2 Replies
View Related
Aug 23, 2013
I have a database for work where I have a table of meters and a table of Faults which has a list of all faulty meters at one time. When a fault is repaired, I have a macro which updates the Meter Status to Working, adds a Fault Closed date, appends the record to the Closed table and then deletes it from the Faults table.
The user runs this from a form by clicking the Closed Fault button which activates the macro. I've added Echo on and off to hide that the form is temporarily closed while the Append and Delete queries are run and then it is re-opened again.
My problem is that the Form always opens at the first record in the Faults table. I would like it to open to the record which would have been next after the one that has been moved to the Closed Faults table.
Below is the code I have been using to test the Copying Meter Reference, closing and opening of the form and finding the correct record:-
Function CopyTest()
On Error GoTo CopyTest_Err
Dim strMeterRef As String
DoCmd.SetWarnings False
DoCmd.GoToRecord , "", acNext
strMeterRef = Meter_Reference
[code]....
As you can see I am trying to go to the next record, copy the Meter_Reference by setting it to strMeterRef and then Find strMeterRef when the Faults form is re-opened.
I have a Macro embedded in a button which calls the above Function by using RunCode but nothing happens.
View 2 Replies
View Related
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
Sep 5, 2006
Well, I'm trying to enforce referential integrity on one of my relationships. But it seems that there are 5 records in the Subtable that don't have a corresponding record in Table, so it won't work. I tried copy/pasting from Subtable to Table, and it says 'unable to make changes because this would make duplicates in the primary key' to all values except 5. Which it asks me if I want them pasted. If I click 'yes' or 'no' nothing happens either way.
Well, anyway, I need code for a query that selects all records from Subtable.Field1 that don't exist in Table.Field1.
View 2 Replies
View Related
Jul 24, 2007
Hi, I have a cross-ref table (called MFC_CIBC_XREF) which links a bank account to a fund number and a general ledger number. It should be a unique relationship, wherein (the "=" means corresponds/links to)
Bank Account # "=" Fund #
For each bank account, there are sometimes multiple currencies being used, so each currency then links to a general ledger account, such that
Bank Account #.Currency = Fund #.General Ledger #
Sorry, if that's kinda cryptic, but here's an example:
Bank Account - ABCD123
Currencies operated in - CAD, USD
Fund # - F30
Based on the above, ABCD123 "=" F30, and including the general ledger numbers, ABCD123.CAD "=" F30.100, ABCD123.USD "=" F30.8121.
Basically, it's a way to keep track of not just the transactions for a particular bank account, but for the transactions in a particular currency.
Anyway, as I said, it's supposed to be a 1-to-1 relationship: each BankAccount.Currency should correspond to 1 and only 1 Fund.GeneralLedger. However, there are some entries in the table which have each BankAccount.Currency corresponding to multiple General Ledger numbers. Using my example above, ABCD123.CAD has two records in the table, one corresponding to F30.100, and another corresponding to F30.8101. This was probably because F30.8101 used to be linked to a different bank account, that got merged (ie, it might have been linked to ABCD124.CAD that then got merged into ABCD123.CAD).
Now I want to run a query on MFC_CIBC_XREF and find all the records where for each BankAccount.Currency, there is more than one Fund.GeneralLedger. I don't really know that much SQL, and even in Design View, I'm not sure of the statements to use. Any help is much appreciated.
View 2 Replies
View Related
