If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append Query to add unrelated info from one table to another ...
I have 1 table (Called Paths) with data that has 2 columns of data [ID
(Primary Key), Source Path] -- the other table is my (Import Buget) table (from my EXCEL spreadsheet), which does not contain the field Source Path. Here's what I'm trying to do ... I created an Append Query with the Import Budget table, and Paths table -- since there isn't a common field in both tables, I'm having a problem getting the Path (from the Paths table) to fill in each row of the Import Budget table. QUESTION: How do you append an unrelated field from one table to another when there is no corresponding (LIKE) field in the destination table? NOTE: I'm not trying to populate ALL the rows from the Paths table into the Import Budget table, only the ones with the set criteria (Like "*1759") If I don't include a join, I get 30037 records appended, when there's only 613 records in the destination -- If I do a join on ID, I get zero records appended, since the ID in Import Budget & Paths is AutoNumber, and there are 613 records in Import Budget and 49 records in Paths. I know there's got to be some type of Join that will allow for a Foreign Join (not sure is a one-to-many will get me there) Any help on this will be greatly appreciated -- thanks in advance. |
#2
|
|||
|
|||
Append Query to add unrelated info from one table to another ...
First of all, please confine your question to one or POSSIBLY two
groups. QUESTION: How do you append an unrelated field from one table to another when there is no corresponding (LIKE) field in the destination table? You can't... Ask yourself this: If this information was on two sheets of paper, how would I know how to do the look up? Is there some sort of primary key in the Import Budget table? Either you could add that column to the Paths table, or possibly add a third table that has a cross reference to the Paths table primary key and the Import Budget table primary key HTH, mcescher wrote: I have 1 table (Called Paths) with data that has 2 columns of data [ID (Primary Key), Source Path] -- the other table is my (Import Buget) table (from my EXCEL spreadsheet), which does not contain the field Source Path. Here's what I'm trying to do ... I created an Append Query with the Import Budget table, and Paths table -- since there isn't a common field in both tables, I'm having a problem getting the Path (from the Paths table) to fill in each row of the Import Budget table. QUESTION: How do you append an unrelated field from one table to another when there is no corresponding (LIKE) field in the destination table? NOTE: I'm not trying to populate ALL the rows from the Paths table into the Import Budget table, only the ones with the set criteria (Like "*1759") If I don't include a join, I get 30037 records appended, when there's only 613 records in the destination -- If I do a join on ID, I get zero records appended, since the ID in Import Budget & Paths is AutoNumber, and there are 613 records in Import Budget and 49 records in Paths. I know there's got to be some type of Join that will allow for a Foreign Join (not sure is a one-to-many will get me there) Any help on this will be greatly appreciated -- thanks in advance |
#3
|
|||
|
|||
Append Query to add unrelated info from one table to another ...
What you say makes "perfect" sense, and that's the concept I've always used
in developing databases. I've been tasked to do something that's outside my scope (as far as what I've always understood). Is there (perhaps) some VBA code that will taked the selected data from the Paths table (based on the criteria) and populate each row in the Import Budget table. If so, what would it look like? Thanks in advance. "mcescher" wrote in message ups.com... First of all, please confine your question to one or POSSIBLY two groups. QUESTION: How do you append an unrelated field from one table to another when there is no corresponding (LIKE) field in the destination table? You can't... Ask yourself this: If this information was on two sheets of paper, how would I know how to do the look up? Is there some sort of primary key in the Import Budget table? Either you could add that column to the Paths table, or possibly add a third table that has a cross reference to the Paths table primary key and the Import Budget table primary key HTH, mcescher wrote: I have 1 table (Called Paths) with data that has 2 columns of data [ID (Primary Key), Source Path] -- the other table is my (Import Buget) table (from my EXCEL spreadsheet), which does not contain the field Source Path. Here's what I'm trying to do ... I created an Append Query with the Import Budget table, and Paths table -- since there isn't a common field in both tables, I'm having a problem getting the Path (from the Paths table) to fill in each row of the Import Budget table. QUESTION: How do you append an unrelated field from one table to another when there is no corresponding (LIKE) field in the destination table? NOTE: I'm not trying to populate ALL the rows from the Paths table into the Import Budget table, only the ones with the set criteria (Like "*1759") If I don't include a join, I get 30037 records appended, when there's only 613 records in the destination -- If I do a join on ID, I get zero records appended, since the ID in Import Budget & Paths is AutoNumber, and there are 613 records in Import Budget and 49 records in Paths. I know there's got to be some type of Join that will allow for a Foreign Join (not sure is a one-to-many will get me there) Any help on this will be greatly appreciated -- thanks in advance |
#4
|
|||
|
|||
Append Query to add unrelated info from one table to another ...
Hi,
not quite understand you, but if you need to join table using like - you can build a query with normal inner join, then switch it to SQL view and replace = in join expression with Like. perhaps you need to add * to your field value to make it looks like wildcard expression -- Best regards, ___________ Alex Dybenko (MVP) http://alexdyb.blogspot.com http://www.PointLtd.com wrote in message ... I have 1 table (Called Paths) with data that has 2 columns of data [ID (Primary Key), Source Path] -- the other table is my (Import Buget) table (from my EXCEL spreadsheet), which does not contain the field Source Path. Here's what I'm trying to do ... I created an Append Query with the Import Budget table, and Paths table -- since there isn't a common field in both tables, I'm having a problem getting the Path (from the Paths table) to fill in each row of the Import Budget table. QUESTION: How do you append an unrelated field from one table to another when there is no corresponding (LIKE) field in the destination table? NOTE: I'm not trying to populate ALL the rows from the Paths table into the Import Budget table, only the ones with the set criteria (Like "*1759") If I don't include a join, I get 30037 records appended, when there's only 613 records in the destination -- If I do a join on ID, I get zero records appended, since the ID in Import Budget & Paths is AutoNumber, and there are 613 records in Import Budget and 49 records in Paths. I know there's got to be some type of Join that will allow for a Foreign Join (not sure is a one-to-many will get me there) Any help on this will be greatly appreciated -- thanks in advance. |
#5
|
|||
|
|||
Append Query to add unrelated info from one table to another ...
Alex -- thanks for your reply -- this is the SQL that I'm using:
SELECT [Import Budget].ID1, [Import Budget].ID, [Import Budget].b, [Import Budget].c, Path.Data, Now() AS [date] FROM Path INNER JOIN [Import Budget] ON Path.ID = [Import Budget].ID1 WHERE (((Path.Data) Like "*S*")); "Alex Dybenko" wrote in message ... Hi, not quite understand you, but if you need to join table using like - you can build a query with normal inner join, then switch it to SQL view and replace = in join expression with Like. perhaps you need to add * to your field value to make it looks like wildcard expression -- Best regards, ___________ Alex Dybenko (MVP) http://alexdyb.blogspot.com http://www.PointLtd.com wrote in message ... I have 1 table (Called Paths) with data that has 2 columns of data [ID (Primary Key), Source Path] -- the other table is my (Import Buget) table (from my EXCEL spreadsheet), which does not contain the field Source Path. Here's what I'm trying to do ... I created an Append Query with the Import Budget table, and Paths table -- since there isn't a common field in both tables, I'm having a problem getting the Path (from the Paths table) to fill in each row of the Import Budget table. QUESTION: How do you append an unrelated field from one table to another when there is no corresponding (LIKE) field in the destination table? NOTE: I'm not trying to populate ALL the rows from the Paths table into the Import Budget table, only the ones with the set criteria (Like "*1759") If I don't include a join, I get 30037 records appended, when there's only 613 records in the destination -- If I do a join on ID, I get zero records appended, since the ID in Import Budget & Paths is AutoNumber, and there are 613 records in Import Budget and 49 records in Paths. I know there's got to be some type of Join that will allow for a Foreign Join (not sure is a one-to-many will get me there) Any help on this will be greatly appreciated -- thanks in advance. |
#6
|
|||
|
|||
Append Query to add unrelated info from one table to another ...
ok,
perhaps better answer would be the following - you can use access "Find unmatched query wizard" to get unrelated records, and then run append query, base on this one -- Best regards, ___________ Alex Dybenko (MVP) http://alexdyb.blogspot.com http://www.PointLtd.com wrote in message ... Alex -- thanks for your reply -- this is the SQL that I'm using: SELECT [Import Budget].ID1, [Import Budget].ID, [Import Budget].b, [Import Budget].c, Path.Data, Now() AS [date] FROM Path INNER JOIN [Import Budget] ON Path.ID = [Import Budget].ID1 WHERE (((Path.Data) Like "*S*")); "Alex Dybenko" wrote in message ... Hi, not quite understand you, but if you need to join table using like - you can build a query with normal inner join, then switch it to SQL view and replace = in join expression with Like. perhaps you need to add * to your field value to make it looks like wildcard expression -- Best regards, ___________ Alex Dybenko (MVP) http://alexdyb.blogspot.com http://www.PointLtd.com wrote in message ... I have 1 table (Called Paths) with data that has 2 columns of data [ID (Primary Key), Source Path] -- the other table is my (Import Buget) table (from my EXCEL spreadsheet), which does not contain the field Source Path. Here's what I'm trying to do ... I created an Append Query with the Import Budget table, and Paths table -- since there isn't a common field in both tables, I'm having a problem getting the Path (from the Paths table) to fill in each row of the Import Budget table. QUESTION: How do you append an unrelated field from one table to another when there is no corresponding (LIKE) field in the destination table? NOTE: I'm not trying to populate ALL the rows from the Paths table into the Import Budget table, only the ones with the set criteria (Like "*1759") If I don't include a join, I get 30037 records appended, when there's only 613 records in the destination -- If I do a join on ID, I get zero records appended, since the ID in Import Budget & Paths is AutoNumber, and there are 613 records in Import Budget and 49 records in Paths. I know there's got to be some type of Join that will allow for a Foreign Join (not sure is a one-to-many will get me there) Any help on this will be greatly appreciated -- thanks in advance. |
Thread Tools | |
Display Modes | |
|
|