|
 |
access thread: Separating Combined Table
Message #1 by "Hugh Anderson" <webmaster@u...> on Tue, 22 May 2001 02:55:42
|
|
I have the following problem with an Access 2000 database. I mistakenly
combined two separate tables into one. One of the separate tables listed
HTML subscribers, the other listed text letter subscribers. I still have
the separate tables. It seems to me it should be possible query or program
in such a way that records in the combined table that equal a record in
the HTML table would be identified - and similarly for the text table. But
I can't figure out how to do it.
Anybody have any ideas, please?
Message #2 by "Susie Eades" <Susie.Eades@a...> on Tue, 22 May 2001 9:09 -0500
|
|
Not exactly sure what records you want to leave in the 3rd (combined) table, if
any, but you can write a query to identify which ones in the combined table are
from each of your other 2 tables.
Assume Tbl 1 = HTML subscribers, Tbl 2=Text Letter subscribers, & Tbl 3
combination of Tbls 1& 2. To find the records in Tbl 3 that equal records in
Tbl 1, I believe you would use Tbl 1 & Tbl 3, joining the field(s) that would
uniquely identify a record (such as a Subscriber ID unless it's an autonumber
field, in which case you could used something like a combination of name &
address). Join them to include only rows where the joined fields are equal.
Select all fields(*) from Tbl 1. That should return all the HTML subscribers'
data. You can then delete the HTML records from Tbl 3. Then do the same using
Tbl 2 & Tbl 3. Hope that works for you.
Message #3 by "Pardee, Roy E" <roy.e.pardee@l...> on Tue, 22 May 2001 07:09:29 -0700
|
|
I believe a straightforward query will do the trick. Add first your
separate, then your combined table, and join them on a common field (e.g.,
e-mail address) by dragging the field from one table onto another.
HTH,
-Roy
-----Original Message-----
From: Hugh Anderson [mailto:webmaster@u...]
Sent: Monday, May 21, 2001 7:55 PM
To: Access
Subject: [access] Separating Combined Table
I have the following problem with an Access 2000 database. I mistakenly
combined two separate tables into one. One of the separate tables listed
HTML subscribers, the other listed text letter subscribers. I still have
the separate tables. It seems to me it should be possible query or program
in such a way that records in the combined table that equal a record in
the HTML table would be identified - and similarly for the text table. But
I can't figure out how to do it.
Anybody have any ideas, please?
Message #4 by "Hugh Anderson" <webmaster@u...> on Wed, 23 May 2001 03:24:53
|
|
It certainly did the trick, lke a charm. Thanks to everybody.
Hugh Anderson
> I believe a straightforward query will do the trick. Add first your
> separate, then your combined table, and join them on a common field
(e.g.,
> e-mail address) by dragging the field from one table onto another.
>
> HTH,
>
> -Roy
>
> -----Original Message-----
> From: Hugh Anderson [mailto:webmaster@u...]
> Sent: Monday, May 21, 2001 7:55 PM
> To: Access
> Subject: [access] Separating Combined Table
>
>
> I have the following problem with an Access 2000 database. I mistakenly
> combined two separate tables into one. One of the separate tables listed
> HTML subscribers, the other listed text letter subscribers. I still have
> the separate tables. It seems to me it should be possible query or
program
> in such a way that records in the combined table that equal a record in
> the HTML table would be identified - and similarly for the text table.
But
> I can't figure out how to do it.
> Anybody have any ideas, please?
Message #5 by "Hugh Anderson" <webmaster@u...> on Fri, 25 May 2001 02:33:06
|
|
Further to my combined table issue. There are records in the combined
table that are not in either of the two separate tables. I want to query
the combined table in such a way that I get a list of only those
particular records, that is, those records whose email field does not
exist in either of the separate tables. Help, please.
> It certainly did the trick, lke a charm. Thanks to everybody.
> Hugh Anderson
>
>
> > I believe a straightforward query will do the trick. Add first your
> > separate, then your combined table, and join them on a common field
> (e.g.,
> > e-mail address) by dragging the field from one table onto another.
> >
> > HTH,
> >
> > -Roy
> >
> > -----Original Message-----
> > From: Hugh Anderson [mailto:webmaster@u...]
> > Sent: Monday, May 21, 2001 7:55 PM
> > To: Access
> > Subject: [access] Separating Combined Table
> >
> >
> > I have the following problem with an Access 2000 database. I
mistakenly
> > combined two separate tables into one. One of the separate tables
listed
> > HTML subscribers, the other listed text letter subscribers. I still
have
> > the separate tables. It seems to me it should be possible query or
> program
> > in such a way that records in the combined table that equal a record
in
> > the HTML table would be identified - and similarly for the text table.
> But
> > I can't figure out how to do it.
> > Anybody have any ideas, please?
Message #6 by "John Ruff" <John_Ruff@m...> on Fri, 25 May 2001 03:52:23 -0700
|
|
You will want to create two queries to perform this function. For clarity
we'll call your combined table Table1 and the other two tables Table2 and
Table3. Also the common field in each table is PartNo.
Query1 -
1. Create a query with Table1 and Table2 linked on PartNo. In the Join
Properties, select "Include All records from Table1 and only those records
from Table2 where the joined fields are equal".
2. Select PartNo from both tables and place them into the query grid.
3. Place the words "Is Null" in the criteria field under Table2"s PartNo.
4. Save this query.
The result of Query1 will display only those records that are in Table1 and
not in Table2.
Query2 - We perform the same steps as we did in creating Query1 only we use
Query1 and Table3.
1. Create a query with Query1 and Table3 linked on PartNo. In the Join
Properties, select "Include All records from Query1 and only those records
from Table3 where the joined fields are equal".
2. Select PartNo from both Query1 and Table3 and place them into the query
grid.
3. Place the words "Is Null" in the criteria field under Table3"s PartNo.
4. Save this query.
The result of Query2 will display those records that are in the Table1 and
not in either Table2 or Table3.
John Ruff - The Eternal Optimist :)
-----Original Message-----
From: Hugh Anderson [mailto:webmaster@u...]
Sent: Friday, May 25, 2001 2:33 AM
To: Access
Subject: [access] RE: Separating Combined Table
Further to my combined table issue. There are records in the combined
table that are not in either of the two separate tables. I want to query
the combined table in such a way that I get a list of only those
particular records, that is, those records whose email field does not
exist in either of the separate tables. Help, please.
> It certainly did the trick, lke a charm. Thanks to everybody.
> Hugh Anderson
>
>
> > I believe a straightforward query will do the trick. Add first your
> > separate, then your combined table, and join them on a common field
> (e.g.,
> > e-mail address) by dragging the field from one table onto another.
> >
> > HTH,
> >
> > -Roy
> >
> > -----Original Message-----
> > From: Hugh Anderson [mailto:webmaster@u...]
> > Sent: Monday, May 21, 2001 7:55 PM
> > To: Access
> > Subject: [access] Separating Combined Table
> >
> >
> > I have the following problem with an Access 2000 database. I
mistakenly
> > combined two separate tables into one. One of the separate tables
listed
> > HTML subscribers, the other listed text letter subscribers. I still
have
> > the separate tables. It seems to me it should be possible query or
> program
> > in such a way that records in the combined table that equal a record
in
> > the HTML table would be identified - and similarly for the text table.
> But
> > I can't figure out how to do it.
> > Anybody have any ideas, please?
Message #7 by "Hugh Anderson" <webmaster@u...> on Mon, 28 May 2001 01:04:13
|
|
This was excellent advice. Thanks. However, being a wimp, I subsequently
discovered a query wizard in Access 2000 that walked me through the job
without coding.
> You will want to create two queries to perform this function. For
clarity
> we'll call your combined table Table1 and the other two tables Table2 and
> Table3. Also the common field in each table is PartNo.
>
> Query1 -
> 1. Create a query with Table1 and Table2 linked on PartNo. In the Join
> Properties, select "Include All records from Table1 and only those
records
> from Table2 where the joined fields are equal".
> 2. Select PartNo from both tables and place them into the query grid.
> 3. Place the words "Is Null" in the criteria field under Table2"s PartNo.
> 4. Save this query.
>
> The result of Query1 will display only those records that are in Table1
and
> not in Table2.
>
> Query2 - We perform the same steps as we did in creating Query1 only we
use
> Query1 and Table3.
> 1. Create a query with Query1 and Table3 linked on PartNo. In the Join
> Properties, select "Include All records from Query1 and only those
records
> from Table3 where the joined fields are equal".
> 2. Select PartNo from both Query1 and Table3 and place them into the
query
> grid.
> 3. Place the words "Is Null" in the criteria field under Table3"s PartNo.
> 4. Save this query.
>
> The result of Query2 will display those records that are in the Table1
and
> not in either Table2 or Table3.
>
>
> John Ruff - The Eternal Optimist :)
>
>
> -----Original Message-----
> From: Hugh Anderson [mailto:webmaster@u...]
> Sent: Friday, May 25, 2001 2:33 AM
> To: Access
> Subject: [access] RE: Separating Combined Table
>
> Further to my combined table issue. There are records in the combined
> table that are not in either of the two separate tables. I want to query
> the combined table in such a way that I get a list of only those
> particular records, that is, those records whose email field does not
> exist in either of the separate tables. Help, please.
>
> > It certainly did the trick, lke a charm. Thanks to everybody.
> > Hugh Anderson
> >
> >
> > > I believe a straightforward query will do the trick. Add first your
> > > separate, then your combined table, and join them on a common field
> > (e.g.,
> > > e-mail address) by dragging the field from one table onto another.
> > >
> > > HTH,
> > >
> > > -Roy
> > >
> > > -----Original Message-----
> > > From: Hugh Anderson [mailto:webmaster@u...]
> > > Sent: Monday, May 21, 2001 7:55 PM
> > > To: Access
> > > Subject: [access] Separating Combined Table
> > >
> > >
> > > I have the following problem with an Access 2000 database. I
> mistakenly
> > > combined two separate tables into one. One of the separate tables
> listed
> > > HTML subscribers, the other listed text letter subscribers. I still
> have
> > > the separate tables. It seems to me it should be possible query or
> > program
> > > in such a way that records in the combined table that equal a record
> in
> > > the HTML table would be identified - and similarly for the text
table.
> > But
> > > I can't figure out how to do it.
> > > Anybody have any ideas, please?
>
>
>
|
|
|
View Cart
