Need to know if this can be done. (1 Viewer)

[ltiner]

Here's the deal...

Developing a payroll system.
I have 3 tables:
One for employee information.
One for archiving current years earnings (each record = 1 week)
One for current earnings. (Identical to the archive table).

On the form for entering current earnings, I need the employees totals from the archive file as well as a lot of the info in the employee information table.

Heres the part i need help with...
What I think would be the ideal situation would be to create a query that would show the total earnings for each employee. (I've done this part and it works fine). THEN create another query that would include the employee information table and the aforementioned "totals" query. Then this query would be the data source for the data entry form.

Here's the problem....
The query based on the table and the totals query, only shows records for the employee's that have historical earnings. I understand why, but I need to have all employees in the employee info table displayed in the query (if they have no historical earnings, it would show zeros or nulls.

Is there anyway to make this happen?

Plan B is to use Dlookup from within the form, and I have done this and it works, but using the query would be more "elegant" and require less vba.

Thx

Larry

 

[David R]

Try an unequal join

Change the Join type between your two data sources to read "Include ALL records from tableEarnings and only those records from tableArchived that..." It'll be one of three options when you right click on the join line and select Join Properties.

 

[ltiner]

Worked prefectly!

Now..... I'm getting greedy. Can this query be "tweaked" to allow for adding records (to the employee info table)?

Thx

Larry

 

[David R]

Ummm...huh?

Doesn't it do so now?

I was assuming this is a subform of sorts, of the Employees form, that shows their earnings. They should be linked by EmployeeID, not in the same query together.

Post your queries in SQL format if you please; perhaps I'm misunderstanding what you're asking.

 

[ltiner]

I'm not at the computer that has the DB in question, so let me try to explain:

1. I have a table "employee Info".

2. I have a table "Earnings History" in which each weeks earnings is contained in a record.

3. I have a totals query that is based on the "Earnings History" table (2 above), that has a row "total earnings" for each employee.

4. I created another query that is based on the "employee info" Table and the "total earnings" Query (3 above). (This is the one that you showed me how to change the relationship properties.) If you run this query, the "add record" * is greyed out. This query is the control source for the form used to enter current earnings. (The current earnings go into another table associated with a sub/form, but I don't think that is relevent to our discussion). Everything I've outlined so far, works fine. I have no need to add employee records on the "Current Earnings" form, I am just adding earnings to existing employees.

However, I was thinking that if I could get this query to allow for the addition of Employees (employee info). Then I could use this query as the control source for the "Employee Info" form and then I could show Year to date earnings on the form.


If this doesn't make sense, I'll be at this DB later and I will post the query(s).


Larry

 

[David R]

I think you're overdefining your query...

Don't try to make one query fit all situations. Because it won't yet have records in the Totals query, it may not allow you to enter a new Employee in Query 4.

Use a table based on Query 1 to input new Employees. Like I said, if you want to have your totals query (Query 4) shown in a subform of that form, that would be easy enough to do, and appropriately separate design.

Someone else may disagree. I am by no means an expert, and I haven't actually constructed a test database like yours. But I've found users generally get less confused with specific forms for specific purposes.

 

[ltiner]

I think you're right.

Thanks

Larry