Append Query to add unrelated info from one table to another ...

[Guest]

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.

 

[Alex Dybenko]

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

 

[Guest]

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*"));

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

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.

 

[Alex Dybenko]

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

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*"));

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

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.