Relating data in 2 fields

G

Guest

In my query I am trying to relate a PRStaff field containing an alpha list of
names and a DayNum field containing the numeric value of the days of a
selected month (with weekends filtered out).

For each different DayNum, I need the PRStaff field to display one name.
When the PRStaff names run out I want them to start over. I want thePRStaff
name list to repeat until there is a name that corresponds to each day of the
month. My ultimate goal is to display a calendar with one name showing for
each work day of the month.

Here's an example of the query results I want if I have 3 names in the
PRStaff list:

PRStaff DayNum
Name1 1
Name2 2
Name3 3
Name1 4
Name2 5
Name3 6
Name1 7

Right now my query causes each DayNum to repeat the same number of times as
there are names in the PRStaff field:
PRStaff DayNum
Name1 1
Name2 1
Name3 1
Name1 2
Name2 2
Name3 2
Name1 3
Name2 3
Name3 3
Name1 4
Name2 4
Name3 4


Here's my SQL statement:

SELECT qryPhoneReliefList.PRStaff, Q_ApptsReportMain.YearNum,
Q_ApptsReportMain.MonthNum, Q_ApptsReportMain.DayNum,
Q_ApptsReportMain.WkDay, Q_ApptsReportMain.CalDate
FROM qryPhoneReliefList, Q_ApptsReportMain
WHERE (([Q_ApptsReportMain]![WkDay]<>1 And [Q_ApptsReportMain]![WkDay]<>7));

I know my mistake is basic and obvious, but I can't see it for the life of
me. I apologize if this has been answered before, but I've searched the
discussion group and haven't been able to find the answer.

Thank you in advance for any help you can provide.
 
S

Smartin

I''ve a feeling I''m not in Unix Anymore said:
In my query I am trying to relate a PRStaff field containing an alpha list of
names and a DayNum field containing the numeric value of the days of a
selected month (with weekends filtered out).

For each different DayNum, I need the PRStaff field to display one name.
When the PRStaff names run out I want them to start over. I want thePRStaff
name list to repeat until there is a name that corresponds to each day of the
month. My ultimate goal is to display a calendar with one name showing for
each work day of the month.

Here's an example of the query results I want if I have 3 names in the
PRStaff list:

PRStaff DayNum
Name1 1
Name2 2
Name3 3
Name1 4
Name2 5
Name3 6
Name1 7

Right now my query causes each DayNum to repeat the same number of times as
there are names in the PRStaff field:
PRStaff DayNum
Name1 1
Name2 1
Name3 1
Name1 2
Name2 2
Name3 2
Name1 3
Name2 3
Name3 3
Name1 4
Name2 4
Name3 4


Here's my SQL statement:

SELECT qryPhoneReliefList.PRStaff, Q_ApptsReportMain.YearNum,
Q_ApptsReportMain.MonthNum, Q_ApptsReportMain.DayNum,
Q_ApptsReportMain.WkDay, Q_ApptsReportMain.CalDate
FROM qryPhoneReliefList, Q_ApptsReportMain
WHERE (([Q_ApptsReportMain]![WkDay]<>1 And [Q_ApptsReportMain]![WkDay]<>7));

I know my mistake is basic and obvious, but I can't see it for the life of
me. I apologize if this has been answered before, but I've searched the
discussion group and haven't been able to find the answer.

Thank you in advance for any help you can provide.
Click to expand...

Really guessing here, but...

Say DayNum is an unbound set of integers (in the mathematical sense)
starting at 1 and PRStaff names are indexed 1 to N.

You might be able to include in the where clause something like

AND (DayNum MOD N + 1) = (qryPhoneReliefList.PRStaffID + 1)

This assumes especially qryPhoneReliefList includes an index on PRStaff,
and there is a regular progression of PRStaff indices beginning with 1.
Where DayNum starts and ends isn't so important, but there might be
irregularities if DayNum repeats (e.g., at the beginning of a
week/month/year).

HTH
 
G

Guest

Thank you, Smartin, I appreciate your help. I haven't worked with indexes
before so I'm having to get up to speed on them before I can try your
solution.
Smartin said:
I''ve a feeling I''m not in Unix Anymore said:
In my query I am trying to relate a PRStaff field containing an alpha list of
names and a DayNum field containing the numeric value of the days of a
selected month (with weekends filtered out).

