Re: Form problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

"tope12" <tope12@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:63F2C7F6-C915-4DB5-88A4-D236304DB7CB@xxxxxxxxxxxxx
> the realtionship between the department table and budget tables is a
> 1:M relationship. They're connected by the employee_department
> attribute (PK in department, FK in budget). Department is connected
> to the meeting table through the employee table. The Department and
> Employee table is a 1:M connection through the employee_department
> field. The employee and meeting tables is a 1:M relationship.
> They're connected by the employee_code attribute (PK in employee, FK
> in Meeting).

Thanks, that helps. I'm not sure I've figure out exactly what you're
after, so check if these assumptions are correct:

1. Your form is supposed to be based on the table Budget, or on an
updatable query of that table.

2. The form should show the record/all records for a specific
department, to be specified by a prompt when the user opens the form.

3. You want to show on that form the sum of Meeting.meeting_actcost for
every employee in that department.

If these assumptions are correct, I think I might handle it as follows:

1. Create a new query named "qryDeptMeetingCost" with SQL like this:

SELECT
employee_department As Dept,
Sum(meeting_actcost) As budused
FROM Employee INNER JOIN Meeting
ON Employee.employee_code = Meeting.employee_code
GROUP BY employee_department;

2. Let your form be based on a recordsource query like this:

SELECT
employee_department
ibud,
cbud,
DLookup("budused", "qryDeptMeetingCost",
"Dept='" & [employee_department] & "'")
FROM Budget
WHERE employee_department = [Enter Branch];

Note: that assumes that the department field is a text field, which it
may not be. If it's a number field, then the criterion for the DLookup
expression should be

"Dept=" & [employee_department])

I have hope that something along those lines is what you're looking for.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Relevant Pages

  • Re: Form problem
    ... FK in budget). ... The employee and meeting tables is a 1:M relationship. ... > Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: Word 2003 SQL Express Datasource
    ... "Andrew Kennard" wrote in message ... PS wierd one about the connection string Word creates in the marcro record is truncated? ... Employee ID, Employee name, Manager ID, Manager name ... NOT NULL CONSTRAINT ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Word 2003 SQL Express Datasource
    ... The alias seems to hjave done the trick and the odc file I am using is ... Employee ID, Employee name, Manager ID, Manager name ... I've already just tried leaving out the Connection and SQL and I've ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Help with datasets
    ... Dim connection As New ... Dim connection2 As New ... ' Create a SqlDataAdapter for the Employee table. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: My Superior Performance Rating...
    ... | to the fact that in my case, it was an absolutely ridiculous process ... hard line budget for raises, but they only budgeted 3% per employee. ... directly from someone else's raise in the department. ...
    (rec.sport.football.college)