Creating an initial drop down list that will return just that person's information / Calculating Items

[keith]

This may seem simple, but I'm having a bit of trouble figuring out
exactly how to do it. I'm accessing a database through an ODBC link,
and I have a query that returns only jobs completed that day (it's
automatically generated). 1 user may finish 10, 20, or 50 jobs a day,
but there can be at least 20-50 users per day, and they're always going
to be different. The users aren't going to return, or if they do, it's
always going to be under different names.

I need some way to, when a person clicks on a shortcut, have Access
create a drop down list that returns all distinct names from column
USERDEFINED1 from the query that returns only that day's jobs. Once the
person chooses a name, only those jobs that the person did that day
would be listed on the screen.

In a different question, is there also a way to have the costs of those
jobs (calculated by multiplying columns AQW and PRS for each job)
automatically calculated and totaled in a report with the ability for
me to add items if necessary?

For instance, user "Riker" has three jobs in the list. Job 1 is 1 job
at $10.00, Job 2 is 3 jobs at $5.00, and Job 3 is 4 jobs at $25.00. Is
it possible to add them all up so that it totals $125, but with the
ability to add additional items (all I need to do is add a description
and a total, no need for calculation here) such as: New CD - $30.00 so
that in the end, its final calculation is $155?

Sorry for asking so much. I'm looking through Google and other
websites, and I'm getting a bit confused. Any help is much appreciated!

[keith]

Sorry, I meant to say that I'm using Access 2000 as well.

[pietlinden]

What do your relationships look like? How is your Jobs table
structured?

From what you've described, you could have something like a

Main/Subform with Main being based on User/Worker and the subform being
based on Job. Then you could have a calendar control on your form and
filter your subform based on that...

Umm... unique values from jobs...

SELECT DISTINCT [SomeField]
FROM tbl_Jobs
WHERE tbl_Jobs.Assign Date=Date()
AND WorkerName=Form s!MyForm!cboWor kerName;

Can you add up the totals? Yeah, if you use DSUM and an unbound
control on your form... If you want to add additional items, just make
the subform (based on tbl_Jobs) to allow additions (that's the
default,anyway, so you shouldn't have to do anything).

If you have to change which field your query is returning then you
probably have some normalization problems with your data.

So post your structure (just the parts relevant to your question) -
something like:

CREATE TABLE Worker(
WorkerID Long PRIMARY KEY,
FirstName Text(50),
LastName Text(50),
.....);

CREATE TABLE Job(
JobID Long PRIMARY KEY,
ToWorkerID Long,
AssignDate Date,
Description Text(255),
Price Currency,
CompleteDate Date,
....
FOREIGN KEY (ToWorkerID) REFERENCES Worker(WorkerID );

If you do it basically like this, solving your problem is simple.
Otherwise, it could get ugly. And if you can't get answers out of your
database, you've built it wrong.... so make sure it's right before you
stuff it with lots of data.

OKay, return just that person's info.
- create a main form/subform with Worker as the Main form recordsource
and the Job as the subform recordsource.
- use the combobox wizard to create a combobox on the Main (Worker)
form, and have it go to the record specified in.
- If you need the total of the worker's open jobs, use DSUM.

CREATE TABLE Job
(JobID

[King Ron]

The simplest method would be a Master/Child form combination.

1) Child: create a form and set its .DefaultView to 'Continuous Form'.
Bind the child's Form.RecordSour ce property to your daily detail level
data query. Be sure to include the primary key (evidently UserName) in
the RecordSource SQL statement. Add and format the fields you want to
see. Add a textbox to the Form footer area to your display the total
value.:

= Sum([JobAmount])

assuming 'JobAmount' is the name of the field with your detail order
amount.

3) Master: create another unbound form and add a comboBox. Set the
comboBox.Record Source to a SQL query that selects from the same data as
your Child form - GroupBy (and OrderBy?) the UserName. When you open
that form, each user name should appear once in the .comboBox dropdown.

4) Add a subform window to your Master form. Set the subform's
..SourceObject to your child form.Name. Set the .LinkMasterFiel ds to
your comboBox.Name, and the .LinkChildField s to the .UserName field in
the child form. You may need to resize the subform window to display
the records as you want to see them.

5) When you pick a UserName from the .comboBox, the related records
will be displayed in the subform. You should be able to scroll thru
each of the records or use the navigation buttons if you include them
in the child form's design. As long as the underlying recordset is
updatable, you will be able to edit or add records as needed. If you do
add a record, Access will add it to the underlying data source with the
UseName of the record currently in context in the .comboBox.. The total
textbox will display the total for the related records.

rock and roll,
King Ron of Chi

[keith]

Thanks for both of your responses! I'm going to try this and report
what happens. Thanks again!

King Ron wrote:

The simplest method would be a Master/Child form combination.

1) Child: create a form and set its .DefaultView to 'Continuous Form'. Bind the child's Form.RecordSour ce property to your daily detail level data query. Be sure to include the primary key (evidently UserName) in the RecordSource SQL statement. Add and format the fields you want to
see. Add a textbox to the Form footer area to your display the total
value.:

= Sum([JobAmount])

assuming 'JobAmount' is the name of the field with your detail order
amount.

3) Master: create another unbound form and add a comboBox. Set the
comboBox.Record Source to a SQL query that selects from the same data as your Child form - GroupBy (and OrderBy?) the UserName. When you open
that form, each user name should appear once in the .comboBox dropdown.
4) Add a subform window to your Master form. Set the subform's
.SourceObject to your child form.Name. Set the .LinkMasterFiel ds to
your comboBox.Name, and the .LinkChildField s to the .UserName field in the child form. You may need to resize the subform window to display
the records as you want to see them.

5) When you pick a UserName from the .comboBox, the related records
will be displayed in the subform. You should be able to scroll thru
each of the records or use the navigation buttons if you include them
in the child form's design. As long as the underlying recordset is
updatable, you will be able to edit or add records as needed. If you do add a record, Access will add it to the underlying data source with the UseName of the record currently in context in the .comboBox.. The total textbox will display the total for the related records.

rock and roll,
King Ron of Chi

[keith]

Thanks again for your help Ron and Pie.

I used a bit of your advice, but went a bit on my own.

I created a query that pulled all the unique names for that day. I then
created a list box that pulled that information and displayed it. Then,
I made a find query that had Like "*" & "*" in the criteria section.

I found out that you could create an action button that would run a
form. Well, I had a form made that was based on the find query. Then, I
made the button link between the list and the customer name so that the
unique names matched up with the customer name on the form. Finally, I
stripped the code from the button and added it to the OnClick Event of
the list. Now, I have a shortcut to that form on my desktop. I click
the shortcut, and a list of all the people who used the system today
appears. I click a person's name, and their receipt pops up,
automatically calculated and ready to go with all the jobs that they
had that day.

So now all I have to do is to figure out how to add values in a hidden
fashion so that I can have the "Add CD/Copies/Etc." option. Right now
when I try it, it says that I can't have null parameters for certain
fields. I'm thinking that if I have a big Add CD button that
essentially opens a new record, there's got to be code that I can
insert to have the other fields fill in (such as date at that second,
Username, etc...most of them can be filled by a fixed text variable)
automatically while leaving me the option to fill in the description
and price.

I'm thinking something in VBA will do this...perhaps an INSERT
statement, but since I don't know VBA, I have to keep searching the web
and Google Groups.

Thanks to all who helped!
Keith