|
I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together?
For example:
Table1:
Name Address
---------------------
John 302 ABC Street
Smith 412 DEF Street</code>
Table2:
Phone email
----------------------
123-4567 m1@hotmail.com
234-5678 s1@gmail.com
Can I use a simple SQL command to create:
Table3:
Name Address Phone email
--------------------------------------------------
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.com
I was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records.
Is there any way to achieve this just by using SQL commands without resort to writing a custom program?
Thanks!
|
|
|
|
|
I'm wondering what you'd do if you had to 'resort to writing a custom program?' - since as you say, they are (I'll use the terms) disjoint/un-related instead of your 'irrelavent'
since you dont have anything in common between the tables, and as you say, you cant join by record numbers the only way I can see of doing this (which assumes you can see record numbers) is to manually create a 'link' table with two columns, record-number-table-1, record-number-table-2, and manually enter the pairs of corresponding record numbers into it using 'eyeball matching' and 'insert' statements - of course, if you have a truckload of data this is going to take a while. You then select record from each table based on the rows in the 'link' table
There must be more to the situation than you're showing us here - To 'write a custom program' implies you know of some relationship between the tables/data, but just because they 'have the same amount of records' is a bad assumption - what about duplicates for example ?
'g'
|
|
|
|
|
Hi,
if everything else fails, you could add a field to both tables ("ID") and fill it with sequential numbers.
Then join both tables on the new field.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Try this
declare @tbl1 table(name varchar(20), address varchar(50))
insert into @tbl1
select 'John','302 ABC Street' union all select 'Smith','412 DEF Street'
declare @tbl2 table(phone varchar(20), email varchar(50))
insert into @tbl2
select '123-4567','m1@hotmail.com' union all select '234-5678','s1@gmail.com'
;with cte1 as (select t1.*, ROW_NUMBER()over (order by name) as rn from @tbl1 t1)
,cte2 as (select t2.*, ROW_NUMBER()over (order by phone) as rn from @tbl2 t2)
select c1.name,c1.address,c2.phone, c2.email
from cte1 c1
inner join cte2 c2
on c1.rn = c2.rn
The output is
name address phone email
John 302 ABC Street 123-4567 m1@hotmail.com
Smith 412 DEF Street 234-5678 s1@gmail.com
But ur database design is wrong.. normalize that please.
I use the Row_number() function to accomplish the task....
Niladri Biswas
|
|
|
|
|
You cannot do this in Access 
|
|
|
|
|
First of all, thanks for all who replied.
I was given those Access tables and was asked to do that "horizontal join". I did not design the database, so I have no control over the design of the tables. For example, there is no primary key in those tables. Had I designed them, I would probably put a rowid field there as the primary key.
The person who asked me to do this needed the result table "so that he can load the table in GeoMedia to map the points." I don't know the details about GeoMedia. I just do whatever he asks me to do. He is my boss. The real requirement is more than what I presented here. He actually asked me to horizontally join more than two tables. Actually the number of tables are unknown at design time. I am actually writing a program to do this. However, I want a query to simplify my program. I was hoping that I can avoid the result table creation part because I don't want to deal with Access data types. If a query is possible, I will just use "select ... into ..." to create the result table, instead of using "create table ..." because to construct the create command I have to find out the fields' info (name and type) for the result table.
Thanks again for all your replies.
|
|
|
|
|
Add a new AutoNumber column to both these tables and use it to join the tables.
|
|
|
|
|
Hi friends.
I have problem reading images from ms access database!
there is a clinic software who was written by a bankrupted company,
and now it doesn’t work by the some reason, and there is no support on this software,
All things that I have to do are recover the patient’s data that was written by doctor handwriting in his notebook tablet-pc.
let me describe a little...
software has a feature that allow to the doctor to write the patients information by hand writing with an electrical pen, it so like paint brush software and it save this information in the database like bellow...
I think the doctor's handwriting must be saving as images in the patient’s records. The problem is I couldn’t read it from database because the data are like this:
AIfIAR0E2A/uBgIZAkgRBUgRRP8BBEgQRTUKSBFXDQAAAFD/AQQDATgAGCIgMgkA9P8CAekiOkUzCQD4nwIBWiA6RTgIAP4DAAAAgH8ADw4S+SONP88jjT8RAACAPx4+4gKD/glK/glJAAAAAAAAAAAYyAAAAAAAAAAAAAAAAAiQIkAAAAAAVMgAAAAESAEJAAAAAAxkAAAAAAIfEAAKLw+E+44+492VloilKFtOjSCG4PDhA6KAjpKCkI
I think images must be converted from image to another type like String Base 64 format.
i attached the database file here/:
http://rapidshare.com/files/302706380/test.accdb.html
there is a tables in the database, and all thing that i need is the "VISIT Table" 's information on the column by the name "NOTES", i think it must be image.
HELP ME,THis IS UrGENT Problem!
thank you very much!
With best Regards!
|
|
|
|
|
Dear Friends,
Well i found that, it's definitely a String Base 64, 'cause I was able to build a byte array from it with this code:
string strData = dataGridView1[2, 0].Value.ToString();//this from visit table in sample database that i //attached
byte[] data = Convert.FromBase64String(strData);
and i use this :
public Image ByteToImageConvert(byte[] byteArrayIn)
{
MemoryStream ms = new MemoryStream(byteArrayIn);
Image returnImage = Image.FromStream(ms);
return returnImage;
}
but i face with this problem on " Image returnImage = Image.FromStream(ms);":
Parameter is not valid.
What should i do?
PLease HELP!
thank you!
|
|
|
|
|
This question has been answered in the C# forum. Please don't post more than once.
|
|
|
|
|
Hi,
I am working on SQL Server 2005.
Here I need to pass an array (2 dimensional, E.g: Emp No, Emp Name ) having multiple records from my ASP.NET application to a Stored Procedure.
Would you please let me know if there is a way to do it?
Many Thanks,
Regards.
Anusha.
|
|
|
|
|
Anusha.1980 wrote: Would you please let me know if there is a way to do it?
One way would be by using XML[^]
I are Troll 
|
|
|
|
|
|
Hi,
I am working on SQL Server 2005 tables. Here I need to add a column named ‘ID’ as ‘IDENTITY’ column (With starting and incrementing values as 1, 1).
Now my problem is that these tables already have thousands of records. So could you please suggest the best and easy way to perform this job?
Many Thanks,
Regards.
Anusha.
|
|
|
|
|
Just add the field, SQL server will insert the ID values for existing records (It does in 2008 and I think it did in 2005). If not add the field, use row_number() to update the value and then add the identity constraint.
|
|
|
|
|
Suppose I have a table
declare @tbl1 table(name varchar(20), address varchar(50))
insert into @tbl1
select 'John','302 ABC Street' union all select 'Smith','412 DEF Street'
select * from @tbl1
The output is
name address
John 302 ABC Street
Smith 412 DEF Street
Now I want to insert identity
Change the table definition by adding an identity field like this
declare @tbl1 table(id int identity,name varchar(20), address varchar(50))
The output is
id name address
1 John 302 ABC Street
2 Smith 412 DEF Street
Else go ahead with the Row_Number() function(since sql server 2005 onwards)
select ROW_NUMBER() over(order by name) id, * from @tbl1
Niladri Biswas
|
|
|
|
|
Just add the IDENTITY column, SQL Server will fill the values automatically.
|
|
|
|
|
I have 2 tables, Products and Receipts. In need to select all rows in the Products table and link it to the Receipts tables where the ReceiptId equals a given value.
Products Table
---------------
ProductId BIGINT
Receipts Table
--------------
ReceiptId BIGINT
ProductId BIGINT
Quantity DECIMAL(15,4)
If I execute a SELECT query on the products table I get 126 rows returned
If I execute a SELECT query on the receipts table WHERE ReceiptId = 330804 , I get 2 rows.
I created a new view as follows
SELECT TOP (100) PERCENT dbo.Products.ProductId, dbo.Receipts.ReceiptId, dbo.Receipts.Quantity
FROM dbo.Products LEFT OUTER JOIN
dbo.Receipts ON dbo.Products.ProductId = dbo.Receipts.ProductId
Then if I perform a SELECT query on the new view as follows :-
SELECT * FROM vuProductReceipts WHERE ReceiptId = 330804 OR ReceiptId IS NULL
I would expect to get 126 rows returned, but I am only getting 63.
Any help would be appreciated.
Thanks
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Steven J Jowett wrote: If I execute a SELECT query on the receipts table WHERE ReceiptId = 330804 , I get 2 rows.
Steven J Jowett wrote: SELECT * FROM vuProductReceipts WHERE ReceiptId = 330804 OR ReceiptId IS NULL
Steven J Jowett wrote: I would expect to get 126 rows returned, but I am only getting 63.
Based on the above three asserions, im guessing there are 61 products without any receipts.
What's your question?
|
|
|
|
|
Hi,
But I have used your code only and is getting the correct result.
declare @tblProduct table(Productid int )
insert into @tblProduct select 1 union all select 2 union all select 3 union all
select 4 union all select 5
declare @tblReceipts table(Receiptid int,Productid int,Quantity decimal )
insert into @tblReceipts
select 1,1,20 union all
select 1,1,30 union all
select 2,1,40 union all
select 2,1,12 union all
select 3,1,45 union all
select 2,2,45 union all
select 1,1,20 union all
select 1,1,30 union all
select 2,1,40 union all
select 2,1,12 union all
select 2,1,45 union all
select 2,2,45
select ProductId,ReceiptId,Quantity from
(SELECT TOP (100) PERCENT p.ProductId, r.ReceiptId, r.Quantity
FROM @tblProduct p LEFT JOIN
@tblReceipts r
ON p.ProductId = r.ProductId ) X(ProductId,ReceiptId,Quantity)
where X.ReceiptId = 2 OR X.ReceiptId is NULL
The output is as desired:
ProductId ReceiptId Quantity
1 2 40
1 2 12
1 2 40
1 2 12
1 2 45
2 2 45
2 2 45
3 NULL NULL
4 NULL NULL
5 NULL NULL
So what is ur doubt? Can u give us some datapoints so that we can give a better insight!
Niladri Biswas
|
|
|
|
|
You can't really figure out what's going on here without knowing more about the data that is actually in the tables.
There are two rows in the Receipts table with ReceiptId 330804. Do these have the same ProductId or different ProductIds?
My guess is that there are two products for receipt id 330804 and 61 products with no receipt id. Total, 63 rows in your result set.
|
|
|
|
|
I have a table called products with a unique id column called ProductId.
The products table contains 124 rows.
I have another table called receipts containing
ReceiptId
ProductId
Quantity
The ProductId would contain a ProductId from the Products table.
So what I want is a list of 124 products and the quantities received next to each product with a particular ReceiptId
Basically
ProductId Quantity
1.........10
2.........NULL
3.........NULL
4.........5
5.........NULL
NULL being where there is no receipt
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
|
Dave
Thanks for the help.
My solution is as follows :-
SELECT NULL as TicketId, *, NULL as Quantity
FROM vuDepotProducts
WHERE NOT EXISTS(
SELECT * FROM vuReceipts WHERE DepotId = vuDepotProducts.DepotId AND ProductId = vuDepotProducts.ProductId AND TicketId = 330804
)
UNION
SELECT * FROM vuReceipts WHERE vuReceipts.TicketId = 330804
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Hi
I am trying to import a CSVs file using OPENROWSET and import this into a database
my problem is the CSV files may contain a varying number of columns
i have tried using
SELECT BulkColumn
FROM OPENROWSET (BULK '\\10.1.2.107\rp_uploaded_files\file.csv', SINGLE_CLOB) MyFile
but this puts all the values into one fields
i have also tried
set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
exec (@string)
but this requires the destintation table to have the same number of columns as the import file
Can anyone advise
thanks
Simon
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
