Linking columns in two different tables

[bradp1979]

Shown below are tblEvent and tblRelEventEmployee. tblRelEventEmployee has a column named ShiftDate, and tblEvent has a column named EventDate. I need tblRelEventEmployee to pull the event date for each EventID from tblEvent and display it in ShiftDate. How can I do that?

[June7]

Did you try a query that joins the tables on the common EventID fields?

[bradp1979]

I tried, but I didn't know how to do it. I created a query, and the tables are showing, but I don't know how to link the two fields based on EventID, nor do I know how to use the query once I do.

[June7]

In query design - click on field in one table, hold and drag to field in other table. This will create the link line. Double click the line to open the relationship dialog. Set preferred JOIN type.

Save the query object. It can be used as the source for a report if you want.

I don't see any need for the event date to be stored in tblRelEventEmployees.

[orange]

I recommend you do some searching on youtube for creating queries in MSAccess.

Richard Rost(599CD) has a 10 or so set of tutorials here

Also, it is easier for readers to assist you if you give a simple, plain English description of what you are trying to do --no Access, no jargon.
Good luck with your project.

[bradp1979]

I don't see any need for the event date to be stored in tblRelEventEmployees.

Short answer: I need tblRelEventEmployee to have the date so that I can easily reference the column in a textbox that is counting the number of shifts from April 15th. The control for the subform is tblRelEventEmployee.

[bradp1979]

This may be an easier way of doing this: Can I modify tblRelEventEmployee so that instead of having "ShiftDate" as a column, instead having "EventDate" as the column, with the info being supplied by tblEvent?

[June7]

Call it ShiftDate or EventDate, my comment is the same - see no need for the date in tblRelEventEmployees.

Build a query that joins the tables on EventID fields and that will make the date available to each of the tblRelEventEmployees records.

Is the textbox on form or report?

[bradp1979]

The textbox is on a form.

[June7]

So this textbox is on subform bound to tblRelEventEmployee? Is the main form bound to tblEvent?

Why do you need to do this count on form? This kind of data calc is best done on report.

However, the subform RecordSource can probably be a query that includes both tables, just don't use INNER JOIN and don't try to edit tblEvent fields from the subform.

[bradp1979]

The reason it's on the form is that when I pull up an employee's information, I want there to be a box down at the bottom of the page that does a running count of all the events the employee has worked since 4/15/15. Since ShiftDate is blank, it's returning a 0

[June7]

Did you try the suggestion?

Alternative is to build and save query object and use domain aggregate (DCount, DLookup, etc.) expression in textbox.