Select From Tbl1 Only where ID Exists in Tbl2 ???

[CharlesWhiteman]

If I have a table (tbl1) containing an ID "MacAddress" and I want to select corresponding rows from another Table (tbl2) which also contains MacAddress's but that table is very large.

Can I do a query which only looks at records from Tbl1 where there is a corresponsing value from Tbl2?

 

[pbaldy]

If you include both tables in a query with a join line between the MacAddress field in each table, you should get that result.

 

[CharlesWhiteman]

Hi P, and thanks for your reply. Having spent 7 days solid on various aspects my brain is now hurting.

I have two tables: TblPrimaryData & TblRadAcct

Tbl PrimaryData includes a couple of fields CustMaxAddress & CustIPaddress

These have to be updated from an external Db which TblRadAcct is linked from. That table contains many repeated entries for the same client. It has a RadAcctID field which is sequencial.

I need to be ab le to pick the last entry for rows where there is a corresponding CustMacAddress is TblPrimaryData to ensure that TblPrimaryData contains the latest IP address.

In my query I added the two above mentioned tables. And in QBE the following fields:

TblRadAcct: RadAcctID, UserName & from TblPrimaryData, CustMacAddress. I put a join from UserName to CustMacAddress (both contain the same value) and then I did a 'GroupBy Last' on the RadAcctID.

That works fine and I see the query produce the correct result. However, in my query i also want another field from TblRadAcct, "IPaddress". When i add that field into the QBE then i get repeated results?

Any notion as to why that would be?

 

[Brianwarnock]

It would have been clearer to see your entire SQL, i suspect that you grouped on IPaddress.

What I think that you need to do is
query1 2 fields Group by CustMaxAddress ,Max, not Last ,RadAcctID
query 2 join 1 back to tblRadacct on RadAcctID ,custmaxaddress and select IPaddress

Brian

 

[CharlesWhiteman]

This is the code working correctly but minus the IP Address field I need:

SELECT DISTINCT Last(radacct.RadAcctId) AS LastOfRadAcctId, TblPrimaryData.CustMacAddress, TblPrimaryData.CustID
FROM radacct INNER JOIN TblPrimaryData ON radacct.UserName = TblPrimaryData.CustMacAddress
GROUP BY TblPrimaryData.CustMacAddress, TblPrimaryData.CustID;

The code produces correctly two rows of data.

Here is the code with the IP Address field but proces multiple lines of rows:

SELECT DISTINCT Last(radacct.RadAcctId) AS LastOfRadAcctId, TblPrimaryData.CustMacAddress, TblPrimaryData.CustID, radacct.FramedIPAddress
FROM radacct INNER JOIN TblPrimaryData ON radacct.UserName = TblPrimaryData.CustMacAddress
GROUP BY TblPrimaryData.CustMacAddress, TblPrimaryData.CustID, radacct.FramedIPAddress;

 

[pbaldy]

Is this what you need?

http://www.baldyweb.com/LastValue.htm

 

[Brianwarnock]

Is this what you need?

http://www.baldyweb.com/LastValue.htm

That's what I said, but he ignored me.

Brian