473,732 Members | 2,190 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Can a query "look" at whats already in 2 table fields before appending a rec w/ redundant values in those two fields?

I have a query, qryAppend30DayO ld260ies that attempts to append
records to tblCorresponden ce. When run, it can result in any of the
following: appending no records, appending 1 record or appending
many records. Two of the target fields in tblCorresponden ce receiving
values in the append operation are [VehicleJobID] and [OutType]. For
any given VehicleJobID value, I want only ONE record in correspondence
table to have an [OutType] value of "01". This query blindly appends
to the correspondence table without regard to whether there is already
a record in tblCorresponden ce having say a value of 100 in
[VehicleJobID] and "01" in [OutType]. How can I make this query "look"
at what's in correspondence table while appending to determine that no
record in correspondence table already has matching values in the
[VehicleJobID] and [OutType] fields, avoiding putting in redundant

INSERT INTO tblCorresponden ce ( VehicleJobID, OutType, ToWhom, UserID,
InProcessor, Tracked )
SELECT DISTINCTROW tblCorresponden ce.VehicleJobID , "01" AS OutType,
tblCorresponden ce.ToWhom, "System" AS UserID,
tblCorresponden ce.InProcessor, True AS Tracked
FROM tblVehicleJobs INNER JOIN tblCorresponden ce ON
tblVehicleJobs. VehicleJobID = tblCorresponden ce.VehicleJobID
WHERE (((tblCorrespon dence.InProcess or) Is Null) AND
((tblCorrespond ence.OutDate) Is Not Null) AND
((tblCorrespond ence.OutProcess or) Is Not Null) AND
((tblCorrespond ence.OutType)=" 00") AND
((DateDiff("d",[DateLeft],Now()))>=30) AND
((tblVehicleJob s.Reclaimed)=No ) AND
((tblVehicleJob s.ENF260Written )=True));

Nov 13 '05 #1
3 1927
How about using the Find Unmatched query wizard and then turning it
into an append query?

Nov 13 '05 #2
I ended up using an index on 2 fields of the table, setting it to
Unique. That prevented addition of a second record with same
values in the two fields already held by an existing record. That
did the trick w/o a lot of fancy preventive coding.

How about using the Find Unmatched query wizard and then turning it
into an append query?

Nov 13 '05 #3
MLH <CR**@NorthStat e.net> wrote in
news:d0******** *************** *********@4ax.c om:
I ended up using an index on 2 fields of the table, setting it to
Unique. That prevented addition of a second record with same
values in the two fields already held by an existing record. That
did the trick w/o a lot of fancy preventive coding.

But the cost of discarding the index collisions may be very high. I
don't believe in letting errors happen that I know how to prevent,
so I always use an outer join to exclude the records that already
exist. This seems like good preventive coding to me.

David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: roy | last post by:
I am just curious whether it is possible to make the java application has a Mac style "look and feel" on widows. Thanks
by: Just Me | last post by:
Find in Files has a dropdown box containing folders to look in. Mine has many folders that I am no longer interested in. Is there some way I can delete these from the list? I found them in the registry but am not sure what would happen if I deleted some of them there Thanks
by: siliconmike | last post by:
I've got a table t with rows a,b,c,d,e,f and a single index i on a,b,c,d,e,f The query is: SELECT * FROM t WHERE a="something" AND b="something" AND c="something" ORDER BY d, e, f LIMIT 100; now, table t may have millions of rows.
by: Alienz | last post by:
Lets say I have a form called Planet_Systems and a form called Species. I then want to link the primary key "Planet" to bring up it's various species in the corresponding subform. So, I created a subform species within the forum Planet_Systems. They're linked properly and related etc.. I typed into the Planet combobox: "Earth". I would now like for all of the different Species found on Earth to be listed in the subform automatically once...
by: MLH | last post by:
You know how the text in the attached label sort of 'greys out' when you click something that sets MyTextbox.Enabled = False? I would like to make a label's text look like that? How do I do it? It look sort-a-like sunken text or shadowed - I don't know what.
by: AA Arens | last post by:
I made a find record button on my form "company" (with field for name, phone nr, e-mail etc.). I expect that when I focus on one of the field, the "look in" of the find-record dialog box should be set to the field, but instead of that it is set to the form name (company). To avoid it I have to set it manually to the focussed field. How to have it default set to the focussed field as I used to find records containing cetrtain data in the...
by: AA Arens | last post by:
I use the undermentioned code to find a record in a predefined "Look In" field: Private Sub Command137_Click() On Error GoTo Err_Find_Record_Click Me.CompanyName.SetFocus DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70 Exit_Find_Record_Click: Exit Sub Err_Find_Record_Click:
by: Presto731 | last post by:
I have a table that has mileage between two points. Each point has a unique id# in my query I want to be able to type in the id for point 1 and then type the id for point 2 and get the mileage. I know by simply putting in the criteria field for point one i can look up that one, how do make it so you enter both id#'s and get the mileage for those two points. any thoughts?
by: puzzled | last post by:
I have "Find" buttons throughout my database, each with focus on a different field (they were created with the wizard, then the focus set to the corresponding field with VB). If i press a find button and change the "Look In" combo to search the form instead of a field, the next "find" button i press defaults to 'Look In' the form, not the field that should have focus. The field that should have focus is available to select in the "Look In" combo...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.