Union All Query NOT Displaying Duplicate Records

Dec 10, 2007

I have two tables, and I made a union query (tbl1 UNION ALL SELECT ...tbl2). Problem is, I have two entries that are identical in both tbl1 and tbl2, but I want to include BOTH of them in the union query. Even though I used the ALL operator, only one set shows up!! I'm pulling out my hair trying to figure this out. Please help, and thanks so much in advance!:(

View Replies


ADVERTISEMENT

How To Sum Duplicate Records In Union Query

Oct 30, 2012

(Example of my data)

Order ID Customer ID Purchase Amount
1 17 $14.95
1 17 $7.35
2 21 $11.90
3 27 $64.80
3 27 $31.13

I have two tables with pull in data. What I'm looking for is a query that would combine the customers purchase and sum the purchase amount field. The tables are unioned as some customer data is in one table but not the other and joining them excludes some of this data. Ultimately I would like to see is

Order ID Customer ID Purchase Amount
1 17 $22.30
2 21 $11.90
3 27 $95.93

View 1 Replies View Related

Queries :: Combining Like Records In Union Query

Nov 25, 2013

How can I add the resulting records from a union query. The results of the union are something like this:

Quantity Item

2 Cats
3 Cats
1 Dog
4 Mice

What I need to display is

Quantity Item

5 Cats
1 Dog
4 Mice

How can I get the query to "do the math"?

View 3 Replies View Related

Queries :: Union Query - Multiple Records On One Report

May 7, 2014

I have a database that has over 20 tables in it and am using Access 2000. Unfortunately I cannot change the structure as it performs specific functions, so I am stuck with it.

I have created a Union Query from these tables yaking data from 5 fields using the Serial Number entered by the user.

SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[Workstation]
WHERE((([Workstation].UnitSerial)=[Enter Serial No.];
UNION
SELECT[LAPTOP].UnitPart,PropertyTag,UnitSerial,Date,Technician

[Code] ....

I use a bar code scanner to scan the serial number, and it goes through the tables and returns the results along with the other specified fields.

I would like to scan up to 16 or more different serial numbers and have it return the results. Perhaps scan the first 16 serial numbers, then run the query? Is that possible. The serial numbers are unique and will return a combination of laptops, printers, monitors, etc...

I have created a report from the above union query and it works perfectly with only one serial number entered.

View 14 Replies View Related

Union Query - Create Additional Field / Clear Records

Jul 20, 2006

The code pasted below creates a union query for a set of tables (J000171, J000174, J000178 etc) and stores the results of the query in a table called temp.

The first piece of code queries the ‘status’ field of a table rjobs for those records with a ‘status’ field of “Live”. Another field within this rjobs table, ‘JobID’, happens to be the name of a table where additional information relating to that job record is held eg. J000178 All of the tables selected in the query on rjobs are then included in the union query.

The second piece of code stores this information in a table called temp

I would like to be able to do 2 things with this;

1.add an additional field to the union query which holds the JobID field value from rjobs (or alternatively the table name from which the data originates eg J000178 etc as that is the same as the JobID file din rjobs)

2.create an option to clear the info in the temp table. Currently additional info is appended, so whenever the query is refreshed new data is simply added to old data. I would like to be able to clear that data where possible.

The union query is run from the on click of a command button on a simple form. Perhaps an additional button could be used to clear the records from the table temp.

Any ideas greatly appreciated.

Here is the existing code …

Option Compare Database
Option Explicit


Private Sub Command0_Click()
Dim db As Database
Dim rsRjobs As Recordset
Dim rsRapps As Recordset
Dim LengthofUnionSQL As Long
Dim sql As String
Dim UnionSQL As String
Set db = CurrentDb
Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot)
Do While Not rsRjobs.EOF
UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, from " & rsRjobs!jobID & " Union "
rsRjobs.MoveNext
Loop
'following two lines are to remove the trailing word Union from the string unionsql
LengthofUnionSQL = Len(UnionSQL)
UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7)
' Now variable Unionsql will hold the value something like
' Select ObjectID, SearchNo, DateSearched, Consultant from J000145
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147
MsgBox UnionSQL


