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 Replies


ADVERTISEMENT

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

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

Queries :: Insert Into Query Will Only Work On Some Records - Key Violation Error

Oct 23, 2014

I am building an access database for my college project and I essentially have a quotation form that when I click a button 'Convert to Invoice' it creates a new record in the invoice table and then creates new records in the invoice details table which match the quotation details table. This is working as it should but for only the first 2 customers in my customer table?

On the quote form I have a combo box which is linked to the customer table and updates the quote table based on the selection. If I select customer 1 or 2 and click 'convert to invoice' it works and opens an invoice form based on the inserted data however if I select any other customer it returns an error that the record wasn't added to the table due to key violations?

As far as I can tell I am not trying to update the primary keys in the Invoice Table or the Invoice Details Tables.

View 1 Replies View Related

Forms :: How To Duplicate Subform Records

Jun 25, 2015

How to duplicate records in SubForm, i have created a form (transmittal) with a subform that contains all items listed:

Master Field : Transmittal No
Child Fields: IDTransmittalNumber

There is a button that allows me to duplicate form , however when i change the Transmittal no in the main form it automatically deletes all data in subform since they are both linked by that field..

View 8 Replies View Related

Forms :: Subform Creates Duplicate Records

Aug 29, 2013

I have a form that uses Table A as a record source and has a subform that uses Table B as a record source. There is a one to many relationship between Table A (one) and Table B (many).The user opens the form to a set record, then uses the subform to create a new record in Table B.

This works perfectly and I can do everything I want. But sometimes the subform creates a blank entry in Table B before I enter any data. Then, once I start entering data, it creates another record in which it stores the data that I am entering.The result is two records: One that only has the date of the record's creation (I set that to be automatic upon creating a new record) and the primary key that links Table B to Table A. A second one is the "true" record, the one which stores all of the data that I am entering.

It won't happen for 10 records or something and there is no apparent pattern. In case it makes a difference, the form is set to open as the subform as the focus.

View 14 Replies View Related

Reports :: Multiple Duplicate Records In Subform

Mar 9, 2015

I currently have a report which contains a subform. The master/child fields used to link them are called 'StudentID'.

In the table on which the subform is based, it is possible for multiple records to be associated with a single StudentID: in this case, the subform shows the locations where a student is studying, therefore if a student is based in three different locations the subform will display all three.

