Need help defining the relationship

[ewong]

Hi all!

I'm currently working on an Access project that I need to finish by tomorrow. I have never got an Access project and have never used one before. I am pretty sure the issue that I am having here is a fairly easy one for experienced people out there.

I currently have 2 tables. The first one is a table with 15 fields in it. The second one has 5 fields.


The first and second table have information about account name and account ID.

The second one has information about account description that the first one doesn't have.

What I am trying to do is to create a new field on the first table for account description according to the information provided on the second table. Each account description will be different according to the account name and account ID.

How do you do that though? I am thinking it would be through the lookup function or relationship function but I am just not sure how!

Thanks for your help!

[orange]

Help! Urgent! is not the way to get an answer to your problem.

Give your post a meaningful title - to give people some idea of what the post is about.
Tell us the layout of your tables.

Working on a project today that is due tomorrow doesn't give us any idea of what you have tried; what errors you have encountered etc.
What is causing the urgency?

I have never heard of relationship function - could you please tell us what you found?

You might try google Adding a field to an MS Access table

http://bit.ly/Pnf8jG

[ewong]

Hi orange,

The problem I'm having is pretty much like the link below:

http://www.access-programmers.co.uk/forums/archive/index.php/t-95436.html

I am very new in Access and I would be very grateful if anyone here could provide me a step by step guidance. Thanks!

Help! Urgent! is not the way to get an answer to your problem.

Give your post a meaningful title - to give people some idea of what the post is about.
Tell us the layout of your tables.

Working on a project today that is due tomorrow doesn't give us any idea of what you have tried; what errors you have encountered etc.
What is causing the urgency?

I have never heard of relationship function - could you please tell us what you found?

You might try google Adding a field to an MS Access table

http://bit.ly/Pnf8jG

[orange]

I hate to be negative, but in the post you referenced -- no one, not the original poster nor the responders bothered to state the problem.
If you're looking for vlookup concept then

check https://www.accessforums.net/access/...cel-27681.html

If that doesn't help then please state exactly what you want to do.
Use some data to show the before and after you have and want.

[ewong]

So,

This is an example. I have 2 tables.

First table has these fields (around 10): Account ID, Employee ID, etc
Second table has these fields: Account ID, Employee ID, and Commission ID

Both tables have the following fields: Account ID and Employee ID.

But only the second table has the Commission ID information...

What I am trying to do is to pull the Commission ID data from the second table to the first table. The Commission ID will vary according to the Account ID and Employee ID.

How do I do that? People say I would need to use queries, dlookup, sql, etc but I am very new in Access and I just don't know how.

Thanks!

I hate to be negative, but in the post you referenced -- no one, not the original poster nor the responders bothered to state the problem.
If you're looking for vlookup concept then

check https://www.accessforums.net/access/...cel-27681.html

If that doesn't help then please state exactly what you want to do.
Use some data to show the before and after you have and want.

[ewong]

So the end result would be all fields in the first table PLUS commission ID (according to the Account ID and Employee ID) from the second table....

So,

This is an example. I have 2 tables.

First table has these fields (around 10): Account ID, Employee ID, etc
Second table has these fields: Account ID, Employee ID, and Commission ID

Both tables have the following fields: Account ID and Employee ID.

But only the second table has the Commission ID information...

What I am trying to do is to pull the Commission ID data from the second table to the first table. The Commission ID will vary according to the Account ID and Employee ID.

How do I do that? People say I would need to use queries, dlookup, sql, etc but I am very new in Access and I just don't know how.

Thanks!

[orange]

I think this should work but have not tested it.

SELECT Table1.*
, Table2.CommissionID
FROM
Table1 InnerJoin Table2 On
Table1.AccountId = Table2.AccountId AND
Table1.EmployeeId = Table2.EmployeeId;

[ewong]

It doesn't seem to be working. It's generating an error code when I try to run it. This is what I typed:

SELECT Jul'12AllAccnt(FUN).*
, Commissions.CommissionID
FROM
Jul'12AllAccnt(FUN) InnerJoin Commissions On
Jul'12AllAccnt(FUN).EmployeeID = Commissions.EmployeeID AND
Jul'12AllAccnt(FUN).AccountID = Commissions.AccountID;

I think this should work but have not tested it.

SELECT Table1.*
, Table2.CommissionID
FROM
Table1 InnerJoin Table2 On
Table1.AccountId = Table2.AccountId AND
Table1.EmployeeId = Table2.EmployeeId;

[ewong]

So,

I am very new in any programming language as well... I just wanted to confirm if I only need to type this (see below) in the sql view on query designer? Do I need to type anything else?? Please provide me with the full code... thanks

SELECT Jul'12AllAccnt(FUN).*
, Commissions.CommissionID
FROM
Jul'12AllAccnt(FUN) InnerJoin Commissions On
Jul'12AllAccnt(FUN).EmployeeID = Commissions.EmployeeID AND
Jul'12AllAccnt(FUN).AccountID = Commissions.AccountID;

[orange]

Because you are using special characters in your table name - the special characters are '() -, you have to enclose any such field or table names in [] with Access. You should use a naming convention that uses alphanumerics and _ only, you will save yourself a lot of typing and a lot of headaches.

You should try (again untested)
SELECT [Jul'12AllAccnt(FUN)].*
, Commissions.CommissionID
FROM
[Jul'12AllAccnt(FUN)] InnerJoin Commissions On
[Jul'12AllAccnt(FUN)].EmployeeID = Commissions.EmployeeID AND
[Jul'12AllAccnt(FUN)].AccountID = Commissions.AccountID;

[ewong]

it says "syntax error in from clause"

Because you are using special characters in your table name - the special characters are '() -, you have to enclose any such field or table names in [] with Access. You should use a naming convention that uses alphanumerics and _ only, you will save yourself a lot of typing and a lot of headaches.

You should try (again untested)
SELECT [Jul'12AllAccnt(FUN)].*
, Commissions.CommissionID
FROM
[Jul'12AllAccnt(FUN)] InnerJoin Commissions On
[Jul'12AllAccnt(FUN)].EmployeeID = Commissions.EmployeeID AND
[Jul'12AllAccnt(FUN)].AccountID = Commissions.AccountID;

[orange]

I just set up something similar

SELECT [Animal(D'escription)].*
, Animal.AName
FROM
Animal INNER JOIN [Animal(D'escription)]

then realized it's the InnerJoin has to be 2 words INNER JOIN
ON Animal.AnimalId = [Animal(D'escription)].id;

[orange]

You should not cross post without advising the reader.
http://www.access-programmers.co.uk/...70#post1185870

Please don't cross-post without providing links to your cross-posts. We answer questions for free. Please don't waste the time of the people helping you.
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[ewong]

Why is it asking for parameter value?

I just set up something similar

SELECT [Animal(D'escription)].*
, Animal.AName
FROM
Animal INNER JOIN [Animal(D'escription)]

then realized it's the InnerJoin has to be 2 words INNER JOIN
ON Animal.AnimalId = [Animal(D'escription)].id;

[orange]

Check your field names, make sure the spelling is correct; verify against the table definition.