Brain tickler

[Daveisalwayshere]

Hi guys,

I have an agreement details table that i can query and get a rate table that looks like this depending on what Agreement type is picked

Rank Rate
GF $110
FM $90
JM $75
3rdyr $70
2ndyr $60
1styr $50

Thats the easy part, I need to figure out how to create a crew mix query where the user can input the Agreement Type, however many of each type of worker he wants and the end results will be (QTY of GF*GF Rate)+(QTY of FM*FM Rate)+(QTY of JM*JM Rate)+(QTY of3rdyr*3rdyear Rate)+(QTY of 2ndyr*2ndyr Rate)/total number of workers this would be the BLENDED RATE. I am at a loss as to where to start with this. would i create a crew size table and have the quantities entered on the form go there? in individual columns then run a make table query that will add the columns up but then how do you get it to multiply by the rates......
would i just keep making queries until i get the answer i want? or is there an easier way. I need to capture is the BLENDED RATE, and the crew mix so they can be part of the quote.

as you can see i am stumped. I am a beginner with access less than a few weeks experience. and could use all the help i can get

 

[WayneRyan]

Dave,

Assume you have:

tblRates
===========
Rank - Text (PK)
Rate - Currency

You need two new tables to define a crew:

tblCrew
=======
CrewID - AutoNumber (PK)
CrewName - Text

tblCrewMix
==========
CrewMixID - AutoNumber (PK)
CrewID - Number (FK to tblCrew)
Rank - Text (GF, FM, JM ...) Note: This can/SHOULD be a FK to your Rank/Rate Table
NumberOfPeople



Then, just do a query:

Select A.CrewName, Sum((B.Rank + B.NumberOfPeople) * c.Rate)
From   tblCrew as A Inner Join tblCrewMix As b On
          a.CrewID = b.CrewID Inner Join tblRates as c on
             b.Rank = c.Rank
Group By a.CrewName

hth,
Wayne

 

[Daveisalwayshere]

I keep getting a missing operator error when i paste this into a database. I made three tables exactly like the three described and created a query. when i go to SQL and paste this into it i get an error and have to close down the query to get out. keep in mind i have never worked in SQL before

 

[WayneRyan]

Dave,

Can you post the DB here?

Wayne

 

[Daveisalwayshere]

Sorry, I tried everything. My zipped size on this database is 2.11MB so i cant attach it yet. is there another way of attaching it?

 

[WayneRyan]

Dave,

Management --> Compact/Repair

Then ZIP.

hth,
Wayne

 

[Daveisalwayshere]

OK thanks. I will do that later tonight. It is a huge database and probably way over my head. It is a lot bigger than the Tradewinds sample database. And I will need all the help I can get. I did figure out the answer to my question though. I ended up using a query wizard and created a summary query with the three fields. [crewname] [rate] and ([numberofworkers]*[rate]). Grouped by crewname. Then I had to create a second query based on that to get the (sumofrates)/(sumofnumberofworkers). It would be nice to get that answer from one query but I'm OK with using 2. I'll post my DB and see if you can offer me some advice.

 

[Daveisalwayshere]

here it is. it is quite messy because i wasnt sure where to start