Add a field to Query

[Rita]

The user inputs the date the payroll ends every two weeks in a form (the
table just has one field). I use this date to run all my forms and reports.
This week the date is 10/11/08.

I want to list the clients on a form. They click a button to pull up the
client's payroll information form (this form contains a subreport will all
the details). This form will pull up if the client IDs match and the payroll
period ending date (this week we're using 10/11/08).

My query is very simple, I just list the client's last and first name. I'd
like to be able to pull in field [PayPeriodDate] from the table [PayPeriod].
I tried just adding the table and adding the field to the table, but I need
to establish a relationship.

 

[Ken Sheridan]

If the PayPeriod table has only one row, i.e. only the current PayPeriodDate
is entered then you don't need to join the tables as a Cartesian product of
the two tables will be returned, joining each client row to the one date row
e.g.

SELECT Firstname, LastName, PayPeriodDate
FROM Clients, PayPeriod
ORDER BY LastName, FirstName;

If the PayPeriod table contains multiple rows, i.e. the latest date is added
as new value in a new row rather than overwriting the one existing value,
then you can again return a Cartesian product of the tables, but this time
group by client and return the MAX date, e.g.

SELECT Firstname, LastName,
MAX(PayPeriodDate) AS LatestDate
FROM Clients, PayPeriod
GROUP BY LastName, FirstName;

These queries won't be updatable, however. For an updatable query you'd
have to use a domain function to look up the date e.g.

SELECT Firstname, LastName,
DMAX("PayPeriodDate", "PayPeriod") AS LatestDate
FROM Clients
ORDER BY LastName, FirstName;

Ken Sheridan
Stafford, England

 

[Rita]

Thanks so much!!!

If the PayPeriod table has only one row, i.e. only the current PayPeriodDate
is entered then you don't need to join the tables as a Cartesian product of
the two tables will be returned, joining each client row to the one date row
e.g.

SELECT Firstname, LastName, PayPeriodDate
FROM Clients, PayPeriod
ORDER BY LastName, FirstName;

If the PayPeriod table contains multiple rows, i.e. the latest date is added
as new value in a new row rather than overwriting the one existing value,
then you can again return a Cartesian product of the tables, but this time
group by client and return the MAX date, e.g.

SELECT Firstname, LastName,
MAX(PayPeriodDate) AS LatestDate
FROM Clients, PayPeriod
GROUP BY LastName, FirstName;

These queries won't be updatable, however. For an updatable query you'd
have to use a domain function to look up the date e.g.

SELECT Firstname, LastName,
DMAX("PayPeriodDate", "PayPeriod") AS LatestDate
FROM Clients
ORDER BY LastName, FirstName;

Ken Sheridan
Stafford, England

The user inputs the date the payroll ends every two weeks in a form (the
table just has one field). I use this date to run all my forms and reports.
This week the date is 10/11/08.

I want to list the clients on a form. They click a button to pull up the
client's payroll information form (this form contains a subreport will all
the details). This form will pull up if the client IDs match and the payroll
period ending date (this week we're using 10/11/08).

My query is very simple, I just list the client's last and first name. I'd
like to be able to pull in field [PayPeriodDate] from the table [PayPeriod].
I tried just adding the table and adding the field to the table, but I need
to establish a relationship.