queary help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with three fields..
ID is the Key
load_size
code

another table has fields ID, date, employee,
these are populated with a form that has an embedded form for the first
table.
the Id on the second form is a Key that is joined with code on the first
form. therfore I have a table with a date and employee and Id that matches a
table with several loads for that employee on that date. I want to print a
report that shows each load that employee took on a give date. help anyone
...
 
Since you didn't tell us the names of your tables, I'll take a stab

Table1: contains ID, [Load_size],
Code: 
Table2: contains ID, [Date], [Employee]  By the way.  Date is an Access
reserved word and should not be used as a field name. I recommend changing
it to something like [Delivery_Date] or [Load_Date] or something else that
accurately describes the meaning of the date.

Select Table2.Employee, Table2.[Load_Date], Table1.ID as Load_ID,
Table1.Load_size
FROM Table2 INNER JOIN Table1 on Table2.ID = Table1.Code.
ORDER ON Table2.Employee ASC, Table2.[Local_Date] DESC

I think this query will give you a list of employees and the loads they
carried, sorted by Employee and then descending by Load_Date.  If you want a
particular date, you will have to enter a WHERE clause to restrict the date.

HTH
Dale
 
Thanks Dale, Not exactly what I need yet. Here is what i wrote

SELECT dailyproduction_table.Employee,
dailyproduction_table.Production_Date, loadsize_table.ID,
loadsize_table.load_size
FROM dailyproduction_table INNER JOIN loadsize_table ON
dailyproduction_table.ID=loadsize_table.Code
ORDER BY dailyproduction_table.Employee,
dailyproduction_table.Production_Date DESC;
The result I'm looking for is more like..

Production_date, Employee, Load_size , Load_size , Load_size, Load_size ...
for how ever many loads this particular employee took on this particular day.
Easily I can show a total load_size for all loads and average load size but
I am looking to print a report that list each load size for a particular day
that each employee took. The report would have a column of drivers and a row
of loads for each driver for each day.

and thanks I forgot about the date thing, I did change it. thank you very
much for your help,

Dale Fye said:
Since you didn't tell us the names of your tables, I'll take a stab

Table1: contains ID, [Load_size],
Code: 
Table2: contains ID, [Date], [Employee]  By the way.  Date is an Access
reserved word and should not be used as a field name. I recommend changing
it to something like [Delivery_Date] or [Load_Date] or something else that
accurately describes the meaning of the date.

Select Table2.Employee, Table2.[Load_Date], Table1.ID as Load_ID,
Table1.Load_size
FROM Table2 INNER JOIN Table1 on Table2.ID = Table1.Code.
ORDER ON Table2.Employee ASC, Table2.[Local_Date] DESC

I think this query will give you a list of employees and the loads they
carried, sorted by Employee and then descending by Load_Date.  If you want a
particular date, you will have to enter a WHERE clause to restrict the date.

HTH
Dale


[QUOTE="brz"]
I have a table with three fields..
ID  is the Key
load_size
code

another table has  fields  ID, date, employee,
these are populated with a form that has an embedded form for the first
table.
the Id on the second form is a Key that is joined with code on the first
form. therfore I have a table with a date and employee and Id that matches
a
table with several loads for that employee on that date.  I want to print
a
report that shows each load that employee took on a give date.    help
anyone
..
[/QUOTE]
[/QUOTE]
Click to expand...
 
Do you actually want this in a report, or do you just want a query?

If you want to do this in a report, you might want to look into a
multi-column report, where each column represents a new driver, and the
following questions would be important.

How many loads would you expect each driver to have in a day?

How many drivers do you have?

If you want to do this as a query, the best method might be to use a cross
tab.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


brz said:
Thanks Dale, Not exactly what I need yet. Here is what i wrote

SELECT dailyproduction_table.Employee,
dailyproduction_table.Production_Date, loadsize_table.ID,
loadsize_table.load_size
FROM dailyproduction_table INNER JOIN loadsize_table ON
dailyproduction_table.ID=loadsize_table.Code
ORDER BY dailyproduction_table.Employee,
dailyproduction_table.Production_Date DESC;
The result I'm looking for is more like..

