Queries :: Inserting With Duplicate Key Query

May 19, 2013

I have a load of data in an excel spread sheet that I'm trying to put into an access using Excel VBA (Jet/ACE SQL I think!?). I do this by constructing INSERT INTO strings and looping through my spread sheet. Let's say the primary key is associated with the field [Company] which holds the company names. A simplified version of what I was using (it all works great ) is below:

Code:
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = GetConnectionString()
cn.Open
cn.Execute strSQL

where strSQL =

Code:
INSERT INTO tblTestMDB ([Company], [Date])
VALUES ('BP', '30/09/2012')

Unfortunately, I am having issues with users adding the same company twice. I'd like it to:

- update the existing company info if the company already exists
- add the company if it is not already there.

I'm trying to do it in two stages. First an update, then an add. The first part works great :

Code:
UPDATE tblTestMDB
SET [Date]='21/03/2015'
WHERE [Company]='BP';

The second part: (I have tried numerous variations on the syntax):

Code:
INSERT INTO tblTestMDB ([Company], [Date])
VALUES ('BP', '30/09/2012')
WHERE NOT EXISTS (SELECT * FROM tblTestMDB WHERE [Company] = 'BP');

I find the error messages rather cryptic but they include things like "Query input must contain at least one table or query." or "Missing semicolon ( ; ) at end of SQL statement." (<== No I'm not!?

View Replies


ADVERTISEMENT

Queries :: INSERT QUERY Not Inserting One Field

Nov 27, 2014

I have a form with many fields and one field has an OnChange event to run an insert query or log an entry in a table about the change. I get insert entry written with everything except one field.The OnChange event code is:

Code:
If AddressStatusFld = 2 Then
'If it has changed from Current to Non-Current create a Contact Log entry for this matte
If (Val([MatterShortNoFld]) Mod 2 = 1) Then
Me.OperatorFld = 16

[code]....

I just can't work out why the record in MatterContactsMade table has a null value for the field Operator.

View 4 Replies View Related

Queries :: Query For Inserting Random Records Into Temporary Table

Jul 16, 2015

I'm trying to insert 10% of a dataset from dbo_billing into another table Random_Temp. Another form is open when this query is to be ran that passess in the billyear and billmonth... I'm sure it's a syntax issue as I can isolate the random number part and it displays the appropriate data, I just can't re-write it to insert into the other table:

INSERT INTO Random_Temp ( indx, peopleId, audited )
SELECT TOP 10 PERCENT b.indx, b.peopleId, b.audited
FROM dbo_Billing AS b
WHERE (((b.billYear)=[Forms]![billing]![billyear]) AND ((b.billMonth)=[Forms]![billing]![billmonth]) AND ((b.recertifying)=-1))
ORDER BY Rnd(-(1000*b.indx)*Time());

View 2 Replies View Related

Queries :: Query Duplicate / Replacements Are More Than 1

Aug 14, 2013

I have a database which holds information about repairs. Each repair is called a task and among other things data is held about things(s) that caused the failure and what need to be replaced. Usually it's one fault but it could be many.Replacements are usually more than 1. Bottom line, in a simplistic view, I have 3 tables task, fails and replacements. There is one to many relationship between tasks and fails and also a one to many relationship between tasks and replacements.

If there is one fail and many replacements my query is good enough as it will produce

task1 fail1 replace1
task1 fail1 replace2
task1 fail1 replace3

On the report I can hide duplicates to get the desired (. = space!!)

task1 fail1 replace1
...............replace2
...............replace3

Problem occurs when I get more than 1 fail. Query returns

task1 fail1 replace1
task1 fail1 replace2
task1 fail1 replace3
task1 fail2 replace1
task1 fail2 replace2
task1 fail2 replace3

Hiding duplicates I get

task1 fail1 replace1
...............replace2
...............replace3
.........fail2 replace1
...............replace2
...............replace3

whereas I'd like to get

task1 fail1/fail2 replace1
.....................replace2
.....................replace3

or something similar.

View 14 Replies View Related

Queries :: Query Shows Duplicate

Dec 4, 2014

I hve a query that I want to total soldAtPrice *quantity This information is stored in my order details table and mus not change .I can do a calculated field to get the answer but the Problem is if there are 2 recodrs to be totaled the query displays 2 records if there are 3 records the query dislplays 3 records and so on .I need one record to be displayed with the total of all the recodrs

View 3 Replies View Related

Queries :: Duplicate Count From Group By Query

Jul 17, 2014

I'm trying to count the number of records within a region range using a lookup table however I keep getting duplicate values, SQL code, what is happening:

SELECT Count([summary].Key) AS CountKey
FROM Summary, lookup
WHERE ([Region])) Between [Region 1] And [Region 2]));

