I feel pretty lucky on my last SQL question.
I think I'll try one more...
If there is a table (tblCorresponde nce) with a field in it named
[OutType] and I am extracting a dynaset of records having an
[OutType] value of "06"...
-
Lets say the query displays [VehicleJobID], [OutType], [TF] and
[OutDate]. And lets say that for every record in the table having an
[OutType] value of "06", I would also like to know if there
is some other record anywhere in the table having an [OutType]
value of "14" and the same [VehicleJobID] #AND# if there IS
such a record, I would like to know its [OutDate] field value.
Is it possible that I can design one more field - a calculated field -
in the query that will display the [OutDate] field value if there is
ANY other record in tblCorresponden ce with the same [VehicleJobID]
value and an [OutType] value of "14"?
To clarify, lets say my table has many records, including these few...
Rec#78 [VehicleJobID] 1 [OutType] "06" [OutDate] #6/10/2005#
Rec#84 [VehicleJobID] 2 [OutType] "06" [OutDate] #6/11/2005#
Rec#89 [VehicleJobID] 3 [OutType] "06" [OutDate] #6/15/2005#
Rec#91 [VehicleJobID] 1 [OutType] "14" [OutDate] #6/17/2005#
Rec#93 [VehicleJobID] 2 [OutType] "04" [OutDate] #6/19/2005#
I would like to see the following in my query's dynaset
[VehicleJobID] 1 [OutType] "06" [TF] True [OutDate] #6/17/2005#
[VehicleJobID] 2 [OutType] "06" [TF] False [OutDate]
[VehicleJobID] 3 [OutType] "06" [TF] False [OutDate]
(I really could do without the [TF] field - its [OutDate] that I want)
Notice that the first line of the dynaset shows data from Rec #78 and
Rec #91 of tblCorresponden ce. Now that cool, if its useful. And it
will be highly useful to me.
The first record in the dynaset would display True in [TF] because
the source table has a record in it where [VehicleJobID] = 1 AND
[OutType] = "14". This is a tiny bit different than the question I
posed a bit earlier - to which Leigh Purvis provided an incredibly
eloquent solution. I've tried modifying his SQL to fit the new
objective, but have been unsuccessful thus far.
2  1484 
MLH wrote: I feel pretty lucky on my last SQL question.
I think I'll try one more...
If there is a table (tblCorresponde nce) with a field in it named
[OutType] and I am extracting a dynaset of records having an
[OutType] value of "06"...
-
Lets say the query displays [VehicleJobID], [OutType], [TF] and
[OutDate]. And lets say that for every record in the table having an
[OutType] value of "06", I would also like to know if there
is some other record anywhere in the table having an [OutType]
value of "14" and the same [VehicleJobID] #AND# if there IS
such a record, I would like to know its [OutDate] field value.
Is it possible that I can design one more field - a calculated field -
in the query that will display the [OutDate] field value if there is
ANY other record in tblCorresponden ce with the same [VehicleJobID]
value and an [OutType] value of "14"?
To clarify, lets say my table has many records, including these few...
Rec#78 [VehicleJobID] 1 [OutType] "06" [OutDate] #6/10/2005#
Rec#84 [VehicleJobID] 2 [OutType] "06" [OutDate] #6/11/2005#
Rec#89 [VehicleJobID] 3 [OutType] "06" [OutDate] #6/15/2005#
Rec#91 [VehicleJobID] 1 [OutType] "14" [OutDate] #6/17/2005#
Rec#93 [VehicleJobID] 2 [OutType] "04" [OutDate] #6/19/2005#
I would like to see the following in my query's dynaset
[VehicleJobID] 1 [OutType] "06" [TF] True [OutDate] #6/17/2005#
[VehicleJobID] 2 [OutType] "06" [TF] False [OutDate]
[VehicleJobID] 3 [OutType] "06" [TF] False [OutDate]
(I really could do without the [TF] field - its [OutDate] that I want)
Notice that the first line of the dynaset shows data from Rec #78 and
Rec #91 of tblCorresponden ce. Now that cool, if its useful. And it
will be highly useful to me.
The first record in the dynaset would display True in [TF] because
the source table has a record in it where [VehicleJobID] = 1 AND
[OutType] = "14". This is a tiny bit different than the question I
posed a bit earlier - to which Leigh Purvis provided an incredibly
eloquent solution. I've tried modifying his SQL to fit the new
objective, but have been unsuccessful thus far.
Try:
SELECT VehicleJobID, OutType, (SELECT FIRST(OutDate) FROM
tblCorresponden ce AS tblC WHERE tblC.VehicleJob ID =
tblCorresponden ce.VehicleJobID AND tblC.OutType = '14') AS OutDate FROM
tblCorresponden ce WHERE OutType='06';
or even:
SELECT VehicleJobID, OutType, (SELECT IIf(Count(OutDa te) <> 0,
First(OutDate) & ' NumberOfDates:' & Count(OutDate), Null) FROM
tblCorresponden ce AS tblC WHERE tblC.VehicleJob ID =
tblCorresponden ce.VehicleJobID AND tblC.OutType = '14') AS OutDate FROM
tblCorresponden ce WHERE OutType = '06';
James A. Fortune
EXCELLENT, Jim. Worked like a charm.
Many thx!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Justin Lebar |
last post by:
Sorry about the huge post, but I think this is the amount of
information necessary for someone to help me with a good answer.
I'm writing a statistical analysis program in ASP.net and MSSQL7 that
analyzes data that I've collected from my business's webpage and the
hits it's collecting from the various pay-per-click (PPC) engines.
I've arrived at problems writing a SQL call to generate certain
statistics.
Whenever someone enters our...
|
by: Neil Ginsberg |
last post by:
We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC
linked tables. I recently created a new set of tables for the app, and users
are complaining that unsaved data is being lost when they move to a new
record. This seems to be the case when there are multiple users. When there
is a single user using it, we don't seem to have that problem.
It seems that we had this problem when we first converted from an MDB back
end...
|
by: Catch_22 |
last post by:
Hi,
I'm extracting data from a mainframe application with a view to loading
it into a MS SQL database. I'm trying to determine the most efficient
way to format the mainframe extract file to make loading into the
database easier.
The problem I have is that the existing record structure includes an
array that can vary between 1 to 50. If I include this array in a
single record the table I use to import the data would need 50 columns
|
by: S. Graefner |
last post by:
I have been unable to locate anywhere the answer to my problem. I
would like to be able to download/import a single record. This record
is stored in many different related (one to one) tables. I have saved
a single record to Excell and then to a floppy by using a query, to
save the parts of the record I want then "Save as" "Excell". The
problem is being able to take it from the floppy/USB drive and import
it to the same, but in a different...
|
by: MLH |
last post by:
I have tblVehicles, tblOwners, tblProxies, tblLienholders.
In the vehicles table are and identifying
owner and proxy (generally an attorney). Vehicles table is
child table to tblOwners and tblProxies. tblLienholders has
and is a child table to vehicles table.
As you can imagine, owners, proxies and lienholders houses
names & addresses of persons/companies. At some point, I need
to extract a dynaset of vehicles and these other...
| | |
by: kenduron |
last post by:
Hello together!
How would you manage the following termin:
I got table "A" and table "B"
I have to show most of the fields in "A", an one columm in table "B".
But table "B" can have several entries which belongs to A.
|
by: Charles Law |
last post by:
Here's one that should probably have the sub-heading "I'm sure I asked this
once before, but ...".
Two users are both looking at the same data, from a database. One user
changes the data and commits it. How does the other user get the updated
view without polling for changes? Is there some sort of callback mechanism
that can be set up on the dataset or connection?
TIA
|
by: Bill Hutchison |
last post by:
I have a query that returns different results (3508 rows for snapshot,
6288 for dynaset) and that is the only thing I change to get the
different results. When I try to make a table from the query, it
makes 3508 rows. I also know that there are 6288 unique rows in the
results. Is there some configuration choice that is causing the
snapshot version to return the wrong row count?
Thanks for your help on this one. If it wasn't that the...
|
by: hippomedon |
last post by:
Hello everyone,
I'm looking for some advice on whether I should break the
normalization rule. Normally, I would not consider it, but this seems
to be a special case.
I have created an "Outcomes Database" used to store response data
from measures/ questionnaires for a longitudinal health study. It is
essentially derived from Duane Hookom's Survey Database (thanks
Duane!!!), with many modifications added to fit the needs of my lab.
|
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: 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...
| | |
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: 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...
|
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: 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...
|
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...
| |
