Trouble with a sql statement.... any ideas?

[nyx]

Hi,

I'm trying to build a single sql statement out of the 2 statements below but I have no idea how, or even if it is possible to do. Can anyone enlighten me?

SELECT Accounts.[Name], Accounts.[Address], Accounts.[Phone]
FROM Query1 INNER JOIN Accounts ON Query1.[Members] = Accounts.[Name];


The following is Query1:

SELECT Members.[Members]
FROM Accounts INNER JOIN Members ON Accounts.[Account_ID] = Members.[Account_ID]
WHERE (((Accounts.[Account_ID])=2586));

As you can see, I've managed to create the queries to do it but I have not been able to put them together. Is it possible?

Thanks for any help you can provide!

 

[ghubbell]

I don't know exactly what you are hoping to receive for results however try running this to understand one method:
SELECT Accounts.*
FROM Accounts LEFT JOIN Members ON (Accounts.Members = Members.Members) AND (Accounts.AccountID = Members.Account_ID)
WHERE (((Accounts.Account_ID)=2586));
In summary if this is the result you require I think you may have a design flaw that needs correction. If not the results, try flipping to right or inner join on both joins and/or query properties unique values Yes/No. About all I can offer with the given info...Good Luck, Gord
ghubbell@total.net

 

[Nebuchednezzar]

Is the account_id not used as a foreign key between tables (i.e... does the account_id in accounts not correspond to the account_id in members)?
If it does, you could simply run,
SELECT Accounts.[Name], Accounts.[Address], Accounts.[Phone]
FROM accounts where accounts.account_id = 2586;

If they aren't the same (the account_id is different for each member in each table - not intended to be a foreign key link), then you can use...

SELECT Accounts.[Name], Accounts.[Address], Accounts.[Phone]
from Accounts
where Accounts.[Name] in
(SELECT Members.[Members]
from Members
where Members.[Account_id] = 2586);

 

[nyx]

Hey guys!

Thanks for the replies. You have to forgive me as I am not an expert at Access as you guys are. I do want to apologize ahead of time if I am answering/asking the wrong questions.

To simplify things I will attempt to explain what I need to do...

I have 2 tables:

Syntax: <Table name: field1,field2,etc>

* denotes the primary key

Accounts: *Account_ID, Name, Address, Phone
Members: *Member_ID, Member_name, Account_ID

Account_ID is a foreign key in the members table.

Basically, what I want to do in plain english is:

Given a specific Account ID (ie. 2586) in the members table

** Select all records in the Members table that have an account ID of 2586 and then

Display the Name, Address and phone fields from the Accounts table Where the Member names of the above selected** are equal to the Account Name.

I don't know if I've explained that well enough. If you need any more information, I will do my best to answer your questions. Thanks again for the help!

ps.. I tried the sql statements you supplied above but they didn't work out for me.

 

[MichaelRed]

Obviously, I am missiing something. The below looks (to me) like it should be what nyx ordered. It is not sufficiently different from other soloutions offered to post, except he (nyx) says the others 'dont work'. On the other hand, he *nyx) doesn't offer any specifics. Do the offerd soloutions return some result - but not the desired? If so, what are the undesireable effects? Do they not work - as in gust give errors? If so, what are the errors? Is this 'embeded&quot; in code - executed SQL, &quot;built&quot; by code? It LOOKS LIKE a test situation, because NO ONE is going to hard code an account number in a query this way. So what is the 'big picture&quot;?


SELECT Members.[Members], Accounts.[Name], Accounts.[Address], Accounts.[Phone]
FROM Members INNER JOIN Members ON Accounts.[Account_ID] = Members.[Account_ID]
WHERE (((Accounts.[Account_ID])=2586));

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[nyx]

Wow! lots of questions. I do apologize. I didn't mean to cause so much grief with you guys.

My name is Nicholas by the way.

I didn't want to send a barrage of information as I know sometimes I get &quot;flamed&quot; for doing so.

To test the above solutions, please correct me if I am doing this the wrong way, but I have cut and paste them into a new query and attempted to run them.

You are correct in assuming MichaelRed that this is a temporary test just to see if the statement will return what I ask. I will then be using the statement in code to display this information when I'm sure that it will return the correct results.

I do not get any results when I tried the above sql statements, as in, they do not return any records when I execute the query.

The solution MichaelRed has offered gives me a Join error when I try to save the query.

I do truly appreciate the effort that has gone into this to try and help. Thanks again.

Nicholas

 

[ghubbell]

Never give up! Nicholas: if you would like, dice out some sample data / zip & email. Will solve it if you would like. My treat. Gord
ghubbell@total.net

 

[nyx]

WAHOO!! I figured it out!

Actually, what I did was take Gord's first statement and made some adjustments to it. Here's what it looks like:

SELECT Accounts.*
FROM Accounts INNER JOIN Members ON
[Accounts].[Account_Name]=[Members].[Members]
WHERE ((([Members].[Account_ID])=2586));

And that gives me the same results as the 2 queries above! Thanks for everyone's help! I really appreciated it!

 

[ghubbell]

Way to go Nicholas! Gord
ghubbell@total.net