For each different DayNum, I need the PRStaff field to display one name.
When the PRStaff names run out I want them to start over. I want thePRStaff
name list to repeat until there is a name that corresponds to each day of the
month. My ultimate goal is to display a calendar with one name showing for
each work day of the month.

Here's an example of the query results I want if I have 3 names in the
PRStaff list:

PRStaff DayNum
Name1 1
Name2 2
Name3 3
Name1 4
Name2 5
Name3 6
Name1 7

Right now my query causes each DayNum to repeat the same number of times as
there are names in the PRStaff field:
PRStaff DayNum
Name1 1
Name2 1
Name3 1
Name1 2
Name2 2
Name3 2
Name1 3
Name2 3
Name3 3
Name1 4
Name2 4
Name3 4


Here's my SQL statement:

SELECT qryPhoneReliefList.PRStaff, Q_ApptsReportMain.YearNum,
Q_ApptsReportMain.MonthNum, Q_ApptsReportMain.DayNum,
Q_ApptsReportMain.WkDay, Q_ApptsReportMain.CalDate
FROM qryPhoneReliefList, Q_ApptsReportMain
WHERE (([Q_ApptsReportMain]![WkDay]<>1 And [Q_ApptsReportMain]![WkDay]<>7));

I know my mistake is basic and obvious, but I can't see it for the life of
me. I apologize if this has been answered before, but I've searched the
discussion group and haven't been able to find the answer.

Thank you in advance for any help you can provide.
Click to expand...

Really guessing here, but...

Say DayNum is an unbound set of integers (in the mathematical sense)
starting at 1 and PRStaff names are indexed 1 to N.

You might be able to include in the where clause something like

AND (DayNum MOD N + 1) = (qryPhoneReliefList.PRStaffID + 1)

This assumes especially qryPhoneReliefList includes an index on PRStaff,
and there is a regular progression of PRStaff indices beginning with 1.
Where DayNum starts and ends isn't so important, but there might be
irregularities if DayNum repeats (e.g., at the beginning of a
week/month/year).

HTH
Click to expand...
 
S

Smartin

I apologize, my use of the word "index" was totally misleading in this
context.

All I meant was you have a nice orderly progression of (unique) numbers
linked to your people. It doesn't necessarily have to be a table index.
It's important the numbers begin with 1, and have no gaps. I'm thinking
the staff table should look like this:

PK PrStaffID EmpName
====================================
<autonumber> 1 Fred
<autonumber> 2 Wilma
<autonumber> 3 Barney

Hope this clarifies!


I''ve a feeling I''m not in Unix Anymore said:
Thank you, Smartin, I appreciate your help. I haven't worked with indexes
before so I'm having to get up to speed on them before I can try your
solution.
Smartin said:
I''ve a feeling I''m not in Unix Anymore said:
In my query I am trying to relate a PRStaff field containing an alpha list of
names and a DayNum field containing the numeric value of the days of a
selected month (with weekends filtered out).

For each different DayNum, I need the PRStaff field to display one name.
When the PRStaff names run out I want them to start over. I want thePRStaff
name list to repeat until there is a name that corresponds to each day of the
month. My ultimate goal is to display a calendar with one name showing for
each work day of the month.

Here's an example of the query results I want if I have 3 names in the
PRStaff list:

PRStaff DayNum
Name1 1
Name2 2
Name3 3
Name1 4
Name2 5
Name3 6
Name1 7

Right now my query causes each DayNum to repeat the same number of times as
there are names in the PRStaff field:
PRStaff DayNum
Name1 1
Name2 1
Name3 1
Name1 2
Name2 2
Name3 2
Name1 3
Name2 3
Name3 3
Name1 4
Name2 4
Name3 4


Here's my SQL statement:

SELECT qryPhoneReliefList.PRStaff, Q_ApptsReportMain.YearNum,
Q_ApptsReportMain.MonthNum, Q_ApptsReportMain.DayNum,
Q_ApptsReportMain.WkDay, Q_ApptsReportMain.CalDate
FROM qryPhoneReliefList, Q_ApptsReportMain
WHERE (([Q_ApptsReportMain]![WkDay]<>1 And [Q_ApptsReportMain]![WkDay]<>7));

I know my mistake is basic and obvious, but I can't see it for the life of
me. I apologize if this has been answered before, but I've searched the
discussion group and haven't been able to find the answer.