Set db = CurrentDb
Dim rsUnionquery As Recordset
Dim rstemp As Recordset
Set rstemp = db.OpenRecordset("temp", dbOpenDynaset, dbSeeChanges)
Set rsUnionquery = db.OpenRecordset(UnionSQL)
Do While Not rsUnionquery.EOF
rstemp.AddNew
rstemp!ObjectID = rsUnionquery!ObjectID
rstemp!SearchNo = rsUnionquery!SearchNo
rstemp!DateSearched = rsUnionquery!DateSearched
rstemp!Consultant = rsUnionquery!Consultant
rstemp!jobID = rsUnionquery!jobID

rstemp.Update
rsUnionquery.MoveNext
Loop

End Sub

View 2 Replies View Related

Queries :: Calculating Total Number Of Records From Union Query

Jun 16, 2014

I am using the following UNION QUERY to total up equipment tested for a report.

SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]

[code]...

I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?

View 2 Replies View Related

Union Query Prompts For Parameters - Returns All Records With Parameter Entered!

May 17, 2006

Hello all,

I realise this issue is a common one and it is usually down to simple typos (I've looked at several similar posts) but none of the advice I've seen has solved my problem. I've designed the following Union Query:

SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 1]

UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 2]

UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 3];


It should be very simple and I've checked it over and over, but when I run the query an Enter Parameter dialog appears prompting me to enter Email.

If I just click OK I get all the records but with the email field blank.

Similarly if I type x@y.com it returns all records but with the email fields all containing x@y.com

Advice would be very much appreciated!! Thank you in advance.

Abi

View 2 Replies View Related

Queries :: UNION / UNION ALL Query Crashed Access

Oct 24, 2013

I'm having a problem with a UNION / UNION ALL query.It seems there is a application crash fault when running the query that MS are aware of and have issued a hot fix. Unfortunately it will take my IT dept some time to check and install the hot fix If they agree to do it at all.

Problem signature:
Problem Event Name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 12.0.6606.1000

[code]...

View 1 Replies View Related

Query Not Displaying All Records

Sep 5, 2006

Hi All

I have 28 records in my table - when I run the query (made by using the wizard) it only picks up 6 records.

I have no criteria in the query - just a straight query fulling all fields from the table.


Any suggestions?

View 7 Replies View Related

Records Not Displaying In Query

Nov 7, 2007

I am attempting to run a query with fields from 6 tables. These tables have been related, but just a simple ID to ID throughout all of the tables. When I select the fields I would like to be in query using the disign wizard it will allow me to create the query, however it will not display and data.

Originally, the query wizard would not allow me to insert all of the different tables fields in the query because "they were not related", so I went back and did the ID to ID relationships. Like I said this now allows me to create the query, but unfortunately no data appears.

Anyone have any suggestions please??

View 9 Replies View Related

Forms :: Multiple Records From Query Not Displaying On Form

Nov 18, 2013

I have a form that a user will use to search records. Upon selecting a record, there are three buttons that allows the user to look at more detailed information. This information will be on the many side of the relationship. So I will have multiple values. The called form is just a popup form that I use for several other instances, like displaying memo fields from other tables.

The problem I am having is that only one record shows up at a time on the called form. I have read through the postings and have changed the properties for Default View, Filter on Load, Data Entry, etc. Nothing seems to change the outcome. I have run the query in SQL view and have no issues. It returns the correct records and multiple rows when expecting. Why they don't show up on the form. There are no errors, just the first record from the many side of the relationship shows up in the form. I would like all records to show up, just like when I run the query.

Code:

SELECT Capability
FROM Process_Meetings_Capabilities, Process_Meetings
WHERE Process_Meetings_Capabilities.Meeting_ID = Process_Meetings.Meeting_ID
AND Process_Meetings_Capabilities.Meeting_ID = 2;

Button Code

