SQL query question - Microsoft Access / VBA

Trey

How do I tell an update query to only search the records that are older then
the current record?

I need to figure out how to write a query that will take a record, look to
see if any record with an older date and time has the same address, then
marks a yes/no box to YES if it does not find a duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any help would
be very much appreciated!

Nov 13 '05 #1

Subscribe Reply

1885

Trey

Forgot to mention, This is in Access 2000.

Trey wrote:

How do I tell an update query to only search the records that are
older then the current record?

I need to figure out how to write a query that will take a record,
look to see if any record with an older date and time has the same
address, then marks a yes/no box to YES if it does not find a
duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any help
would be very much appreciated!

Nov 13 '05 #2

John Winterbottom

"Trey" <tr***********@ hotmail.com> wrote in message
news:Sv******** *********@twist er.socal.rr.com ...

How do I tell an update query to only search the records that are older then the current record?

I need to figure out how to write a query that will take a record, look to see if any record with an older date and time has the same address, then
marks a yes/no box to YES if it does not find a duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any help would be very much appreciated!

Normally it would take a subquery - unfortunately that doesn't work in Jet.
You need to use domain aggregate functions or "queries-feeding-queries".
Give us some more info and someone will be able to write the sql for you.

Nov 13 '05 #3

iuliegriffiths

try in "Record Created" fleld run the query like "[date <*]" and Is Not
Null
try

if NO ........ then
txtFledName = yes in VBA

Trey" <tr***********@ hotmail.com> wrote in message
news:Sv******** *********@twist er.socal.rr.com ...
How do I tell an update query to only search the records that are older then
the current record?

I need to figure out how to write a query that will take a record, look
to
see if any record with an older date and time has the same address, then
marks a yes/no box to YES if it does not find a duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any help would
be very much appreciated!

Nov 13 '05 #4

Trey

John Winterbottom wrote:

"Trey" <tr***********@ hotmail.com> wrote in message
news:Sv******** *********@twist er.socal.rr.com ...
How do I tell an update query to only search the records that are
older then the current record?

I need to figure out how to write a query that will take a record,
look to see if any record with an older date and time has the same
address, then marks a yes/no box to YES if it does not find a
duplicate.

I know this can be done, I just don't know how.

I know it will take a subquery of some kind. I am just lost. Any
help would be very much appreciated!

Normally it would take a subquery - unfortunately that doesn't work
in Jet. You need to use domain aggregate functions or
"queries-feeding-queries". Give us some more info and someone will be
able to write the sql for you.

I have a table named "main". then I have a query named "12month", this query
selects only the records in the table that are 12 months old and newer. From
this point on, that is the only data that I will be working with for this
operation.

SQL for 12month:
SELECT DateDiff('m',[date_called],Date()) AS expr1, [main].[date_called],
[main].[call_count], [main].[Address], [main].[Zip], [main].[Billable],
[main].[Bill_sent], *
FROM main
WHERE (((DateDiff('m' ,[date_called],Date()))<=12))
ORDER BY [main].[date_called], [main].[Address];
---------------
This part works great. But now comes the update query that I am having
trouble with.
I have some ugly bastardized SQL that I have glued together. This update
query is able to find any records that do not have duplicate addresses, and
mark the warning = yes.

UPDATE 12month SET 12month.Warning = Yes
WHERE [12month].address = (SELECT [12month].Address
FROM 12month WHERE ((([12month].Address) In (SELECT [Address] FROM [12month]
As Tmp GROUP BY [Address] HAVING Count(*)<=1 )))
ORDER BY [12month].Address);
-------------
However, it searches all the records, not just the records that are older
then the current record. So instead of looking at a record, seeing if an
OLDER duplicate address exists, and marking the warning = yes accordingly,
it searches all the records. And if any duplicates exist (including new
duplicates) then it wont mark it.

I am afraid I did not explain this correctly, but this is the best I can do
with my limited SQL knowledge.

Thanks in advance for your help.

Nov 13 '05 #5

Similar topics

3406

Fine tuning this sql-query. help!

by: majsen | last post by:

Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in a triangle. Like this member A -> B->C A give his appartment to B. B gives his appartment to C and finally C gives his appartment to A Soo my query looks for matching parameters like rooms, location, size

Microsoft SQL Server

3271

Query performance question on a large table

by: Együd Csaba | last post by:

Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run simultaneously 4 similar queries it takes nearly 5 minutes instead of 4 times 9 seconds or something near of that. here is a sample query: select mertido, fomeazon, ertektipus, mertertek from t_me30 where fomeazon in (select distinct fomeazon...

PostgreSQL Database

14127

Pulling the Record Number into a Query - Attempt 2

by: John Ortt | last post by:

> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record (and store it in field 2). > > I would like to run a query that returns all the data in the table plus the > record number, in a similar sense to the getuser() command to get the User's > Identity, I would like a GetRecord() command.

Microsoft Access / VBA

3090

Filling question ID automatically by using query or VBA

by: Ekqvist Marko | last post by:

Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table before rowID answID qryrow questionID datafield 1591 12 06e 06e 06e question 1593 12 06f 06f 06f question 1594 12 answer to the question 06f

Microsoft Access / VBA

3391

1 SP with dynamic input parameters and multiple rows as the source of the query

by: serge | last post by:

How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always either AND or OR but never mixed together. We can use Northwind database for my question, it is very similar to the structure of the problem on the database I am working on. IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL DROP TABLE REPORT_SELECTION

Microsoft SQL Server

4849

Use Select Query variable against a table and return value to same select query

by: jjturon | last post by:

Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time

Microsoft Access / VBA

2040

Help trying to limit the results on a 3-table query?

by: mmitchell_houston | last post by:

I'm working on a .NET project and I need a single query to return a result set from three related tables in Access 2003, and I'm having trouble getting the results I want. The details: Question ------------ QuestionID QuestionText Question_MediaTypeID

Microsoft Access / VBA

31211

Query for Month

by: Stan | last post by:

I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for June, and will return all records in that month.

Microsoft Access / VBA

2063

How to use a saved cross-tab query in a report?

by: Richard Hollenbeck | last post by:

I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few parameters...." I have read many articles on the web about how to make a dynamic report based on a cross-tab query. But for some reason mine never works right. First, my saved query's criteria is the data in an open form's combo box. So the...

Microsoft Access / VBA

3519

duplicates query help & strategy for update queries with SetWarnings = False

by: ARC | last post by:

Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...

Microsoft Access / VBA

9423

Changing the language in Windows 10

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,...

Windows Server

10214

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9996

The easy way to turn off automatic updates for Windows 10/11

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,...

Windows Server

9865

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8872

AI Job Threat for Devs

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...

Career Advice

6674

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5304

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

3963

transfer the data from one system to another through ip address

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

C# / C Sharp

2815

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

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...

General