Jerry, I have made a queary with a rank. I used this sql
SELECT Query1.Production_Date, Query1.Employee, Query1.Truck_num,
Query1.load_size, Query1.Code, Query1.L_ID
(SELECT Count(*)
FROM [Query1] AS T
WHERE T.code = [Query1].code
AND T.L_ID <= [Query1].L_ID) AS Rank
FROM Query1;
this has a queary where the rank is 1,2,3... for each load for each employee
on a specific day and for the same day each employee starts their first load
with 1 then 2...ect.
I keep getting an error in my crosstab query. could you look at this and
tell where the error is coming from.
SELECT Query1.Production_Date, Query1.Employee, Query1.Truck_num,
Query1.load_size, Query1.Code, Query1.L_ID
(SELECT Count(*)
FROM [Query1] AS T
WHERE T.code = [Query1].code
AND T.L_ID <= [Query1].L_ID) AS Rank
FROM Query1;
and this is Query 1
SELECT dailyproduction_table.Production_Date,
dailyproduction_table.Employee, dailyproduction_table.Truck_num,
loadsize_table.Code, loadsize_table.load_size, loadsize_table.L_ID
FROM dailyproduction_table INNER JOIN loadsize_table ON
dailyproduction_table.P_ID=loadsize_table.Code;
thanks for your help Jerry, I apreciate all the people that post help on
this site. your all great and knowlegable. and a great asset us that are new
at this.
Thanks again
brz said:
Thanks again for your response Jerry.
I have tried to queary many different ways and make a cross tab queary.
cannot seem to get the correct results.. I believe I am on the right track
that was on another post, but cant seem to get the syntax correct.. I need to
Rank the code field for each drive on each day. Right now I have [date]
[employee] and all [loads] and a
Code:
field for that employee on that
[date] with a code number that is specific to only that driver on that day
because it is bound with the KEY ID on the production side of the form. if i
could rank that field the code field where the firs load would be 1 then
second load a 2 then the third load a 3 and so on then the next employee even
though the code is a 7 or 8 would rank all the 7s starting with 1 then 2 then
3 and so on. I could cross tab by using all the rank=1 rank =2 rank = 3
and so on for each driver.
[QUOTE="Jerry Whittle"]
You may be trying to do much at one time. A crosstab looks like it might work
for most of it though.
First thing create a plain old select query that gathers the data that you
what to see. Just the data. Don't worry about sorting or grouping it. Name
the query something like qryDaily.
Next create a crosstab query based on qryDaily. See if that gets you in the
ballpark.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Thank you for your response Jerry,
What i tried was applying other database info to my data base. He is my
problem back to the basics.
I have 2 tables
loadsize_table with fields Id, load size and code ID being the Key
production_table with fields Id, production date, employee, truck
These tables are joined [loadsize_table].code = [production_table].ID
I populate these tables with a form that has an ebedded form. results
production table gives me an employee and a date in a truck that has many
loads that have the same code as id for that employee on that date. All of
this works fine.
Problem is the report I want. A report that groups by Day then by employee
and list all loads for that employee on that day.
To look something like this.
Date
Employee truck load load load load load.... up to 14 load per day
Employee truck load load load load load......and so on
for as many employees as needed for that day. Then of coarse totals like
average load size for each driver and total load size for the day and number
of loads for each .. ect.
I am a novice at this and have looked at crosstab queary and multi column
reports but can't seem to get the results i'm looking for. What would you
suguest.
Thanks very much
"bob" the rookie
:
Unless there's a cut and paste error or typo's you are missing some [].
[dailyproduction_table.Employee],
should be
[dailyproduction_table].[Employee],
This can't work as you are calling from two different tables. You need to
remove either the T or [dailyproduction_table.
WHERE T1.[dailyproduction_table].[Employee] =
T.[dailyproduction_table.Employee]
Actually there are a bunch of problems with that SQL statement so let's get
back to basics. What are you trying to accomplish?
What are the primary keys for dailyproduction_table and loadsize_table? How
are they related to each other?
Who the heck is Bob?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I have tried to apply some infromation off the message boards to my Data Base.
I have exhausted all the Help from Microsoft office and cannot seem to fix
this bug.
THis is a copy of the statement I have
SELECT
[dailyproduction_table.Employee],[dailyproduction_table.Production_Date],[
loadsize_table.ID, loadsize_table.load_size], loadsize_table.score
FROM dailyproduction_table INNER JOIN loadsize_table ON
dailyproduction_table.ID = loadsize_table.Code,(SELECT COUNT(*)
FROM [Bob]T1
WHERE T1.[dailyproduction_table.Employee] = T.[dailyproduction_table.Employee]
AND T1.Score >= T.Score) AS Rank FROM [Bob] AS T
ORDER BY [dailyproduciton_table.Employee],Score DESC;
I am using Access 2002 and the error seems to be at the last FROM
statement.
Any help please.
Thanks[/QUOTE][/QUOTE][/QUOTE]
