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.
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 ;)
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).
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.
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...
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
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
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.
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.
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];
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?
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.
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
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.
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!
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".
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));
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.
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.
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.
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.
