Merge 2 different tables

[JezLisle via AccessMonster.com]

Hi,

How is it possible to merge 2 different tables together.

Table1 has AddressID, ApptNum, ApptDate, CompletedDate
Table2 has AddressID, NANum, NADate, CompletedDate

I want to merge into
AddressID, Number, Date, CompletedDate, Description

The Desctiption will show Appt for table1 and NA for table2.

Thanks,

 

[Jerry Whittle]

SELECT Table1.AddressID,
Table1.ApptNum AS [Number],
Table1.ApptDate AS [Date],
Table1.CompletedDate,
"Appt" AS Description
FROM Table1
UNION
SELECT Table2.AddressID,
Table2.NANum AS [Number],
Table2.NADate AS [Date],
Table2.CompletedDate,
"NA" AS Description
FROM Table2;

WARNING: Bothe Number and Date are reserved words in Access and could cause
problems. Check out the following:

http://support.microsoft.com/kb/286335/

 

[JezLisle via AccessMonster.com]

Thanks, I adapted the Number and Date to aviod any issues with them being
reserved words.

The query works but when running it the description column is filled with the
AddressID and not either "Appt" or "NA". How can I get around this?

SELECT Table1.AddressID,
Table1.ApptNum AS [Number],
Table1.ApptDate AS [Date],
Table1.CompletedDate,
"Appt" AS Description
FROM Table1
UNION
SELECT Table2.AddressID,
Table2.NANum AS [Number],
Table2.NADate AS [Date],
Table2.CompletedDate,
"NA" AS Description
FROM Table2;

WARNING: Bothe Number and Date are reserved words in Access and could cause
problems. Check out the following:

http://support.microsoft.com/kb/286335/

Hi,

[quoted text clipped - 9 lines]

Thanks,

 

[JezLisle via AccessMonster.com]

Forgot to write...

If I just go with table1 and use "Appt" as Description it will work but when
the Union is created it duplicates the AddressID

Thanks, I adapted the Number and Date to aviod any issues with them being
reserved words.

The query works but when running it the description column is filled with the
AddressID and not either "Appt" or "NA". How can I get around this?

SELECT Table1.AddressID,
Table1.ApptNum AS [Number],

[quoted text clipped - 19 lines]

 

[Jerry Whittle]

I might be missing something here.... Please post your SQL statement and
some sample data showing the problem.

One thing to try first is to change the UNION to UNION ALL.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Forgot to write...

If I just go with table1 and use "Appt" as Description it will work but when
the Union is created it duplicates the AddressID

Thanks, I adapted the Number and Date to aviod any issues with them being
reserved words.

The query works but when running it the description column is filled with the
AddressID and not either "Appt" or "NA". How can I get around this?

SELECT Table1.AddressID,
Table1.ApptNum AS [Number],

[quoted text clipped - 19 lines]

Thanks,

 

[JezLisle via AccessMonster.com]

The SQL

SELECT quniAddressAppts.uprn AS HRN, quniAddressAppts.ApptNum As ApptOrNANum,
quniAddressAppts.ApptDate As ApptOrNADate, quniAddressAppts.completion_date
AS [Completed?], "Appt" As Description
FROM quniAddressAppts
UNION
SELECT quniAddressNoAccess.uprn AS HRN, quniAddressNoAccess.NA As ApptOrNANum,
quniAddressNoAccess.NADate As ApptOrNADate, quniAddressNoAccess.
completion_date AS [Completed?], "NA" As Desciption
FROM quniAddressNoAccess;

I might be missing something here.... Please post your SQL statement and
some sample data showing the problem.

One thing to try first is to change the UNION to UNION ALL.

Forgot to write...

[quoted text clipped - 12 lines]

 

[Jerry Whittle]

Your SQL statement looks fine. We probably need to see a sample of the
results and what it wrong with it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The SQL

