Is there a way to join 2 tables where the 2 fields on which the tables joined are of different data types(char and varchar)?I tried with CONVERT but is does not give the desired output.
My requirement is to get the minimum (earliest) Posted date along with two other fields.But when i join the two fields I get the duplicate records as well.
Following is my query.
---------------------------------------------------------------------------------------------------------
SELECT DISTINCT RMS_T_CV_SENT_I NFORMATION.ReqR efID,
T.FirstName,MIN (RMS_T_CV_SENT_ INFORMATION.CVP ostedDate) AS Posteddate FROM
RMS_T_CV_SENT_I NFORMATION JOIN
(SELECT DISTINCT RMS_T_CV_INFORM ATION.FirstName ,RMS_T_CV_INFOR MATION.CVRefID
FROM RMS_T_CV_INFORM ATION GROUP BY RMS_T_CV_INFORM ATION.FirstName ,RMS_T_CV_INFOR MATION.CVRefID) AS T
ON CONVERT(varchar (11),SUBSTRING( T.CVRefID,1,11) )=CONVERT(varch ar(11),SUBSTRIN G(RMS_T_CV_SENT _INFORMATION.CV ID,1,11))
GROUP BY RMS_T_CV_SENT_I NFORMATION.ReqR efID,T.FirstNam e
ORDER BY RMS_T_CV_SENT_I NFORMATION.ReqR efID
---------------------------------------------------------------------------------------------------------
In the above ,the RMS_T_CV_SENT_I NFORMATION.CVID and RMS_T_CV_INFORM ATION.CVRefID are the related fields but one is of varchar(15) and other field is of Char(15).
I m sorry ,if this is too lenthy and confusing.
thanks in advance.
4 11197
Not knowing really what your data looks like and I am confused why you are joining to recordsets. Can you give some additional details, maybe some comments on why u r doing what u r doing.
hi
you must create ' views' or 'stored procedure with dataset returning' for each section of your select statement then you can use cast() function for changing the type of your output column in that view. then join that views or storedprocedure s in anaother select statement to output the dataset you need.
you must create ' views' or 'stored procedure with dataset returning' for each section of your select statement then you can use cast() function for changing the type of your output column in that view. then join that views or storedprocedure s in anaother select statement to output the dataset you need.
Actually, if you are doing it in a stored procedure you could create temp table that holds everything and output that data.
Something along this lines... - DECLARE @Temp TABLE(
-
,CVID varchar(11)
-
,ReqRefId int
-
,firstname varchar(20)
-
,posteddate datetime)
-
-
INSERT @Temp(CVID, ReqRefId,posteddate)
-
SELECT DISTINCT CAST(SUBSTRING(CVID,1, 11) AS varchar(11)), ReqRefID, MIN(CVPostedDate)
-
FROM RMS_T_CV_SENT_INFORMATION
-
-
UPDATE @Temp
-
SET firstname=a.FirstName
-
FROM RMS_T_CV_SENT_INFORMATION a
-
INNER JOIN @Temp b
-
ON b.CVID=a.CVRefID
-
-
SELECT * FROM @Temp
-
Thanks for the replies.I tried creating stored procedures but that doesn't filter the correct records either.
Requirement: Get the minimum posted date ,with the CV holder's name per Requirement Id(ReqRefId)
Fields from RMS_T_CV_SENT_I NFORMATION table :ReqRefID,CVPos tedDate
Fields from RMS_T_CV_INFORM ATION table :FirstName
Common fields are:RMS_T_CV_IN FORMATION.CVRef ID(char 15) and RMS_T_CV_SENT_I NFORMATION.CVID (varchar 15)
---------------------------------------------------------------------------------------------------------------
SELECT DISTINCT RMS_T_CV_SENT_I NFORMATION.ReqR efID,RMS_T_CV_I NFORMATION.Firs tName, MIN(RMS_T_CV_SE NT_INFORMATION. CVPostedDate) AS PostedDate
FROM RMS_T_CV_SENT_I NFORMATION
INNER JOIN RMS_T_CV_INFORM ATION ON
CONVERT(char(15 ),RMS_T_CV_INFO RMATION.CVRefID )=CONVERT(char( 15),RMS_T_CV_SE NT_INFORMATION. CVID)
GROUP BY RMS_T_CV_SENT_I NFORMATION.ReqR efID,RMS_T_CV_I NFORMATION.Firs tName,RMS_T_CV_ SENT_INFORMATIO N.CVPostedDate
ORDER BY RMS_T_CV_SENT_I NFORMATION.ReqR efID
-------------------------------------------------------------------------------------------------------------------
There are 5 other tables too involved in the query with same data mismatch issues.But at least if this part works i could apply the same theory for those as well.The database contains no relationships or primary keys.(I am sorry, it's the worst database created by a fresher).
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Dave M |
last post by:
All
I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.
There are several different types of widgets. Not all companies sell or
report all types of widgets.
We want to report how many companies have reported or not reported their
|
by: James |
last post by:
Can anyone please shed some light on the following...
I have a framework that uses dynamically created tables, named using an
incremental "attribute set ID", as follows:
attrdata_1
attrdata_2
attrdata_3
etc, etc...
|
by: Random Person |
last post by:
Does anyone know how to use VBA to relink tables between two MS Access
databases? We have two databases, one with VBA code and the other with
data tables. The tables are referenced by linked tables in the database
where the code resides. If we move the database with the data tables to
a new directory, the links are no longer valid.
I tried to update the links by changing the Connect property and refreshing:
Set td = db.TableDefs(0)...
|
by: Koen |
last post by:
Hi all,
My application uses a lot of lookup tables.
I've splitted the frontend (forms, reports, etc) from the backend (data).
The database has around 10 different users.
The values in the lookup tables are not likely to change.
Question 1: Should I include them in the backend (with rest of data) or the
frontend?
|
by: Stephen |
last post by:
It is possible to relate queries to tables, right? It seems logical
but when I try to match my queries to any of the tables or even to
each other it gives me a blank relationship. What could I possibly be
doing wrong on this?
Thanks for the help,
Stephen
| |
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my database,just help in getting me pointed in
the right direction.
I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then...
|
by: Reader |
last post by:
Hello all,
I am joining two tables in a query and the output should be all those
records where two fields in table1 match two corresponding fields in
table2.
I joined the tables using both fields in design view and the Select
statement in SQL view looks good. The query runs perfectly and shows
the result I want but when I save the query and close it, re-opening in
design view shows the error message "Microsoft Office Access can't
|
by: Robert |
last post by:
How do you get an accurate count of the number of records returned from
a query when using linked tables.
I have an access 2003 database as a front end to another access 2003
database that contains only tables. I have linked the tables for the
front end to the back end database.
I am trying to set the recordsource of a form to a query established by
the user to narrow the scope but I don't want to display the form if
there are no...
|
by: nkechifesie |
last post by:
I have created 9 tables in access via visdata and need to create another table joining all these tables. I have used the query in visdata to do this but it gives me an outrageous view. I have entered 2 records in each table but it gives me 65 records in the query. Please could you help me out. This is the code genreated by Visdata after I created the query, please what is wrong.
the common field in 8 of the tables is Vehicle no and the common...
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: 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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |