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.
I have a query pulling data from several tables. In the middle I've got join between a part number in a BOM table and a part number in an inventory table.
What I want is the query to return the inventory location when we have stock. When we have nothing in, then there would be no entry in the inventory table and it should return a blank or null in this and all the proceeding tables.
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 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).
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
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField] "kick the ball" "return the pass"
my query result should looks like this
[Orig] [new] "kick the ball" "kick" "kick the ball" "the" "kick the ball" "ball" "return the pass" "return" "return the pass" "the" "return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
I created an unmatched query with the wizard and it works fine - when I want to compare with only one field. Is there anyway possible for it to compare two fields? I need for it to look at field1 and field2 when checking for unmatched records. Any ideas?
I'd like to create a query that shows me what employees haven't had a certain training (i.e. Bob hasn't had heavy machinery training). I've played with the unmatched records wizard and it just seems to not work out no matter how I connect them. I think the only two tables I need be concerned with are the 1st and 2nd table. Can anybody give me an idea on how to work this?
I have a query in an Access 2003 database that list fields from two tables. I want a query based on this query to pull out records where the interest rate does not match the PrimePlusMargin. See code below:
SELECT [95_1324].ACCOUNT, [95_1324].[CLIENT'S NAME], [95_1324].[OPEN DATE], [AR PlanDefinition].[ARL Plan], [95_1324].[Interest Rate], [AR PlanDefinition].Margin, 6 AS Prime, [Prime]+[Margin] AS PrimePlusMargin FROM 95_1324 INNER JOIN [AR PlanDefinition] ON [95_1324].[ARL Plan] = [AR PlanDefinition].[ARL Plan] ORDER BY [95_1324].[OPEN DATE] DESC;
I think I figured it out but it doesnt work completely. I created another field called "No Match" and set the criteria to "No Match" See code:
When I run the code, the pop up parameter asking for PrimePlusMargin. I know I spelled it correctly. If I just say ok, it returns all records. PrimePlusMargin is an alias field. Does this matter?
In table 1 I have some document #s e.g. 320 321 322 170 171 172 151 152
In table 2 I have some document #s e.g. 170 171 151
I would like to create a query, that will look at the document #s starting with 17* and 15* in table 1 and look for it in table 2, if the document #s do not exist, I would like the query to spill out the following:
I have a a table that changes monthly, and each month I need to find the records from the old table that are not in the new table and visa versa. I made the entire row in the table the key. and wrote two find unmatched queries. However, the queries are not finding all the information that is no longer there or that has been added. Can someone please take a look and see what I am doing wrong? Any help appriciated!! Thanks
tbl_returns ID NAME RETURN 123 Store A 9999 123 Store A 8888 789 Store C 9999
I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B
My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.
SQL SELECT tbl_stores.store, tbl_stores.name FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2 GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store
I have an unmatched query that I would like to convert to a delete query. I would like to be able to delete the unmatched records from two tables using one query. I tried something like this (below), but I get an error message stating "could not delete from specified tables". What am I missing?
DELETE PUBLICIST and STORYPERSON STEPS Old table.* FROM [PUBLICIST and STORYPERSON STEPS Old table] LEFT JOIN [STEP TABLE] ON [PUBLICIST and STORYPERSON STEPS Old table].Pernr = [STEP TABLE].Pernr WHERE ((([STEP TABLE].Pernr) Is Null));
Hello all, I have 2 tables. I wish to fetch all the records which are not common in two tables.
I do not have Exp with the Unmatched query wizard. I want to sleect the records based on more than one criteria and wizards allows to select on only one field.
I have a table listing 147 Company training Policy# with each Position to which each policy applies. Not all Policy# applies to every position, i.e. Driver versus Office staff. There are 32 employees in 7 different positions.
I have a table of Employee Records with standard information including Employee ID and Position.
I created a select query from the Employee Record and the above training requirements that provides a data list of every employee to each pertinent training policy by inner joining Positions with three columns Policy# and Title from the Policy# table and Employee ID from the Employee Record table.
I am trying to create a query that will tell me by Policy# what Employee has no record of being initially trained in that particular Policy #. I have built a query but it doesn’t give me a complete listing of missing employees.
SELECT qryPolicyByEmployeeID.CWSPolicy, qryPolicyByEmployeeID.EmpID, qryPolicyByEmployeeID.Title FROM qryPolicyByEmployeeID LEFT JOIN TrainingLog ON qryPolicyByEmployeeID.EmpID = TrainingLog.EmpID WHERE (((TrainingLog.EmpID) Is Null)) ORDER BY qryPolicyByEmployeeID.CWSPolicy, qryPolicyByEmployeeID.EmpID;
My problem occurs at the “Where” statement. When inquiring on a specific Policy# and the EmpID is in the training log for any other Policy# that EmpID does not appear in the listing. I am trying to figure a way to get around this.
Has anyone a suggestion as to how I can make that happen? I am very flexible about changing table layouts if that will get me what I need.
This is my first post on this forum so I was wondering whether you guys could help me.
I import data from a server daliy. My DB has three tables containing print files;
Print: shows leaflets that have been printed Package: shows leaflets that have been packaged Dispatch: shows leaflets that have been dispatched
Since data can only be imported at the end of the business day, I needed to create a query which will show the files that have printed but havent been packaged. To do this I created an unmatched query and tested it, it works fine.
This unmatched query has now been placed in a sub form, I would like the users to add comments next to the rows in the queries. However since the unmatched query cannot be updated I was wondering if there is any way of around it
I have two tables with 'BatchNo' in common. However, there are some batches on Table1 where the sum of the 'Paid' amount does not equal the sum of the 'Paid' amount on Table2. I tried using the unmatched query wizard to find these records, but because it may find the batch number on Table2, it returns nothing.
How do I say look for the 'BatchNo' AND the 'Paid' amount and return the ones that are not equal?
Hi all, im hoping someone can help. This is the problem.
I have 3 tables that have tax payer information for 1 year broken up into 4 months, 1 table for each 4 month chunk. all 3 tables mostly have the same taxpayers, but each table has payers that are not in 1 or both of the other tables. each table has a 2 part primary key, the business number is the first part, and branch number for businesses with chains. the next 4 fields are each month of taxes paid. heres the design of each table.
is there an easy way to merge all of this data into 1 table easily. ive tried using make table or append queries, but it only addes the information where the IDs are in each table. this leaves out the ids that are in only 1 or 2 tables.
if you need me to clarify or explain anything else, please let me know. i can post screenies or the design, but obviously, not the data.
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