Production_date, Employee, Load_size , Load_size , Load_size, Load_size ...
for how ever many loads this particular employee took on this particular day.
Easily I can show a total load_size for all loads and average load size but
I am looking to print a report that list each load size for a particular day
that each employee took. The report would have a column of drivers and a row
of loads for each driver for each day.

and thanks I forgot about the date thing, I did change it. thank you very
much for your help,

Dale Fye said:
Since you didn't tell us the names of your tables, I'll take a stab

Table1: contains ID, [Load_size],
Code: 
Table2: contains ID, [Date], [Employee]  By the way.  Date is an Access
reserved word and should not be used as a field name. I recommend changing
it to something like [Delivery_Date] or [Load_Date] or something else that
accurately describes the meaning of the date.

Select Table2.Employee, Table2.[Load_Date], Table1.ID as Load_ID,
Table1.Load_size
FROM Table2 INNER JOIN Table1 on Table2.ID = Table1.Code.
ORDER ON Table2.Employee ASC, Table2.[Local_Date] DESC

I think this query will give you a list of employees and the loads they
carried, sorted by Employee and then descending by Load_Date.  If you want a
particular date, you will have to enter a WHERE clause to restrict the date.

HTH
Dale


[QUOTE="brz"]
I have a table with three fields..
ID  is the Key
load_size
code