View 4 Replies View Related

Queries :: Hide Duplicate Record Using Query

May 18, 2015

I have a question on hiding duplication record using query.

The fields in the query are:

full name(trainee), NRIC(trainee), gender(trainee), preferred language(trainee), company(trainee), course name(course), course date(course), competent(course), class(course), L3 survey(trainee), L4 survey(trainee), num of month(course)

When the query is being run, it will show all the people that have not done the L3 and L4 survey after 3 month. The the query will be convert into a form. However the problem is that the record will show a few same name due to one person can take more than 1 course. therefore, the data in the course table will always be different.

How can i make the record only show 1 name even though they have different course name.

I had tried putting 'yes' for unique record and unique value but it did not change the result.

current SQL query:

SELECT DISTINCT trainee.[Full Name], trainee.NRIC, trainee.Gender, trainee.[Preferred Language], trainee.Company, course.[Course Name], course.[Course Date], course.Competent, course.Class, trainee.[L3 survey], trainee.[L4 survey], DateDiff('m',[Course Date],Date()) AS [Num of Month]
FROM trainee INNER JOIN course ON trainee.NRIC = course.NRIC
WHERE (((course.Competent)="c") AND ((trainee.[L3 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=3)) OR (((trainee.[L4 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=6));

View 6 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 :: Custom Delete And Duplicate Query

Jun 12, 2013

I am trying to create a query to find duplicates and delete the duplicates. The result will eventually be used in another query (append query) to update a table.I have a table with 4 columns lets say for simplicity they are A, B, C, D

I want my query to find duplicates within B and deleting them. The catch is before deleting them I need to look into column A to ebsure they are actually duplicates. Example below

Example
A B
John Doe Tires
John Doe Wipers
Allison Doe Tires
Allison Doe Tires

As you can see from the above Tires is a duplicate and need to be deleted.

A B
John Doe Tires
John Doe Wipers
Allison Doe Tires

View 7 Replies View Related

Queries :: How To Manipulate Find Duplicate Query

Jun 15, 2013

I am trying to manipulate a find duplicates query using the following criteria:

Fstnm L2, Lstnm L5, Add1, Zip

This is what I have done so far:

SELECT [Duplicate Identification Dataset].[FSTNM], [Duplicate Identification Dataset].[LSTNM],
[Duplicate Identification Dataset].[ADD1], [Duplicate Identification Dataset].[ZIP],
[Duplicate Identification Dataset].[ID], [Duplicate Identification Dataset].[MIDNM],
[Duplicate Identification Dataset].[SPFSTNM], [Duplicate Identification Dataset].[SPMIDNM],

[Code] .....

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

Queries :: Creating Query Without Repeating Duplicate Relationships

Feb 13, 2014

I have two tables that look like this:

Table 1

LOCATION NUMBER , SIZE
1.12 ,100
1.13 ,100
1.14, 12
1.15, 12
1.16 ,150
1.17 ,150
1.18 ,100

Table 2

ITEM , SIZE
A , 12
B , 12
C ,100
D ,12
E ,100
F ,100
G , 150

I would like to do a query that Joins the "Size" in each table, and then matches an "Item" to a "Location". However, because of how a normal join works, I cannot seem to figure out how to limit the "Location" field from producing duplicates in the match.

I only want to have 1 location for every 1 Item.

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

Queries :: Syntax In Query To Remove Duplicate Data For Field

Apr 26, 2013

I'm having difficulty with the syntax in this query to remove duplicate data for the field "StocktransID".

Code:
SELECT DISTINCT tblStockTrans.StockTransID, tblItem.Brand, tblItem.Category, tblItem.SubCategory,
tblItem.Model, tblItem.Description, IIf(TransTypeID=3,Quantity*-1,Quantity) AS Qty,
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType,
tblItem.Origin, tblOption.ParentID

[code]...

View 6 Replies View Related

Queries :: Inserting A Record Number?

Apr 9, 2013

I have a query with records,

I want a field named RECORD NUMBER beside it I want a consecutive number increment EG

RecordNumber - RecordDecription - RecordTitle
------------------------------------------
1 - TITLE001 - TITLE001
2 - TITLE002 - TITLE002
3 - TITLE003 - TITLE003

is there a formula i can use in my query to create this?

View 5 Replies View Related

Queries :: Inserting Missing Data In A Table

Jan 10, 2015

How to fix some records in my access table. It is a huge table more than 12k records!

In one of the field there are some data missing. The logic to reconstruct them is easy but I am not sure how to apply it in Access.

I have three columns one is the student ID, Year, term1 and term2

ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003
3311 2004 005 005

In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311

ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004 003 004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003 004 005
3311 2004 005 005

View 3 Replies View Related

Queries :: Inserting Values From A Listbox Into A Table

Jun 28, 2013

inserting values from a listbox in to a table. I have a table with a field named: PrefReports. This field will hold data from values in a list box that the user selects.

Table: Profiles
Field Name: PrefReports Type: Memo

The user utilizes a form to input the values necessary for the query. I then have the query tied to a command button. When the command button is clicked, the query is executed.

The query is as follows:

INSERT INTO TblProfiles ( [Employee ID], FName, LName, Barcode, PrefReports )
VALUES ([forms]![FrmCreateNewProfile]![txtEmployeeID], [forms]![FrmCreateNewProfile]![txtEmployeeFName], [forms]![FrmCreateNewProfile]![txtEmployeeLName], [forms]![FrmCreateNewProfile]![txtBarcode], [forms]![FrmCreateNewProfile]!lstReports.Value);

How can I take what the user selects in the list box, and insert into my TblProfiles.PrefReports field? When I run the query, all other information makes it to the table except the list box values.

View 3 Replies View Related

Queries :: Check If Record Exists Before Inserting From Form?

Jul 2, 2014

The problem I have is, that I need to insert an apointment into de database but first I need to verify if there is a record or an apointment in that room that day the same hour, if that is so send a msgbox saying an apointment already exist in that room this day at this hour.

i tried using dlookup but it only works in one record using just one criteria

View 10 Replies View Related

Queries :: Inserting And Updating Based ID In Main Table

Sep 29, 2013

I have add my two tables

Main table calls Attack
Sub table calls Research

the main table attack is linked with sub table Research by Attack.ID and Research.attack_id from form automatically

The sub table Research have more than one record which is linked into the main table Example:

How can I get the data which is marked in blue into record 24 and the data marked in Red into row 23

using Unite_Equal column?

View 3 Replies View Related

Queries :: If Function And Inserting Null Values In False Part

Oct 7, 2013

I have a calculated field (Bhours) with the following IIf function:

=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)

It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".

View 7 Replies View Related

Queries :: Current Date Field From Form Inserting In Table

Mar 7, 2015

I have a form, has some fields, one of them is the current date, so when the user click (save )button , which make (add new record )to the only table I have the problem that .all fields are inserted in the table , except the current date !! it is a text box ( Now() )!!

View 1 Replies View Related

Inserting Name From Listbox Into Query

Nov 8, 2004

I have a listbox inside a subform. I want to use the 'click' Event on a "search" command button (found on the main form) to trigger
a query from inside that listbox and fill it with values from a table. The following is the argument I have written for that event procedure:


Private Sub CommandSearch_Click()
Me.GS_SearchSF.Form.listSF1.RowSource = _
"SELECT MaxSize1 " & _
"FROM GS_Ctg_Float " & _
"WHERE Supplier = '" & Me.GS_SearchSF.Form.SupplierQuery.Value & "';" 'THIS ONE ISN'T WORKING
End Sub


Except as you might imagine, it is not working. It works when I just type a name from that table into the argument in place of
the text seen above in red. For instance:

Private Sub CommandSearch_Click()
Me.GS_SearchSF.Form.listSF1.RowSource = _
"SELECT MaxSize1 " & _
"FROM GS_Ctg_Float " & _
"WHERE Supplier = 'Viracon';" 'THIS ONE WORKS
End Sub

What am i doing wrong?

Here's some background:

GS_SearchSF is the name of my subform

listSF1 is the name of the list box in the SubForm that the
query is supposed to fill

SupplierQuery is the name of the list box in the subform
I want to extract the value from and put into the query

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

Doing Calculation And Inserting That Value Into New Row Into Query Table

Apr 13, 2008

Hi, I am wondering If I can Sum the value of rows in one field of my Query Table and Inserting that Calculated value into the bottom of the summed Row in the VBA or through Query Design View.

Please let me know, Thank YOU!

Below I have attached the picture of the data that Im trying to sum and insert into a new row that Hopefully can be created through MS access Query.

View 1 Replies View Related

Inserting Data Into A Field From A Query.

Oct 3, 2004

Hi,
Am currently working on a project for AS IT. one problem that i need to come across is trying to see data in a field that comes from a query?!?! Confused?! so am i!

When i first open the form, a paremeter query appears asking me to insert a surname (which i have created) when i insert the surname....and click on the drop down button, it gives me three values...ForeName, Surname and class

When clicking on the pupils name, i want the Class of the student to be inserted into the Class Field!

Can anybody help?
Danbwest

View 1 Replies View Related







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