PLS HELP need a solusion for a access problem

[frodeb]

Can anyone help me? I have made 2 table. Table1 have id,name,bodyweight,sex. Table2 have bodyweight,mail,femail. In my query i have placed all fild from table1 any added point. What i want to do is when i enter bodyweight the fild point return the value from table 2 based on sex and bodyweight. Any ide how i can make this work.... my e-mail is frode-b@online.no


tabel 1
id name bodywight sex
1 bob 63 M
2 Lisa 60 F

tabel 2
bodyweight mail femail
60 0,23 0,14
61 0,25 0,16
62 0,26 0,17
63 0,27 0,19

query
name bodyweight sex point
bob 63 m (i want this to return 0,27)
lisa 60 f (i want this to return 0,14)

 

[AvGuy]

Use a form to display the information in table one. To find the related information from table two write a query that get information from table two using as criteria information shown on the form.

Example: You show Bob, male, weight 63 on form. Query on table two uses weight *63) and sex (male) shown on form as criteria in query to return 0,27. On criteria line of query applied to table 2 put like this for weight:

Forms!MyForm!Weight

in sex block put

Forms!MyForm!Sex

When query executes it will use values shown on form to find value you want.

I hope you understand.
Cheers, AvGuy

 

[TonyJollans]

Hi frodeb,

Not the best table design, but the SQL you want is something like this ..

SELECT [Table1].[Name], [Table1].[BodyWeight], [Table1].[Sex],

IIF([Table1].[Sex]="M",[Table2].[Male],[Table2].[Female]) As Point

FROM [Table1] INNER JOIN [TABLE2] ON [Table1].[Bodyweight] = [Table2].[Bodyweight]

I see you are a brand new member here; I hope you find the site useful. One of the great things about the site is the public nature of all the questions and answers and you will find that members do not normally enter into private correspondence. Don't let that put you off though; please come back with any more questions you have - you will usually find someone ready and willing to post an answer.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[frodeb]

Thanks for replaying.

TonyJollans - Your ide workt, but i what i would like the query to do is: When a user enter 1 (or select 1 from a pulldown) in the fild [id], [name]=bob and [sex]=male(is lookup(this one i am using by relatianship lookup and is working) then the user enter bodyweight in the fild [bodyweight] and then fild [point] display(select) the correct value from table2 (i don`t know how to make this). Then a user can select a new name and so on. When the user is done he/she have a list of selected names with all the info. PS if the user enter a bodyweight value that is not in the list(table2) the fild point will display the closes value. eks ([bodywight]=65 (this is not in the list) [point] will return 0,27 (if male) and 0,19 (if female)

AvGuy - am sorry diden`t understand can you make a sample file?

PS! i am going to make a form of query1 later. I am also going to add a fild sum:[bodyweight]*[point]

 

[TonyJollans]

Hi frodeb,

I'm not sure I completely understand what you're trying to do, but on a Form, you could set the ControlSource of your Point field to ..

=Iif([Sex]="M",DLookup("Male","Table2","[BodyWeight]="&[BodyWeight]),DLookup("Female","Table2","[BodyWeight]="&[BodyWeight]))

.. which should get you the point value according to sex for aa bodyweight entered.

Finding the closest bodyweight if the actual one is not there is trickier - do you actually want the closest?

If you had, let's say, {60, 64, 72}

.. and the value was 67 .. then you want 64?
.. and the value was 69 .. then you want 72?
.. and the value was 68 .. then you want what?

Lastly, how do you want to build up your list of selected names? And what do you want to do with it afterwards? Do you want to write out a temporary (or permanent) table, and/or a report?

Sorry if this isn't an awful lot of help, or not really what you want - I'm not sure where you're at with Queries and Forms at the moment.


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.