Query - Returning ID instead of Value...??

[Poolio]

I'm probably the newest of the Newbies on here...



... I have been asked to set up a Project-Profit-Time Managing tool... I got so far in Excel (24MB) before realising that it was databases I wanted, so been learning Access for a week... a rather steep learning curve!?

All going OK.

I've set up a table with Project details, one of the parameters (column headers) of which is a Multiplier (a value by which any individual's working on the scheme salaries are multiplied) - there are only 6 of these, varying from 1.6 to 2.8.

I have used another 2 column table with all of my Multipliers in, ID 1-6, values 1.6 to 2.8 as a lookup for my main Project Details table.

However, In my query the returned value I want is correct i.e. 1.6-2.8, but when I multiply it by the number of hours, it is multiplying by the ID value i.e. 1-6... can't see where I'm going wrong... any help please?

Poo

[orange]

Please show your table structures and the the query. We can only guess at your setup without these.

[Poolio]

Thanks orange - speedy reply!

Please show your table structures and the the query.

What's the standard way of doing this? Should I have read a "how-to" section before posting..??

Basically the query that I'm concerned with 'q_SchemeProfit' relates to 2 tables: 't_SchemeMASTER' and 't_MultiMASTER'.

One of the columns in SchemeMASTER has a lookup from MultiMASTER (which is a list of available Multipliers you can use)

'q_SchemeProfit' returns the Scheme Number, Name, Hours and is meant to return the SchemeMultilpier, so that I can simply multiply the Hours by the Multiplier, but it's using the ID instead of the Values...

The "Query" I'm using is....
SchemeMultiplier: [t_SchemeMASTER]![SchemeMultiplier]

Or do you want the SQL view:

SELECT t_SchemeMASTER.SchemeNumber, t_SchemeMASTER.SchemeName, t_StaffSchemeHrs.LastName, t_StaffMASTER.BaseRate, t_StaffSchemeHrs.Hours, [t_StaffMASTER]![BaseRate]*[t_StaffSchemeHrs]![Hours] AS StaffCost, [t_SchemeMASTER]![SchemeMultiplier] AS SchemeMultiplier
FROM t_StaffMASTER INNER JOIN (t_SchemeMASTER INNER JOIN t_StaffSchemeHrs ON t_SchemeMASTER.SchemeNumber = t_StaffSchemeHrs.SchemeName) ON t_StaffMASTER.StaffID = t_StaffSchemeHrs.LastName
ORDER BY t_SchemeMASTER.SchemeNumber;

Not sure how I show my Table Structures...??

Sorry if I'm an ill-educated pain...

[Poolio]

Just found this bit of info:

you should NOT be trying to put in lookups into your tables anyway. (see here for more info as to why)
You should use them at FORM level, not table/field level. Just because Microsoft included a feature doesn't mean it was a good idea nor that you should use it. {smile}
Bob Larson, Former Access MVP (2008-2010)

Would you agree??

[orange]

Yes, absolutely!

[Poolio]

Cheers pal...

So I list my Multipliers in a seperate table as IDs 1-7, and reference them in the Scheme Table by their ID, so when they're returned in a query it returns the values I want (which I can change)...
I think I'm treating 'my' Tables as Forms for 'me'... must do better
Poo