SELECT quniAddressAppts.uprn AS HRN, quniAddressAppts.ApptNum As ApptOrNANum,
quniAddressAppts.ApptDate As ApptOrNADate, quniAddressAppts.completion_date
AS [Completed?], "Appt" As Description
FROM quniAddressAppts
UNION
SELECT quniAddressNoAccess.uprn AS HRN, quniAddressNoAccess.NA As ApptOrNANum,
quniAddressNoAccess.NADate As ApptOrNADate, quniAddressNoAccess.
completion_date AS [Completed?], "NA" As Desciption
FROM quniAddressNoAccess;

I might be missing something here.... Please post your SQL statement and
some sample data showing the problem.

One thing to try first is to change the UNION to UNION ALL.

Forgot to write...

[quoted text clipped - 12 lines]

Thanks,

 

[JezLisle via AccessMonster.com]

Sample of the data when in Union query

HRN ApptOrNANum ApptOrNADate Completed? Description
6543645 1 08-Apr-08 6543645
15352001295 1 12-Apr-08 12-Apr-08 15352001295
10029000261 1 14-Apr-08 15-Apr-08 10029000261
15352000825 1 02-Apr-08 02-Apr-08 15352000825
87555666 1 21-Apr-08 21-Apr-08 87555666
10457000677 1 27-May-08 10457000677

Your SQL statement looks fine. We probably need to see a sample of the
results and what it wrong with it.

The SQL

[quoted text clipped - 17 lines]

 

[JezLisle via AccessMonster.com]

This is when I just use the single query (not Union)

6543645 1 08-Apr-08 Appt
15352001295 1 12-Apr-08 12-Apr-08 Appt
10029000261 1 14-Apr-08 15-Apr-08 Appt
15352000825 1 02-Apr-08 02-Apr-08 Appt
87555666 1 21-Apr-08 21-Apr-08 Appt
10457000677 1 27-May-08 Appt

Sample of the data when in Union query

HRN ApptOrNANum ApptOrNADate Completed? Description
6543645 1 08-Apr-08 6543645
15352001295 1 12-Apr-08 12-Apr-08 15352001295
10029000261 1 14-Apr-08 15-Apr-08 10029000261
15352000825 1 02-Apr-08 02-Apr-08 15352000825
87555666 1 21-Apr-08 21-Apr-08 87555666
10457000677 1 27-May-08 10457000677

Your SQL statement looks fine. We probably need to see a sample of the
results and what it wrong with it.

[quoted text clipped - 3 lines]

 

[Jerry Whittle]

WOW! That is strange. Everything looks fine with one teenie-weenie thing. The
word "Description" is reserved. Try changing it a little to something like
"Descriptions". If this fixes it, I really, really want to know!

For more on reserved words, check out the following:

http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

This is when I just use the single query (not Union)

6543645 1 08-Apr-08 Appt
15352001295 1 12-Apr-08 12-Apr-08 Appt
10029000261 1 14-Apr-08 15-Apr-08 Appt
15352000825 1 02-Apr-08 02-Apr-08 Appt
87555666 1 21-Apr-08 21-Apr-08 Appt
10457000677 1 27-May-08 Appt

Sample of the data when in Union query

HRN ApptOrNANum ApptOrNADate Completed? Description
6543645 1 08-Apr-08 6543645
15352001295 1 12-Apr-08 12-Apr-08 15352001295
10029000261 1 14-Apr-08 15-Apr-08 10029000261
15352000825 1 02-Apr-08 02-Apr-08 15352000825
87555666 1 21-Apr-08 21-Apr-08 87555666
10457000677 1 27-May-08 10457000677

Your SQL statement looks fine. We probably need to see a sample of the
results and what it wrong with it.

[quoted text clipped - 3 lines]

Thanks,

 

[JezLisle via AccessMonster.com]

This is so bizarre!!! Tried the changing of Description to Descriptions and
still the same happens.

I am totally lost with this

WOW! That is strange. Everything looks fine with one teenie-weenie thing. The
word "Description" is reserved. Try changing it a little to something like
"Descriptions". If this fixes it, I really, really want to know!

For more on reserved words, check out the following:

http://support.microsoft.com/kb/286335/

This is when I just use the single query (not Union)

[quoted text clipped - 20 lines]

 

[Jerry Whittle]

