Get value from a Table1 col1 where Table1 col2 equals Query Value

[DStevens]

Confusing question?

The only way I can explain it best is to show it:

Table1
---------------------------
Name    Num1   Num2   Num3
Devin   224    18     845
Scott   322    32     652
Jim     179    46     929
Tim     202    41     562

Query Getting Max Value
---------------------------
Num1    Num2    Num3
322     46      929

I need to write a query to get the name of each of the highest values. It should look something like this:

Pseudo code:

SELECT [NAME] WHERE TABLE1.[NUM1] = QUERY.[NUM1]

My code as it stands now:

SELECT TABLE1.NAME
FROM TABLE1, [QUERY]
WHERE (("TABLE1.NUM1"="QUERY.NUM1"));

Does this make any sense? Is there a better way to do this?

 

[SteveSchapel]

DStevens,

1. Showing is often the best way of explaining! Good for you.

2. 'Name' is a Reserved Word (i.e. has a special meaning) in Access, and as such should not be used as the name of a field or control. Recommend to change this.

3. Try it like this:
SELECT TABLE1.YourNameField
FROM TABLE1 INNER JOIN QUERY ON TABLE1.NUM1 = QUERY.NUM1

You will have to decide what you want to do in the event of more than one Table1 record having the maximum value.

 

[DStevens]

DStevens,

1. Showing is often the best way of explaining! Good for you.

2. 'Name' is a Reserved Word (i.e. has a special meaning) in Access, and as such should not be used as the name of a field or control. Recommend to change this.

3. Try it like this:
SELECT TABLE1.YourNameField
FROM TABLE1 INNER JOIN QUERY ON TABLE1.NUM1 = QUERY.NUM1

You will have to decide what you want to do in the event of more than one Table1 record having the maximum value.

The naming of the tables and columns are pseudo-code.

I completely forgot about columns that share the max value. I'm going to have to rethink my strategy I think...

Thanks for the reply!

 

[Kryst51]

Also, You might have a normalization problem.... Storing something like Num1, Num2, Num3 in columns suggests that you do.... Those should be records in a different table that references the name.... Also, I would suggest adding a pk, autonumber ID to your tables, and referencing the number for your name in the table that you put the records Num1, num2 etc. This will make querying easier.

tblNames

pkNamesID - Autonumber, pk
FirstName
LastName

tblNumbers

pkAutonumberID - Autonumberpk
fkNameID - Number, relates to your tblNames, use the relationship screen to form this
NumField

Then you can build the query by pulling both of these tables into the query builder and use group by, and max.

 

[DStevens]

Also, You might have a normalization problem.... Storing something like Num1, Num2, Num3 in columns suggests that you do.... Those should be records in a different table that references the name.... Also, I would suggest adding a pk, autonumber ID to your tables, and referencing the number for your name in the table that you put the records Num1, num2 etc. This will make querying easier.

tblNames

pkNamesID - Autonumber, pk
FirstName
LastName

tblNumbers

pkAutonumberID - Autonumberpk
fkNameID - Number, relates to your tblNames, use the relationship screen to form this
NumField

Then you can build the query by pulling both of these tables into the query builder and use group by, and max.

Haha apparently I'm terrible at my pseudocode! The rows do have a Primary key auto number for the ID.

I'm not sure what you mean by putting the other numbers in a table. Why would I do that? I guess it just doesn't make sense to me having a table of names referencing a table of numbers with the same names in it. Is that what you're talking about?

Another question I have. Is it possible, and is it advisable, to have conditionals in a column?
For example:

table2
-------------------------
PK     RATING_ID
         RATING

Rating
Low
Medium
High

table3
-------------------------
PK     TABLE_ID
        TABLE_NAMER
FK     RATING
        RATING_ACCESS

Rating Access:
Tier 1
Tier 2
Tier 3
Tier 4
Tier 5

if table3 Rating = High
Rating Access includes all Tiers

if table 3 rating = Medium
Rating Access includes up to and including Tier 3

if table 3 rating = Low
Rating Access includes up to and including Tier 2

 

[Kryst51]

Haha apparently I'm terrible at my pseudocode! The rows do have a Primary key auto number for the ID.

I'm not sure what you mean by putting the other numbers in a table. Why would I do that? I guess it just doesn't make sense to me having a table of names referencing a table of numbers with the same names in it. Is that what you're talking about?

Read up HERE about normalization, it is very important to a well functioning and designed database.

 

[DStevens]

Read up HERE about normalization, it is very important to a well functioning and designed database.