Code:
Private Sub Capabilities_btn_Click()
strSQL = "SELECT Capability AS Results"
strSQL = strSQL & " FROM Process_Meetings_Capabilities, Process_Meetings"
strSQL = strSQL & " WHERE Process_Meetings_Capabilities.Meeting_ID = Process_Meetings.Meeting_ID"
strSQL = strSQL & " AND Process_Meetings_Capabilities.Meeting_ID = " & Me!Meeting_cmbo.Column(0)

[Code] .....

View 6 Replies View Related

Query For Duplicate Records

Jul 9, 2007

I am creating an ordering system which comprises of amongst others, these fields:

ID
OrderHdr
OrderLine

I want to ensure that if a user enters an ID, OrderHdr and OrderLine that this combination does not already exist elsewhere within the same table.

I have written a query like this:

COUNT OrderLineDetails.id, OrderLineDetails.orderno, OrderLineDetails.orderline
FROM OrderLineDetails
WHERE (((OrderLineDetails.id)=?) AND ((OrderLineDetails.orderno)=?) AND ((OrderLineDetails.orderline)<>?));

I will be replacing the "?" with the relevant form fields from the form, but how do I run the query and store the result in a variable and prevent the user from carrying on until they change any of the fields until there is no duplication.

View 3 Replies View Related

Help Filtering Duplicate Records From Query

Nov 28, 2007

Dear Experts,
I have created a database with queries that is working fine, however there are many records that have the same information that can not be removed easily, is there a method anyone knows of that can filter the records produced from a query to remove duplicates? i.e. 'select distinct' but for the whole query after the search parameters have been entered.

e.g. the search query is producing, after the refined search values are entered:
414353p - Fiat - 1.6 - MLS
414353p - Fiat - 1.6 - MLS
How can i then apply a filter to automatically reduce these duplicates to just one entry for the whole form?

Thanks

View 6 Replies View Related

Eliminate Duplicate Records In A Query

Jun 5, 2007

I know there a many posts about eliminating duplicate records because I did search, but I did not see anything that really answers my question.

I have a query in a local Access database that is using a Linked table from an SQL database managed by our corporate IT department. I have read access to only certain tables / fields in the SQL database. My query is based on 3 tables from the database and I have them linked together in my query.

When managers move from one site to another, they are given a different TeamID number. However, until they are replaced, the manager will have more than one TeamID assigned to him in the SQL database. Evidentally, the way our IT has the SQL database structured, when I query open items it duplicates the record for that manager because he has 2 TeamID numbers. So the only field that comes up differnt in the duplicate records is the manager's TeamID number.

I would like for my Select Query to ignore the duplicate record. I know I can use an Append Query to copy the data temporarily to a local table and set the proper fields as Primary Keys to do this. However, it would be nice if I did not have to go to all that trouble.

Is there a way to eliminate duplicate in my Select Query?

As always, I appreciate the help.

Jim

View 2 Replies View Related

Filtering Duplicate Records Query Problem

Jul 19, 2006

1 table contains the records I needs to be queried.
The result Im looking for is to view all the clients that have a different advisor assigned to a duplicated ssn. - not same advisors assigned to a duplicated ssn.
Example of desired outcome.
ssn name advisor plan
111223333 joe smith john doe b
111223333 joe smith jane sony c
111223333 joe smith unknown a

I am getting all data.
ssn name advisor plan
111223330 Jack Jack john doe b
111223330 Jack Jack john doe c
111223330 Jack Jack john doe a

Ive tried query with using 'first' for the ssn and the advisor still give me all data.
Please help me....
Thanks

View 3 Replies View Related

Queries :: Removing Duplicate Records In Query

Apr 24, 2014

Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.

A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left

There are other tables but these are the 3 that are used in the query.

View 8 Replies View Related

Queries :: Duplicate Results For Some Of Records In A Query

Dec 3, 2013

Why I am getting duplicate results for some of my records in a query. I have unique values set to Yes. I have also validated that the tables I am using don't have duplicate data. SQL is below.

SELECT DISTINCT [tbl_Rewards Activity Report - By Member Number].[Member Number],
[tbl_Rewards Activity Report - By Member Number].[Last Four],
[tbl_Rewards Activity Report - By Member Number].[BAL ID],
[tbl_Rewards Activity Report - By Member Number].[Primary Name],

