Bringing 2 table together in a query

[keri]

Hi,

I have a strong feeling this is a simple question, but I have been on
this computer so long my brain has fried!

I want to bring 2 tables together in a query. Both tables (these store
records of appointments made) have the same fields - one has data that
is imported from Excel, and one has data inputted from a form in my db.
(In case you are wondering for a couple of reasons I cannot import my
data from excel directly into the same table that my form entries will
go into). Therefore when users want to view all of the appointments
they have made they currently have to go to 2 seperate tables - one for
old data (imported) and one for new data (entered via the db).

The problem is when i come to create relationships I can't seem to
understand how to correctly do it. The primary key in the new table is
the APPID - an autonumber - in the imported table it is the same field
but not an autonumber. The Name, AppDate and all other fields on both
tables may be duplicated between tables, and in each table on its own.

How do I do this? Please tell me if I am being very dumb!

 

[Douglas J. Steele]

If you absolutely can't put the Excel data into the same table, you'll have
to use a UNION query to return the data from both tables at once.

Can you not append the data from Excel into your Access table though?

 

[Tom Wickerath]

Hi Keri,

(In case you are wondering for a couple of reasons I cannot import my
data from excel directly into the same table that my form entries will
go into).

Well, yes, I am wondering. Would you mind explaining why you feel that the
data cannot be combined into one table? Note: You can add a field to
indicate Imported (older) data vs. Inputted (newer) data.

The problem is when i come to create relationships I can't seem to
understand how to correctly do it.

Are you sure there is a relationship that exists between these two similar
tables?

An Autonumber data type is typically joined to a Number (Long Integer), in
order to create what is known as a one-to-many (abbreviated 1:M)
relationship. This assumes that the Number (Long Integer) field is not set as
a primary key or uniquely indexed. If you create a unique index, then the
relationship would be 1:1 between the Autonumber and the Number. However, I'm
just not seeing either a 1:1 or a 1:M relationship between two similar sets
of data, which probably should be in one table.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[keri]

The reason I cannot or am not putting the data from Excel into the same
table-

The excel data is coming from another db - (not access and not
recogniseable to access). This db creates an AppID for each
appointment. If I save this data to excel and try and import into my
APPS table in access I get an error as the AppID field is set to be an
autonumber. (It will be an autonumber for the appointments I enter via
my form - but for the imported calls it is just a number). I do not
want to assign access autonumbers for the AppID to my imported
appointments data as I wish to keep the previous AppId assigned by the
other database. I hope this makes sense.

Other than this - there is no relationship between the tables - they
are just two tables holding similar data in identical fields - the only
difference is that one of the tables is data I have imported and one is
data entered via access.

I would prefer to have these tables as one table but I don't think this
is possible!

Thanks for the help - and I hope this explains things clearer.

 

[keri]

The reason I cannot or am not putting the data from Excel into the same
table-

The excel data is coming from another db - (not access and not
recogniseable to access). This db creates an AppID for each
appointment. If I save this data to excel and try and import into my
APPS table in access I get an error as the AppID field is set to be an
autonumber. (It will be an autonumber for the appointments I enter via
my form - but for the imported calls it is just a number). I do not
want to assign access autonumbers for the AppID to my imported
appointments data as I wish to keep the previous AppId assigned by the
other database. I hope this makes sense.

Other than this - there is no relationship between the tables - they
are just two tables holding similar data in identical fields - the only
difference is that one of the tables is data I have imported and one is
data entered via access.

I would prefer to have these tables as one table but I don't think this
is possible!

Thanks for the help - and I hope this explains things clearer.

 

[Tom Wickerath]

Hi Keri,

I would prefer to have these tables as one table but I don't think this is possible!

It depends. Is the other database still being used to generate new records
(ie. generating new AppID's)? If not, then you most certainly can combine
the data. You would do this by creating a new empty table with an Autonumber
data type plus all the other required fields. Link to the Excel file that you
can produce from this other database. Create an append query to append the
records from your linked Excel table to the new table. You can append a value
to an Autonumber data type, as long as the value is not already present (this
is why I asked if the other database is still being used to generate new
records).

Your other alternative is to create a union query to bring the two tables
together in a query. Are you familiar with the process of creating union
queries?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[keri]

I have not used union queries before so I am going to read up on this
now.

Further to this - the data from my otherdatabase will only be exported
into excel once and then into access once - this is done when the
database is set up for each user. So the first records of appointments
should be the ones imported from this excel file. I was trying to do
this through the method of Get External Data - does this work in a
different way to an append query?

 

[Tom Wickerath]

Here is a sample that you may find helpful for learning more about Union
queries:

http://home.comcast.net/~tutorme2/samples/unionqueries.zip

It was created several years ago by a co-worker (Melinda Smith). It includes
a Word document and a sample database (Access 97 format).

Further to this - the data from my otherdatabase will only be exported
into excel once and then into access once

If I understand you correctly, this means that the older database is not
being used to generate new records. Is this correct?

does this work in a different way to an append query?

Yes. You can append an existing number to an Autonumber data type, as long
as the number you are attempting to append is not already present in the
target table.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Douglas J. Steele]

If the data is only coming from Excel once, then you can put it in your
Access table using an Append query even if the Access table is using an
AutoNumber field.

 

[keri]

Thanks everyone - the append query worked super - and I can run this
from a macro (i presume) so it is a lot easier for a user to set up
their database.

Once again excellent advice - thanks for the time and effort you put
into helping us all.

 

[Jan Kowalski]

Hi,

I have a strong feeling this is a simple question, but I have been on
this computer so long my brain has fried!

I want to bring 2 tables together in a query. Both tables (these store
records of appointments made) have the same fields - one has data that
is imported from Excel, and one has data inputted from a form in my db.
(In case you are wondering for a couple of reasons I cannot import my
data from excel directly into the same table that my form entries will
go into). Therefore when users want to view all of the appointments
they have made they currently have to go to 2 seperate tables - one for
old data (imported) and one for new data (entered via the db).

The problem is when i come to create relationships I can't seem to
understand how to correctly do it. The primary key in the new table is
the APPID - an autonumber - in the imported table it is the same field
but not an autonumber. The Name, AppDate and all other fields on both
tables may be duplicated between tables, and in each table on its own.

How do I do this? Please tell me if I am being very dumb!

 

[strive4peace]

Hi Jan,

"(In case you are wondering for a couple of reasons I cannot import my
data from excel directly into the same table that my form entries will
go into). Therefore when users want to view all of the appointments
they have made they currently have to go to 2 seperate tables - one for
old data (imported) and one for new data (entered via the db)."


Instead of thinking about importing data directly into the table,
consider appending data TO the table from the table that you did import...

it is NOT a good idea to have redundant structures in a database. There
are several things you can do to keep track of what came from where...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*