SELECT [Individual Log].[Log Id], [Individual Log].Date, [Individual Log].Staff, [Individual Log].Additions, [Individual Log].Deletions, [Individual Log].Renewals, [Individual Log].Lapses, [Individual Log].Invoicing, [Individual Log].Underwriting, [Individual Log].Changes, [Individual Log].[New Group], [Individual Log].[New Group Members], [Individual Log].[Customer Requests], [Individual Log].Urgents, [Individual Log].Quotes, [Individual Log].[Quoted Members], [Individual Log].[Calls Received], Staff.[Hours Per Day]
FROM [Individual Log] INNER JOIN Staff ON [Individual Log].[Log Id] = Staff.[Staff Id Number]
WHERE ((([Individual Log].Date) Between [Please Enter From Date] And [Please Enter End Date]));
It is basically a parameter query on a table where the date is between two parameters.
There is also a field in there that is Staff.[Hours Per Day] i have just added this from a table called Staff and the field is obviously Called Hours Per Day.
I want this field to contain the correct info depending on the staff member that the query finds in the results when the query is run (The individual log and staff tables have the same info for the staffs names)
I think the mysql can just be changed to input this? Im I Wrong? ) Help!
what i meant was, i can't see your data so i don't know whether your JOIN, as written, would actually work -- or indeed whether this was even what you needed help with, because what you needed help with wasn't very clear
I have a form for a table both called [Individual Log]
I have on that form/table a textbox that is Called [Hours Per Day]
What i want to happen is: When that text box gets focus,
I want it to find the Hours Per Day info from a form/Table called [Staff]
Which also contains the field [Hours Per Day] (Preset with the info)
it needs however to find the correct hours per day info from the staff table from the record that corresponds with the staff name on both tables
I'm so sorry i can't explain it very well!!!!
Let me put it another way too!
I want the Individual log form to pull the hours per day figure for the correct staff member from the staff form
- Obviously the staff form/table contains all the staff with the hours per day for each staff member and the individual form contains the staff member name.
Ok i have tried to use the DLookup but i need to reference by a field that is not a primary key:
StaffNameChosen = Staff.Text
hpd = DLookup("[Hours Per Day]","Staff","[Staff Name]"= StaffNameChosen)
Staff Name is not the primary Key and i Cant'd Get the Staff Id Number from the form I am using as the combo box i am using stores the staff name chosen into the table not the ID Number because ut being used to create reports that cannot change.
The Combo Box i am using (Staff.Text) Therefore has this as the row
SELECT Staff.[Staff Name], Staff.[Staff Name] FROM Staff;
I have been stuck on this for days!