[Code] .....

View 1 Replies View Related

Queries :: How To Remove Duplicate Records From A Query

Feb 11, 2014

I have this small database, I would like to have your support to setup this query "QryResults" in order to remove the duplicate records, I can't find a way to get shown only true records, for some reason I'm getting duplicate rows and fake values, the query is calculating operations from two different queries and a table.

View 3 Replies View Related

Duplicate Records In Query To Join Fields From Several Imported Txt Files

Jan 10, 2008

Hi all,

I have the following issue:

In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.

My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.

The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?

Example of current result

http://img178.imageshack.us/img178/5659/currentsu7.th.png (http://img178.imageshack.us/my.php?image=currentsu7.png)

to achieve result

http://img179.imageshack.us/img179/5100/toachievefk4.th.png (http://img179.imageshack.us/my.php?image=toachievefk4.png)

thank you for any comments

View 4 Replies View Related

Queries :: Insert Into Query - Duplicate Records In A Subform To New Form

Jun 4, 2013

I'm trying to duplicate the records in a subform to a new form but keep getting a too few parameters error.

Code:
strSql = "INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) " & _
"SELECT " & lngID & " As NewOrderID, ProductID, Quantity, DiscountPercentage " & _
"FROM [OrderDetailT] WHERE OrderNumber = " & Me.OrderNumber & ";"

The debug.print comes out as below:

INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) SELECT 49 As NewOrderID, ProductID, Quantity, DiscountPercentage FROM [OrderDetailT] WHERE OrderT!OrderNumber = 11;

View 4 Replies View Related

Forms :: Underlying Query In Subform Creating Duplicate Records On Form

Jun 18, 2014

I have an "Returns" master form that contains two subforms. The subforms contain items that we are returning back into our inventory. The underlying queries in each subform show only those records where the "Return Date" is null. The query(s) works fine, except that if there are 3 items that need to be returned there are 3 records that show in the master Returns form. I tried the Totals option in the query but the I need that Return Date on the subform. I only want one Returns master form to show the 3 records (not 3 records of the same master form).

View 5 Replies View Related

Queries :: Mass Duplicate Main Records And Related Subform Records

May 29, 2014

In my simple database (attached), I need to mass duplicate Tasks and their Notes.

I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.

The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.

I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?

Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.

View 5 Replies View Related

Qry Results Show Duplicate Records, Records Are Not Dup In Table.

Nov 16, 2004

I have built a qry that initially shows the correct information. For example.

tblContent has 289 records with a Type = Class.

I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.

The SQL Statement is below

SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));

The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?

http://briefcase.yahoo.com/turnerbkgabrobins

Thank you in advance for your assistance.

View 1 Replies View Related

Displaying Records

Feb 21, 2005

I have 500 Records in my Software Table, and I would like to display only those records that match my criteria that I place in my text box on my form called Software Name. So Once I have entered the software type in my text box, then I would like to display those records at the bottom of the screen as the selected records.... Any ideas??My table is called, Software, which contains Software Type, Software Name, Serial Id, Comments, Inventory Number, Version , Key and Company... I have a text box that is called txtSoftware, Which searches the databse and display the information in a list box called, lstResult, I would like to however, have the option, display in the list box but also display those records on the bottom of my form as it will look like in design view of my table. I have no idea how to ever start the idea ....

View 1 Replies View Related

Displaying Records

Nov 2, 2006

This is probably a very basic question, but I'm going to ask it anyway.

I have two tables linked together, in a one to many relationship (a supervisor table, and an employee table). Each supervisor can have many employees that report to them. I then set up a forum to enter new employees as they come into the corporation, and need to referance which supervisor they report to, I have a drop down box that I want to display SupervisorLastName, SupervisorFirstName (for example, Williams, George) but is only displaying the SupervisorID (for example 60).

How can I make the forum display Williams, George instead of 60?

Thanks in advance for your help.

View 2 Replies View Related

Queries :: Query Records With Both Duplicate Values And Different Values?

Jun 18, 2013

I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit

For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable

It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').

View 5 Replies View Related







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