This seems to be resulting, when I run the master report, in three identical iterations of the same report being displayed (at least this is the only reason i can see why these duplicates would be displayed; it doesn't happen if a student only has one record in the subform).

Clearly, as a single instance of the report shows all three records in the subform anyway, I don't want to be seeing these duplicates.

View 5 Replies View Related

Forms :: Insert New Data And Show All Records In Subform

Mar 27, 2013

I have a form with a subform. I want to use the main form to insert new data and the subform to show all records that are there. One could say that the after inserting a new record with the fields in the form and save it, it should appear in the subform datasheet view.

Please see attached the sample database..

View 3 Replies View Related

Modules & VBA :: Unable To Create Duplicate Records On A Subform?

Nov 20, 2013

I am working with a sub-form where once a staff member enters there sub measure I would want to create a duplicate of that record. The problem I am having is that once you enter the sub-form and click the duplicate button it creates a duplicate of the record selected but overwrites the first record in the table. I want it to create a new SubMeasure Number which is the primary key and assigns the record the next available number.

Also if I try to add another record after one has been added I get runtime error "3021" - No current record. I would have to close the form and reopen for it to be able to add again.

I have attached the code below:

Private Sub cmdDuplicate_Click()Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form
'Return Database variable pointing to current database
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

[code]....

View 4 Replies View Related

Modules & VBA :: Create X Number Of Duplicate Records According To A Field On Subform

Jun 13, 2013

I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".

The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.

Code:

Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer

[code]....

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

Duplicate Records In Queries

May 10, 2006

I thought I had just about finished my DB but now Ive printed out and checked my reports I notice I have several records in different types of reports showing duplicate records. Iv'e gone back over the queries and there are one or two duplicates in several of my queries that I did not notice before because there are are only the odd one or two. No matter what I do I can't stop this happening. I think it must be something to do with the dates in my payment table. PaymentID is key field - foreign key is MemberID. One member can have several paymentID's a new one every year when membership renewed. The problem seems to be when the member has two payment dates in the same year. This shouldn't be anyway but the database has not been used properly with entry dates missing, written over of wrong dates etc. Ive tried to correct this but do not want to tamper with past payment date records. I already have 'select distinct' in my queries and have tried 'distinct row' which seems to return even more duplicate records. Im pulling my hair out over this there must be a way to return the records from members showing just 1 only of their very latest payment record/date. Im using this expression in the query.

LastPaid: (SELECT MAX(PaymentDate) FROM S_Payments_Table WHERE S_Payments_Table.MemberID =S_Members_Table.MemberID)

View 1 Replies View Related

Queries :: No Duplicate Records

Oct 25, 2014

I have been hitting my head against the wall to have my query not show any DUPLICATE STOCK CODES in my report..I have two "AQN.PR.A" records, I only want my report to print one "AQN.PR.A"...I have three "ALA" records, I only want my report to print one "ALA"AQN.PR.A

View 14 Replies View Related

Queries :: Duplicate Records Do Not Show

Feb 20, 2014

How can I have this query to show no duplicate records.

I would like to only see the first record only of each stock code.

View 2 Replies View Related

Queries :: Removing Duplicate Records

Jul 23, 2015

I'm having a problem removing duplicate records from a table. It should be easy but I can't suss it.This is an example of the data in my table:

Code:

RefDateStatus
113007111/06/2015Do Not Pay
114454306/07/2015Do Not Pay
115760714/05/2015Do Not Pay
116520705/05/2015Do Not Pay
117670108/05/2015Do Not Pay
118036218/05/2015Do Not Pay
118517015/05/2015Do Not Pay
178734020/07/2015Do Not Pay
182809915/07/2015Do Not Pay
184226010/07/2015Payment Due
184226022/07/2015Payment Query

As you can see, there are 11 records here but the last two records have the same reference number. I need my query to show the first 9 records + the record from the bottom two with the latest date (22/07/2015).

View 2 Replies View Related

Queries :: Duplicate Records Show Only The Last One

Jun 24, 2015

I have a table and there are so called duplicate records (not entirely duplicate because there is field with the date of creation and other differences in a few fields, but from my point of view they are duplicates.). I d like to make a query which shows me only the newest record in case of duplicity.

View 3 Replies View Related

Queries :: Updating First Row Of Duplicate Records

Jan 30, 2014

I have two tables -say Table A and Table B.

Table A Table B

ID userno ticketno Status userno ticketno Status
1 1211 YAB 1211 YAB LIVE
2 1211 YAB 1232 ACE LIVE
3 1232 ACE 1232 AAT INVALID
4 1232 ACE
5 1232 ACE
6 1232 AAT

I am trying to write an update query in MS Access that will update only the first record of any custno with the status so that my final table A looks like this:

Table A

ID userno ticketno Status

1 1211 YAB LIVE
2 1211 YAB
3 1232 ACE LIVE
4 1232 ACE
5 1232 ACE
6 1232 AAT INVALID

How to write the query.

View 3 Replies View Related

Retriving Duplicate Records With Multiple Queries

Aug 18, 2005

when running the attached report I am getting duplicate records. If using the protocol specified tc02026 there should be 8 records total. When running the report it pulls from both queries which have select criteria. How do I limit the report to only give me the 8 records and eliminate the duplicates? When I run each query it only gives me the 8 records I am looking for. Each Sample# in the report is a unique number(to help see the replicates).

Thanks

View 1 Replies View Related

Queries :: Remove Duplicate Records From Table

Jun 12, 2014

I have a table which has duplicate records so I want to write down the query that will remove the duplicate records from the table.

View 1 Replies View Related

Queries :: Find Duplicate Records In Table With Two Fields

Aug 29, 2013

I want query to find duplicate records, i have two field in one table

Cusip and category

cusip and category are many or duplicates

but in one cusip category should be the same if not then provide the cusip which has different category used

like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A

result should be

CusipCategory
123R
123R
123A

View 6 Replies View Related

Queries :: Update And Insert Records With SQL Statements Using Access 2010

Jul 10, 2014

I am trying to update and insert records with SQL statements. Below my code:

Select Case FirstGRV
Case "Y"
sql = "UPDATE Tbl_SellingPrices " & _
"SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = date(), SellingPriceDateTo = #" & Me!Text3 & "#" & _
"WHERE SellingPriceStockCode = Forms!Frm_GRV!GRVStockCode and SellingPriceDateFrom =#01-01-1900#;"

[Code] ....

My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.

View 14 Replies View Related

Queries :: Insert Into - Copy Records From Another Base Into Existing Table

Apr 15, 2014

I'm trying to copy records from another base into existing table in current base by:

Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")

But it doesn't run. Says insert into syntax error.

View 6 Replies View Related

Queries :: Get Rid Of Duplicate Records That Show Different Data In Multiple Columns

Sep 8, 2013

I have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas. For example: I want this...

Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training 1/1/2010 5/1/2010
1 John Smith Python Training 1/30/2011 4/1/2011
1 John Smith Leadership Development 6/27/2013 9/1/2013
1 John Smith Sensitivity Training 9/5/2010 -
2 Hank Joel MS Office Training 8/1/2010 10/1/2010
2 Hank Joel Sensitivity Training 8/1/2010 10/1/2010
2 Hank Joel WHMIS Training 11/15/2010 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012

To turn like this:

Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training, Python Training, Leadership Development, Sensitivity Training 1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010 5/1/2010, 4/1/2011, 9/1/2013, -
2 Hank Joel MS Office Training, Sensitivity Training, WHMIS Training 8/1/2010, 8/1/2010, 11/15/2010 10/1/2010, 10/1/2010, 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012

I am using two tables to find the data (main - "employee tbl" and "courses tbl")I have been trying to follow Allen Browne`s method, but I`m unsuccessful. This is the code I've put in SQL of this query:

Quote:

SELECT [Employee #], ConcatRelated("[Courses]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Start Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl];

However, I am prompt with "Syntax error in query expression".It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:

Quote:

WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")

I placed the following in VBA module:

Code:
Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _

[code]....

View 4 Replies View Related







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