Thank you in advance for any help you can provide.
Click to expand...
Really guessing here, but...

Say DayNum is an unbound set of integers (in the mathematical sense)
starting at 1 and PRStaff names are indexed 1 to N.

You might be able to include in the where clause something like

AND (DayNum MOD N + 1) = (qryPhoneReliefList.PRStaffID + 1)

This assumes especially qryPhoneReliefList includes an index on PRStaff,
and there is a regular progression of PRStaff indices beginning with 1.
Where DayNum starts and ends isn't so important, but there might be
irregularities if DayNum repeats (e.g., at the beginning of a
week/month/year).

HTH
Click to expand...
Click to expand...
 
G

Guest

Thank you again for your help and for your clarification. After I tried
creating a table index and wasn't able to manipulate it to get the results I
needed, I realized that what you had meant was for me to create some sort of
ID number associated with each person. I knew I was missing something very
basic and you helped me realize what it was -- I was trying to associate
apples with oranges (a text field with a number field).

Smartin said:
I apologize, my use of the word "index" was totally misleading in this
context.

All I meant was you have a nice orderly progression of (unique) numbers
linked to your people. It doesn't necessarily have to be a table index.
It's important the numbers begin with 1, and have no gaps. I'm thinking
the staff table should look like this:

PK PrStaffID EmpName
====================================
<autonumber> 1 Fred
<autonumber> 2 Wilma
<autonumber> 3 Barney

Hope this clarifies!


I''ve a feeling I''m not in Unix Anymore said:
Thank you, Smartin, I appreciate your help. I haven't worked with indexes
before so I'm having to get up to speed on them before I can try your
solution.
Smartin said:
I''ve a feeling I''m not in Unix Anymore wrote:
In my query I am trying to relate a PRStaff field containing an alpha list of
names and a DayNum field containing the numeric value of the days of a
selected month (with weekends filtered out).

For each different DayNum, I need the PRStaff field to display one name.
When the PRStaff names run out I want them to start over. I want thePRStaff
name list to repeat until there is a name that corresponds to each day of the
month. My ultimate goal is to display a calendar with one name showing for
each work day of the month.

Here's an example of the query results I want if I have 3 names in the
PRStaff list:

PRStaff DayNum
Name1 1
Name2 2
Name3 3
Name1 4
Name2 5
Name3 6
Name1 7

Right now my query causes each DayNum to repeat the same number of times as
there are names in the PRStaff field:
PRStaff DayNum
Name1 1
Name2 1
Name3 1
Name1 2
Name2 2
Name3 2
Name1 3
Name2 3
Name3 3
Name1 4
Name2 4
Name3 4


Here's my SQL statement:

SELECT qryPhoneReliefList.PRStaff, Q_ApptsReportMain.YearNum,
Q_ApptsReportMain.MonthNum, Q_ApptsReportMain.DayNum,
Q_ApptsReportMain.WkDay, Q_ApptsReportMain.CalDate
FROM qryPhoneReliefList, Q_ApptsReportMain
WHERE (([Q_ApptsReportMain]![WkDay]<>1 And [Q_ApptsReportMain]![WkDay]<>7));

I know my mistake is basic and obvious, but I can't see it for the life of
me. I apologize if this has been answered before, but I've searched the
discussion group and haven't been able to find the answer.

Thank you in advance for any help you can provide.

Really guessing here, but...

Say DayNum is an unbound set of integers (in the mathematical sense)
starting at 1 and PRStaff names are indexed 1 to N.

You might be able to include in the where clause something like

AND (DayNum MOD N + 1) = (qryPhoneReliefList.PRStaffID + 1)

This assumes especially qryPhoneReliefList includes an index on PRStaff,
and there is a regular progression of PRStaff indices beginning with 1.
Where DayNum starts and ends isn't so important, but there might be
irregularities if DayNum repeats (e.g., at the beginning of a
week/month/year).

HTH
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSum function 4
Query a table's particular column and return the field name. 3
Crosstab query 1
How to get groups to be across, but details to be down within group? 1
Query for random values multiple records 9
Selecting Records with Matching Values in 4 different Fields 2
Compare Names in a column and create single list with adjacent vla 3
Like operator and wildcards, compare fields 2

Top