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 Replies

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

Query Needs To Return Match And Unmatched Results

Jul 27, 2006

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.

Can this be done?

View 3 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

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

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 1 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

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

Modules & VBA :: Return Rows In Query From Variant Array Return From UDF

Sep 16, 2014

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.

View 3 Replies View Related

Need Help With Unmatched Query!

Jul 26, 2005

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?

View 2 Replies View Related

Unmatched Query

Sep 4, 2005

Hi,

I can create unmatch quiries using tables but i don't seem to be able to do it using quiries.

I want to look at the values in on Query1 and compare it with Query2 and return the results that don't have a match.

Is there somthing different i need to do when using quiries instead of tables.

Regards

Pete

View 2 Replies View Related

Unmatched Query

Oct 22, 2006

Hi!

Please find attached database.
I have created a query to find unmatched data.
Where I'm doing wrong?

S_log = 301 & T_log = 292 record.

Unmatched should be 9 record

I'm getting 301.?????

Can any one help me in this.

Regards,
Terry.:confused:

View 3 Replies View Related

Unmatched Q Problem

Jan 5, 2007

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?

My tbls are as such:

1st Table:EMPLOYEEtbl
EmployeeID
EmployeeName

2nd Table:CompleteClasstbl
EmployeeID
classID
DateTaken

3rd Table:CLASStbl
className
classID

View 1 Replies View Related

Unmatched Query

Feb 21, 2008

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:

UnMatch: IIf([Interest Rate]=[PrimePlusMargin],"Match","UnMatch")

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?

View 1 Replies View Related

Query Set Up For Unmatched Data

Sep 22, 2005

I have two field columns

For example:

Field 1 Field 2
-------- --------
Blue Blue
Red Red
Yellow Black

I need to write a select query that will output any rows where field 1 and field do not match.

How can I write this? does anyone have any suggestions?

Thanks

View 1 Replies View Related

Tricky Unmatched Query

Jul 15, 2005

Hello all:

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:

320
321
322
172
152

Is this possible?

Thanks so much in advance for helping!

View 3 Replies View Related

Unmatched Query Not Working

Sep 21, 2005

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

the tables are imported from Excel

View 2 Replies View Related

Unmatched Query With Condition

Jan 9, 2006

Hi there, I currently have the below tables:

tbl_stores
ID NAME
123 Store A
456 Store B

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

Any ideas? Thanks for your help with this one!

Cheers

Tony

View 2 Replies View Related

Delete Unmatched Results

Jan 12, 2006

Hello,

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));

Please help!

thanks,

Pablo:confused:

View 3 Replies View Related

Unmatched Records Urgen Help Req . Thanks

May 22, 2006

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.

Any suggestions please.

Thanks!!

View 1 Replies View Related

Unmatched Query Problem

Jan 29, 2008

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.

View 6 Replies View Related

Unmatched Query Without Wizard

Feb 5, 2008

How to I find unmatch records between 2 related tables without using the default unmatched query wizard in access?

View 5 Replies View Related

Updating A Unmatched Query

Apr 21, 2008

Hello everyone

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

Thanks guys

View 3 Replies View Related

Problem With Unmatched Query

Jun 23, 2005

Good morning,

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?

Thanks in advance for your help.

View 2 Replies View Related

Merging Tables With Unmatched Keys

Nov 12, 2007

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.

Q1
ID 1ID2Data1 Data2 Data3
10
20
31
40
50

Q2
ID1ID2Data1 Data2 Data3
20
40
61
81
Q3
31Data1 Data2 Data3
40
61
81
90
100

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.

View 4 Replies View Related

Find Unmatched/Unique Records

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