Funny, I actually had just read that before you posted it ;P

 

[Kryst51]

Funny, I actually had just read that before you posted it ;P

Post back if you need more help with normalization in a new thread..... Also, once that is done and you are building your query, if you need help let us know.

 

[Kryst51]

Oh, one more thing. When posting, use real table names and field names. It is helpful in that you won't get asked irrelevant questions. Also by taking the time to post what is really going on you are more likely to get more accurate information; and it shows others that you value their time, by taking your time with your post. Good Luck

 

[DStevens]

Oh, one more thing. When posting, use real table names and field names. It is helpful in that you won't get asked irrelevant questions. Also by taking the time to post what is really going on you are more likely to get more accurate information; and it shows others that you value their time, by taking your time with your post. Good Luck

Noted. I think I've got the Database all separated out. Just gotta write queries to move the data to the new tables.

I would like to show real table names and such but I don't know if I'm allowed to. Sensitive info

I truly appreciate everyone's help! I may be back in the next few hours if I get hung up. A lot of the moving could cause some potential issues >.<

 

[Kryst51]

Noted. I think I've got the Database all separated out. Just gotta write queries to move the data to the new tables.

I would like to show real table names and such but I don't know if I'm allowed to. Sensitive info

Well that makes sense then. I guess then it might be prudent to say that in your post, or if that will always be the case, maybe add it to your sig.... But it depends on whether or not the questions about your names will bother you.

 

[Kryst51]

Quick question. I have my queries working correctly copying data to new tables. How can I tell what rows match up to the names? It just shows the ID number. Would it be wise to show the ID and the Name?

In the query you build to get your max, you will pull in the name field, but you don't want to store the name twice... That is the point of normalization, no duplication. So, when you get to the place where you need to see the actual name, post here with details and I'll help you.

 

[DStevens]

In the query you build to get your max, you will pull in the name field, but you don't want to store the name twice... That is the point of normalization, no duplication. So, when you get to the place where you need to see the actual name, post here with details and I'll help you.

Yeah brain fart. Forgot I could create a form to do the lookups and input data for me. Keep thinking I have to enter all data in the tables...

 

[Kryst51]

Yeah brain fart. Forgot I could create a form to do the lookups and input data for me. Keep thinking I have to enter all data in the tables...

The transition to DB thinking is difficult, it's definitely a learning curve.

 

[DStevens]

The transition to DB thinking is difficult, it's definitely a learning curve.

K, I'm kind of hung up so I want to see if I'm doing this correctly.

PERSON
PK     A_PERSON_ID       AutoNumber
        FIRST_NAME      Text
        SECOND_NAME   Text

NUMBERSET
PK     NUMBER_SET_ID  AutoNumber
FK     A_NAME_ID        Number
        NUMBER_1          Integer
        NUMBER_2          Integer
        NUMBER_3          Integer

Should it look like that? When I create a form I should base it off of NUMBERSET right?

 

[Kryst51]

K, I'm kind of hung up so I want to see if I'm doing this correctly.

PERSON
PK     A_PERSON_ID       AutoNumber
        FIRST_NAME      Text
        SECOND_NAME   Text
FK     NUMBER_SET_ID NUMBER
 
NUMBERSET
PK     NUMBER_SET_ID  AutoNumber
FK     A_NAME_ID        Number
        NUMBER_1          Integer
        NUMBER_2          Integer
        NUMBER_3          Integer

Should it look like that? Should all the information be visible from the PERSON table

Remove The FK NUMBER_SET_ID Number from your PERSON table. It doesn't belong.... Read up on building relationships. Basically there is a one to many relationship between your name and number, one name can have many numbers.... but one number will only ever have one name.... Therefore you need the reference to the name ID in the number table, but not the other way around. In the relationship screen build the relationship between the two tables.

IF a number can have more than one name, however, then it becomes a many -to- many relationship which means you would need a junction table.

 

[SteveSchapel]

DStevens,

The naming of the tables and columns are pseudo-code.

Just a hint for the future... this is always a bad idea.

People answering your questions are unable to directly see what you are doing, and are therefore hungry for every little hint and nuance that might help us to understand your application. Showing actual field and object names often helps in this process.

 

[Kryst51]

DStevens,


Just a hint for the future... this is always a bad idea.

People answering your questions are unable to directly see what you are doing, and are therefore hungry for every little hint and nuance that might help us to understand your application. Showing actual field and object names often helps in this process.

Yeah I told the OP that, but the OP said that it was sensitive info.