another table has  fields  ID, date, employee,
these are populated with a form that has an embedded form for the first
table.
the Id on the second form is a Key that is joined with code on the first
form. therfore I have a table with a date and employee and Id that matches
a
table with several loads for that employee on that date.  I want to print
a
report that shows each load that employee took on a give date.    help
anyone
..
[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Yes dale, The report is my goal. I cannot seem to get these loads to show up
side by side on a report. I can get them in a column. So I was trying to
query them first. I did take a stab at a cross query. I had drivers in the
column then tryed puting the load codes across and tried loads across I'm
not sure how to do a cross tab. would it be easier to do this in a report
if so I can pose this question under reports. Finally there could be
anywhere from 1 to 25 drivers in a day and they could take from 1 to 14 loads
per day. Do I make any sense. I have a data base that currently does this
but it is my firs and instead of having just [load] as a field I have
[load_1],[Load_2].....[Load_14] so on a report and for all calculations I
have a lot of zeros and everyone has 14 loads so there are a lot of iifs and
junk. I'm trying to clean it up.

Dale Fye said:
Do you actually want this in a report, or do you just want a query?

If you want to do this in a report, you might want to look into a
multi-column report, where each column represents a new driver, and the
following questions would be important.

How many loads would you expect each driver to have in a day?

How many drivers do you have?

If you want to do this as a query, the best method might be to use a cross
tab.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


brz said:
Thanks Dale, Not exactly what I need yet. Here is what i wrote

SELECT dailyproduction_table.Employee,
dailyproduction_table.Production_Date, loadsize_table.ID,
loadsize_table.load_size
FROM dailyproduction_table INNER JOIN loadsize_table ON
dailyproduction_table.ID=loadsize_table.Code
ORDER BY dailyproduction_table.Employee,
dailyproduction_table.Production_Date DESC;
The result I'm looking for is more like..

Production_date, Employee, Load_size , Load_size , Load_size, Load_size ...
for how ever many loads this particular employee took on this particular day.
Easily I can show a total load_size for all loads and average load size but
I am looking to print a report that list each load size for a particular day
that each employee took. The report would have a column of drivers and a row
of loads for each driver for each day.

and thanks I forgot about the date thing, I did change it. thank you very
much for your help,

Dale Fye said:
Since you didn't tell us the names of your tables, I'll take a stab

Table1: contains ID, [Load_size],
Code: 
Table2: contains ID, [Date], [Employee]  By the way.  Date is an Access
reserved word and should not be used as a field name. I recommend changing
it to something like [Delivery_Date] or [Load_Date] or something else that
accurately describes the meaning of the date.

Select Table2.Employee, Table2.[Load_Date], Table1.ID as Load_ID,
Table1.Load_size
FROM Table2 INNER JOIN Table1 on Table2.ID = Table1.Code.
ORDER ON Table2.Employee ASC, Table2.[Local_Date] DESC

I think this query will give you a list of employees and the loads they
carried, sorted by Employee and then descending by Load_Date.  If you want a
particular date, you will have to enter a WHERE clause to restrict the date.

HTH
Dale


I have a table with three fields..
ID  is the Key
load_size
code

another table has  fields  ID, date, employee,
these are populated with a form that has an embedded form for the first
table.
the Id on the second form is a Key that is joined with code on the first
form. therfore I have a table with a date and employee and Id that matches
a
table with several loads for that employee on that date.  I want to print
a
report that shows each load that employee took on a give date.    help
anyone
..
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
would it be possible to add a text box to the form that is used to input
[Load] and for each load that is put in it woul populate the text box with a
count number. then I could sort drivers in a column and count numbers in
the row to queary the load size for drivers and have many loads.

brz said:
Yes dale, The report is my goal. I cannot seem to get these loads to show up
side by side on a report. I can get them in a column. So I was trying to
query them first. I did take a stab at a cross query. I had drivers in the
column then tryed puting the load codes across and tried loads across I'm
not sure how to do a cross tab. would it be easier to do this in a report
if so I can pose this question under reports. Finally there could be
anywhere from 1 to 25 drivers in a day and they could take from 1 to 14 loads
per day. Do I make any sense. I have a data base that currently does this
but it is my firs and instead of having just [load] as a field I have
[load_1],[Load_2].....[Load_14] so on a report and for all calculations I
have a lot of zeros and everyone has 14 loads so there are a lot of iifs and
junk. I'm trying to clean it up.

Dale Fye said:
Do you actually want this in a report, or do you just want a query?

If you want to do this in a report, you might want to look into a
multi-column report, where each column represents a new driver, and the
following questions would be important.

How many loads would you expect each driver to have in a day?

How many drivers do you have?

If you want to do this as a query, the best method might be to use a cross
tab.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


brz said:
Thanks Dale, Not exactly what I need yet. Here is what i wrote

SELECT dailyproduction_table.Employee,
dailyproduction_table.Production_Date, loadsize_table.ID,
loadsize_table.load_size
FROM dailyproduction_table INNER JOIN loadsize_table ON
dailyproduction_table.ID=loadsize_table.Code
ORDER BY dailyproduction_table.Employee,
dailyproduction_table.Production_Date DESC;
The result I'm looking for is more like..

Production_date, Employee, Load_size , Load_size , Load_size, Load_size ...
for how ever many loads this particular employee took on this particular day.
Easily I can show a total load_size for all loads and average load size but
I am looking to print a report that list each load size for a particular day
that each employee took. The report would have a column of drivers and a row
of loads for each driver for each day.

and thanks I forgot about the date thing, I did change it. thank you very
much for your help,

:

Since you didn't tell us the names of your tables, I'll take a stab

Table1: contains ID, [Load_size],
Code: 
Table2: contains ID, [Date], [Employee]  By the way.  Date is an Access
reserved word and should not be used as a field name. I recommend changing
it to something like [Delivery_Date] or [Load_Date] or something else that
accurately describes the meaning of the date.

Select Table2.Employee, Table2.[Load_Date], Table1.ID as Load_ID,
Table1.Load_size
FROM Table2 INNER JOIN Table1 on Table2.ID = Table1.Code.
ORDER ON Table2.Employee ASC, Table2.[Local_Date] DESC

I think this query will give you a list of employees and the loads they
carried, sorted by Employee and then descending by Load_Date.  If you want a
particular date, you will have to enter a WHERE clause to restrict the date.

HTH
Dale


I have a table with three fields..
ID  is the Key
load_size
code

another table has  fields  ID, date, employee,
these are populated with a form that has an embedded form for the first
table.
the Id on the second form is a Key that is joined with code on the first
form. therfore I have a table with a date and employee and Id that matches
a
table with several loads for that employee on that date.  I want to print
a
report that shows each load that employee took on a give date.    help
anyone
..
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top