RELATIONSHIPS

[Guest]

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!!!

 

[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

 

[Guest]

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?

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

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!!!

 

[Roger Carlson]

Let me repeat: YOU CANNOT CREATE A RELATIONSHIP BETWEEN LINKED TABLES. The
tables MUST exist in ONE database (ie. MDB file) in order for a relationship
to be created.

--
--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

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?

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

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!!!

 

[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

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?

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

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!!!