I'd like to see this. If the information in the database isn't sensitive,
please zip it up and send a copy to me at:

(e-mail address removed)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

This is so bizarre!!! Tried the changing of Description to Descriptions and
still the same happens.

I am totally lost with this

WOW! That is strange. Everything looks fine with one teenie-weenie thing. The
word "Description" is reserved. Try changing it a little to something like
"Descriptions". If this fixes it, I really, really want to know!

For more on reserved words, check out the following:

http://support.microsoft.com/kb/286335/

This is when I just use the single query (not Union)

[quoted text clipped - 20 lines]

Thanks,

 

[JezLisle via AccessMonster.com]

Ok, I have come accross the issue thats stopping the uery running properly.

I was recreating the DB with the tables in that build up my SQL and
transfering the original Union Query into this temp DB and ran them to make
sure it worked as should before sending to you. You would not believe it but
the SQL been having issues with actually works and puts either "Appt" or "NA"
in the Description columns. Unbelievable!!!

Now here is my dilema...

The Temp DB I was sending you worked on the fact I imported the tables from
the Original Database, where as the DB I work from everyday uses the Link
Tables option. Could this be affecting the whole thing?

I dont understand why it would but thats the difference between it working
and not working.

If I was to recreate the DB as a stand alone DB would that resolve the issue?
My original idea was to ADO link from the SQL Server to my Access DB (not
that I know how to do that) but instead I opted for the Link Tables option.

I'd like to see this. If the information in the database isn't sensitive,
please zip it up and send a copy to me at:

(e-mail address removed)

This is so bizarre!!! Tried the changing of Description to Descriptions and
still the same happens.

[quoted text clipped - 13 lines]

 

[JezLisle via AccessMonster.com]

I am still struggling with this, and totally confused with what's happening

Ok, I have come accross the issue thats stopping the uery running properly.

I was recreating the DB with the tables in that build up my SQL and
transfering the original Union Query into this temp DB and ran them to make
sure it worked as should before sending to you. You would not believe it but
the SQL been having issues with actually works and puts either "Appt" or "NA"
in the Description columns. Unbelievable!!!

Now here is my dilema...

The Temp DB I was sending you worked on the fact I imported the tables from
the Original Database, where as the DB I work from everyday uses the Link
Tables option. Could this be affecting the whole thing?

I dont understand why it would but thats the difference between it working
and not working.

If I was to recreate the DB as a stand alone DB would that resolve the issue?
My original idea was to ADO link from the SQL Server to my Access DB (not
that I know how to do that) but instead I opted for the Link Tables option.

I'd like to see this. If the information in the database isn't sensitive,
please zip it up and send a copy to me at:

[quoted text clipped - 5 lines]

 

[Jerry Whittle]

Hi Jez,

I finally got a chance to look at the database you sent. As you mentioned,
it works find for me.

Some possibilities:

1. You mention that it's linked to SQL Server. There could be some problems
there. Instead of linking to the table, you might consider using pass-through
queries and let SQL Server do the work.

2. You have a union query built upon other union queries. That introduces
many more places for an error to occur. See if you can directly query the
tables in one SQL statement.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am still struggling with this, and totally confused with what's happening

Ok, I have come accross the issue thats stopping the uery running properly.

I was recreating the DB with the tables in that build up my SQL and
transfering the original Union Query into this temp DB and ran them to make
sure it worked as should before sending to you. You would not believe it but
the SQL been having issues with actually works and puts either "Appt" or "NA"
in the Description columns. Unbelievable!!!

Now here is my dilema...

The Temp DB I was sending you worked on the fact I imported the tables from
the Original Database, where as the DB I work from everyday uses the Link
Tables option. Could this be affecting the whole thing?

I dont understand why it would but thats the difference between it working
and not working.

If I was to recreate the DB as a stand alone DB would that resolve the issue?
My original idea was to ADO link from the SQL Server to my Access DB (not
that I know how to do that) but instead I opted for the Link Tables option.

I'd like to see this. If the information in the database isn't sensitive,
please zip it up and send a copy to me at:

[quoted text clipped - 5 lines]

Thanks,