Stop/SKIP Appending Query If Duplicate Found
Mar 25, 2007
I want to stop/skip appending of those records who match in main data table "MCIGMMS" on the basis [PORTCD] [IGMNO] fields in "PCIGMMS2". When Match found Msg Box appear and appending skip or stop of those records.
Can any one help me or give me code.
Regards.
SQL CODE:
INSERT INTO MCIGMMS ( PORTCD, IGMNO, IGMYY, IGMDAT, SHPANO, VSLFLT, VOYAGE, ARVDAT, SLOCCD, CNTCOD, LSTPRT, CAPTAN, SHPCMP, TOTIDX, BOTTOM, BEGIDX )
SELECT PCIGMMS2.PORTCD, PCIGMMS2.IGMNO, PCIGMMS2.IGMCY, PCIGMMS2.IGMDAT, PCIGMMS2.SHPANO, PCIGMMS2.VSLFLT, PCIGMMS2.VOYAGE, PCIGMMS2.ARVDAT, PCIGMMS2.SLOCCD, PCIGMMS2.CNTCOD, PCIGMMS2.LSTPRT, PCIGMMS2.CAPTAN, PCIGMMS2.SHPCMP, PCIGMMS2.TOTIDX, PCIGMMS2.SBOTTOM, PCIGMMS2.BEGIDX
FROM MCIGMMS RIGHT JOIN PCIGMMS2 ON MCIGMMS.IGMNO = PCIGMMS2.IGMNO;
View Replies
ADVERTISEMENT
Jan 15, 2015
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results.
after item 2, do an export.
etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME],
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
[Code] .....
View 1 Replies
View Related
May 2, 2006
I have two table. Deliveries Created (DC) and Goods Receipt (GR).
I have a query which links the two by PO number, PO Item, Unit code and Delivery quantity.
This works fine until one PO Number has more than one delivery quantity which is equal. For example.... The PO quantity is 4,000 but, because the units can only be shipped in quantities of 1000, there are four different entries in both the DC and the GR tables each with the same PO number, PO Item number, Unit code and delivery quantity (and shipped on the same date). This of course gives 16 results for the query using this PO number.
There is one unique field in the DC table this is the posting number field. I've tried grouping by this number and taking the First values from the GR table. This works OK in that it produces Four results but, two of the deliveries were received on Day 1 and two were received on Day 2. If I take First of GR date I get DAY 1 for all, If I take Max I get Day2 for all. What I need is the correct date.
I hope I have explained this OK.
And I hope you can help
View 6 Replies
View Related
Oct 22, 2006
I will explain this situation using a scenario. Let's assumed that i'm
working on a library system where i need a loan form that will only
show books that are available for loan. In this case, this form has a
main form that shows library member's details and its subform shows the
details of the books that are loan by library member. In this subform,
one of the fields (ISBN no) displays its values using a combo box. Once
a value is selected from this combo box, values in other fields found
in the subform will be shown too. These values are based on a table
that contain books info, where only the value (ISBN no) in the combo
box comes from a query. This query will only show books that are
available for loan.
If a library member wants to borrow 'booktitle1' then this book will
not be available to be loan by other members and assumed that there is
only one 'booktitle1'.
I tried to update the field manually by changing the status field every
time the book is loan out. I hope to solve this problem in an effective
way.
I am new with access and i have difficulty to explain it in a much
better way. Sorry, if my description cause any sort of confusion.
These are the tables.
book_info (table1)
-------------------------------
bookTitle ---------- text
ISBN(pkey) ------- text
authorName ------ text
category ---------- text
dateReceived ---- date
publisher ---------- text
status -------------- text
user_info (table2)
--------------------------------
name --------------- text
userID(pKey) ------ text
address ------------ text
tel ------------------ number
hp ------------------ number
occupation -------- text
DOB --------------- date
loan_info (table3)
------------------------------
userID(fKey) ------ text
ISBN(fkey) -------- text
dateReturned ---- date
dateBorrowed ---- date
dateDue ---------- date
remarks ----------- text
Thanks.
Leah_603
View 1 Replies
View Related
Feb 25, 2005
I have an user imput form that has a unique file reference number which is typed in by the user in the first bound field. Does anyone know of a way of informing the user that the file reference number is already in use and therefore a new one must be used. At the moment the user is completing the entire form before being made aware that it would create duplicate records and therefore unable to save current record etc...Totally fustrating! any ideas? Steve
View 2 Replies
View Related
Dec 3, 2012
I have one Form & Table. I am making customer list, But i dont want a duplicate name, so how i control or stop duplicate name when i do entry.
View 1 Replies
View Related
Apr 3, 2013
I have a database that lists all the books I have read. The Table "Books" holds "AuthorId' "Title" etc. I have another table that is called "Whats Next To Read" which I store the next book that I will read from that author. It also uses "AuthorId" and "Title" etc. In the form, how can I look to see if the title put in for that author in "Whats Next" does not have the same title already in "Books" table. I can use Dcount to find the title but it could be same title different author. I need same title with same author.
This is what I have so far.
If DCount("Title", "Books", "Title = '" & forms!Whatsnexttoread!Title & "' > 0 Then
Msgbox "Title is already in books.", vbInformation
End If
I need it to only look at the same author.
View 3 Replies
View Related
Apr 8, 2014
I export data via vba from access to excel.
Here my VBA.
Code:
Dim xlApp As Object ' Excel.Application
Dim xlBook As Object ' Excel.Workbook
Dim xlSheet As Object ' Excel.Worksheet
Dim rst As DAO.Recordset
[Code] ....
How can i do it that by exporting the data to excel, that it will skip one row.
Example:
I have the following querry
country/date apr may jun jul aug sep oct nov dec jan feb mar
AT
BE
It starts with A4 to J4 and then it will skip the row K4.
View 2 Replies
View Related
Sep 18, 2005
ok im having some serious problems here. im using a tutorial to make a database for my small business and everything was going great, except this 1 problem ive been having for like 2 weeks now. i had my 2 main tables linked together using another table which is my foriegn field i think? anyway i used a query to make this table and to add the data from my other 2 tables to it. now i want to add the data from my suppliers table to it aswell but i dont know how. my original query wasnt saved cause the tutorial told me i didnt need to save. i think what i need to do is append this data to query like i did when i first made it but if the query isnt there anymore how can i append it? does this mean i have to start my database from scratch and do all 3 tables at once? or can i make a new query? ive tried making a new query to add this data to the linking table but no go. im horrible at explaining this so hopefully some pictures will help.
heres my relationships. what i want to do is make my tblSuppliers link to my tblItems. i think i need to link it to tblCategoryItems and do the query thing to add the data to it. is this correct? or is there another way i can do it?
http://i11.photobucket.com/albums/a174/ragrim3/relationships.jpg
in this pic you can see how the first 2 fields were populated for me by the query but now i added an extra field which i want populated with the supplier ID. i tried to manually add the number of the supplier here but it didnt work either. i think i need another primary key for it to work but i cant add another primary key to this table cause access complains.
http://i11.photobucket.com/albums/a174/ragrim3/blah.jpg
this is getting realy frustrating that i cannot do this and i would be overly thankfull to anyone who has the time to help me out here. thanks.
View 6 Replies
View Related
Mar 22, 2006
Can you append to a table that is empty? If you can please let me know. I made table through "make table" query and now I run the append query to the empty table but it is not putting any information in the empty table
Can you append to a table that is empty?
View 1 Replies
View Related
Jul 15, 2006
Hi all,
What is an Appending Query?
How to create an Appending Query?
Sorry for bothering you...
Thanks & Regards
View 1 Replies
View Related
Jul 4, 2005
I need to build up a string containing values separated by commas. The following almost does this.
I use an update query in which the "Update to" field for 'String field' contains
[String field] & IIf(Len([String field])<1,"",", ") & [Related table]![Value to append]
I would like to have the [Related table]![Value to append] in ascending order.
I.e. I would like the result to look like "A, B, C, F, H", not "B, C, H, F, A". Any suggestions as to how to apply the sort?
View 1 Replies
View Related
Feb 9, 2005
i have several comboboxes and textboxes in a form called
inputorders.frm. I am trying to get all of them to an append
table which would allow to show the control values in a table on
the form with a command button:"add item". I'm using a
append query that will only run by itself. Getting the "enter
parameter values" msgbox and not the values from the form.
I really don't know what i'm am doing wrong. It is a single row and
I can't get the appendquery to work with the command trigger event
What is the correct way to code the query along with the command
button to trigger the query with the control values automatically displaying
in the table.
THanks for the help,
View 9 Replies
View Related
Jan 4, 2006
i have a table with tons of records in it. There is a field called "date to return by" that previously had a default value of Date+30. Now, some things changed in our company, and under certain conditions, the "date to return by" for new records will be Date+14, or Date+30. At first, i tried making this relationship in a table as a default value, but, you cannot make a relationship like this in a table.
So, i made a query to evaluate the relationship.
what i want to do, is have a query that will have all the same fields the table had and evaluate whether a records' "date to return by" should be Date+30 or Date +14, and then put that new record back in the table.. but, this will only be for new records, as i will be keeping all the old records in the table as they are.
how can i do this?
all of my reports read off of my table so i want to keep the table. can i make a query that will put new records into a table one at a time as i make them?
View 1 Replies
View Related
Jan 24, 2006
Ok so I am making an application which requires me to add a record.. ok fine, but then i also need a cancell option, which would delete the record... np, BUT if the user cancells, I don't want my Auto number increasing... So I am writing to a temporary table which mirrors the original one, and then if the user clicks cancell, i delete the temporary record... If the user clicks "save" then i want to Append that new record to my original Table and then delete the record... HOWEVER, when i appended this record the first time it added 2 identical records to the original table, Then 4 the next time, Then 8, 16, 32 , etc etc I'm not quite sure how I did this but i hope its a common problem that somebody has maybe done themselves and will be able to help me out...
Attached is a Skeleton with the original Table, and the Temporary Table, along with the same query currently i have some data entered, When you attempt to add another record to the temp table , and then run the append query, it will add 8 identical records... TRY IT! and then HELP ME!! please!
View 2 Replies
View Related
Sep 8, 2014
I'm making a database for tracking purchase orders for contract workers in my department. Business rules dictate that a PO is created for each unique Contractor-Project instance.
I pulled a report (.csv format) of weekly timesheets from the company's project management database system. Some of the fields include: EmpUserName, EmpID, ProjectID, ProjectName, WeekEndDate, Hours. I linked to the file in my database and ran a query on it without WeekEndDate so that I get the total hours each contractor has worked for each project they've billed to. I created a table called tblContractorProject and appended the records to it. I'm going to be pulling this report each week so I can see if any contractors start billing to any new projects so I can create a purchase order for it. Is there a way to write an append query that can recognize if the EmpID and ProjectID combination is new and doesn't already exist in the table?
View 1 Replies
View Related
Jun 29, 2007
Hi.
I have two tables, both with load of info. I need to run a query, that checks data in table1 against data in table2. If it is in table two, then moves on. If it is NOT, then shows the results to me. So that i can see which ones were not there.
how do i go about that.
thanks
Alex
View 7 Replies
View Related
Aug 12, 2005
Hi Guys and Girls.
I have about 100 or so tables that I need to append back into one table :eek:
However all of these 100 tables all begin with the number 100 at the beggining. I know that you can append tables - but as far as I know you can only do one at a time. Is there any easy way to do this - for example writing a small SQL statement - saying select all tables that begin with 100* and then append into a master table :confused:
Alternatively are there any programs on the net that can do this (I just wrote a massive macro to import them from a text file in to access- not realising this problem would happen!) :(
Unfortunately each of the tables has the first row as the column heading too?
Any help would be much appreciated!!
Cuurently using Access 97 though.
View 1 Replies
View Related
Feb 27, 2015
I have a local table that I am trying to append to a linked table. The fields are exactly the same. When I try to append the entire local table to the linked table I get an error code.
ODBC- insert on a link table failed.
[ctreeSQL]-17002 CT- Key value already exists in index (linked table field) (#-17002)
If I specify the criteria in the field to refer to a specific value in the local table, it updates it just fine. I want an append query because I don't want to manually update 500+ records!! I don't believe an update query would work because the values are not in the current linked table... so nothing to update!
View 8 Replies
View Related
Dec 17, 2006
Hi,
I am trying to implement an update query per the instructions at http://support.microsoft.com/default.aspx?scid=kb;en-us;127977 to append records from table2 to table1.
I would like to add a criteria here saying update (and append) only records that match the criteria table2.dateofreview <= #12/17/2006#
This does not seem to work. The same query works without this criteria.
To test this, I created just one record in table2 (table1 is blank at this time) and manually entered the dateofreview as 12/17/2006 and tried to execute the query. It is not able to show any records.
Won't this query work *with* a Criteria?
View 1 Replies
View Related
Oct 21, 2014
we have a calculated field in an append query Line_Cost which eventually creates a purchase order.
We have to have a minimum order value of £1500.00, so, we need to total the values before running the append query.
View 3 Replies
View Related
Aug 20, 2007
I've created a function to search for a string in SQL definitions:
Public Sub SearchInQueryDefs(strSearch As String)
Dim qdf As QueryDef
Dim qdfs As QueryDefs
Dim blnFound As Boolean
Set qdfs = CurrentDb.QueryDefs
For Each qdf In qdfs
blnFound = InStr(1, qdf.SQL, strSearch) > 0
If blnFound Then
Debug.Print "Searching : " & qdf.Name & "...";
Debug.Print " - found"
If vbNo = MsgBox("Found!" & vbCrLf & vbCrLf & "" & strSearch & " found in "
& qdf.Name & vbCrLf & vbCrLf & qdf.SQL & "" & vbCrLf & vbCrLf & "Click 'Yeah' to
continue search, 'Duh' to stop", vbExclamation + vbYesNo, "SearchInQueryDefs") Then
Exit Sub
End If
End If
Next qdf
MsgBox "Done searching.", vbInformation, GetAppTitle()
End Sub
Using the following statement (in the immediate window) i get the following result:
SearchInQueryDefs "Queries"
Searching : ~sq_cfrmReports~sq_clstQueries... - found
However query "~sq_cfrmReports~sq_clstQueries" doesn't exist.
It is probably a query which populates the listbox "lstQueries" in the "frmReports"
form. But that form doesn't exist in my database. I have deleted it some time ago. I
thought that Compact and Repair got rid of stuff like this.
I found the definition in the MsysObjects and with this Id also in the MsysQueries.
So my question is obvious: what is this, why is this and what can i do about it?
Thx!
View 6 Replies
View Related
Oct 20, 2005
In query:
I set the input parameters:
enter: [Please enter the value:]
After running, it shows "Please enter the value:"
This one I know
How can I make 2 lines showing
"Please enter the value:
eg. 0506:
enter: [Please enter the value: & vbcrlf & eg. 0506:]
It doesn't work.
Please let me know about it. Thanks.
View 1 Replies
View Related
Apr 25, 2005
Hi everyone, I'm nearly done finishing a database for a legal firm and one of the small issues I'm having with this project is that, I have a button that creates records in other tables once a record is created in a primary table.
I have a table called tbl_login, it has a field called job_number (which is an autonumber), and when all the info is entered in the form associated with tbl_login I want a button to give the user the ability to create a record in tbl_billing with the value of job_number, now this is a easy append query, works great, but the problem is, when the button is pressed there is an event message, "You are about to update an table....." this is the natural warning even message that access gives before a change is made, my questions is.
To please me end users, how do I get that event message to stop popping up. So they just hit the button, it appends, no warning.
I'm open to suggestions,
View 4 Replies
View Related
Jan 10, 2005
I'm trying to create a query which will only show fields that have information in them, but I can't figure out how to do it.
View 2 Replies
View Related
Dec 19, 2005
Hi
I am a new user to Access. I created couple of forms using wizard, but when I view them, they show me all the available data by default. I want to stop this, if anyone can help me plz.
RMA
View 5 Replies
View Related
