RELATIONSHIPS

R

realspido

Hi everybody,
I'm trying to create a relationship between my database and excel sheet
containing information about employees spent time. This is how the excel
sheet looks like:
exc_table:
date empl_no hours
5/5/06 0001 7
.....
I made Link table in my database.
my database is made from two linked tables:
table1:
key date
1 5/5/06
.....

table2
key_link emp_no
1 0001
1 0002
....
there's a relationship between: table1.key and table2.key_link
I thought, to make a relationship between my database and excel sheet I have
to create a unique code in both of them. I made queries:
query1:
expr: [exc_table.date]&[exc_table.empl_no]
sum of hours: [exc_table.hours]

querry2:
expr: [table1.date]&[table2.empl_no]
table2.key_link
table2.empl_no

Creating relationships between those two queries (between expressions)
didn't work, so I decided to make queries as 'Make-table query' but then I
cant add any data in table1 and table2.
What am I doing wrong?
Please help me!!!
 
R

Roger Carlson

You can't create a relationship between a table in a database and an excel
spreadsheet. Nor can you create a relationship between tables that are
stored in separate databases. The only time you can create relationships
between tables is when both tables are in the same physical MDB file. In
the case of a split database (ie Front-End/Back-End), the relationship must
be created in the Back-End database. When you link both tables into the
Front-End, the relationship will follow.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
R

realspido

That's why I made in my database a Link-table from xls sheet.
Problem is how to make a relationship between one table containing all
columns:
date empl_no hours
and two linked tables where first contains only key(for relationship) and
date and the second one key and name?

Roger Carlson said:
You can't create a relationship between a table in a database and an excel
spreadsheet. Nor can you create a relationship between tables that are
stored in separate databases. The only time you can create relationships
between tables is when both tables are in the same physical MDB file. In
the case of a split database (ie Front-End/Back-End), the relationship must
be created in the Back-End database. When you link both tables into the
Front-End, the relationship will follow.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


realspido said:
Hi everybody,
I'm trying to create a relationship between my database and excel sheet
containing information about employees spent time. This is how the excel
sheet looks like:
exc_table:
date empl_no hours
5/5/06 0001 7
....
I made Link table in my database.
my database is made from two linked tables:
table1:
key date
1 5/5/06
....

table2
key_link emp_no
1 0001
1 0002
...
there's a relationship between: table1.key and table2.key_link
I thought, to make a relationship between my database and excel sheet I have
to create a unique code in both of them. I made queries:
query1:
expr: [exc_table.date]&[exc_table.empl_no]
sum of hours: [exc_table.hours]

querry2:
expr: [table1.date]&[table2.empl_no]
table2.key_link
table2.empl_no

Creating relationships between those two queries (between expressions)
didn't work, so I decided to make queries as 'Make-table query' but then I
cant add any data in table1 and table2.
What am I doing wrong?
Please help me!!!
 
S

SusanV

You're still LINKED to the spreadsheet. To have a relationship you need to
import the spreadsheet into a table in the database first.
--
hth,
SusanV

realspido said:
That's why I made in my database a Link-table from xls sheet.
Problem is how to make a relationship between one table containing all
columns:
date empl_no hours
and two linked tables where first contains only key(for relationship) and
date and the second one key and name?

Roger Carlson said:
You can't create a relationship between a table in a database and an
excel
spreadsheet. Nor can you create a relationship between tables that are
stored in separate databases. The only time you can create relationships
between tables is when both tables are in the same physical MDB file. In
the case of a split database (ie Front-End/Back-End), the relationship
must
be created in the Back-End database. When you link both tables into the
Front-End, the relationship will follow.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


realspido said:
Hi everybody,
I'm trying to create a relationship between my database and excel sheet
containing information about employees spent time. This is how the
excel
sheet looks like:
exc_table:
date empl_no hours
5/5/06 0001 7
....
I made Link table in my database.
my database is made from two linked tables:
table1:
key date
1 5/5/06
....

table2
key_link emp_no
1 0001
1 0002
...
there's a relationship between: table1.key and table2.key_link
I thought, to make a relationship between my database and excel sheet I have
to create a unique code in both of them. I made queries:
query1:
expr: [exc_table.date]&[exc_table.empl_no]
sum of hours: [exc_table.hours]

querry2:
expr: [table1.date]&[table2.empl_no]
table2.key_link
table2.empl_no

Creating relationships between those two queries (between expressions)
didn't work, so I decided to make queries as 'Make-table query' but
then I
cant add any data in table1 and table2.
What am I doing wrong?
Please help me!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top