Query Development

[trashman]

Is there a way to perform a calculation with in a query field of a column in
a table and have it call out a value in another column in the same table? The
example of the table is actual and represents one column Manufacturing days
numerically correlating to the 2nd column representing the Calendar day.
Example: 2 columns

Mday Cday
1705 12/2/04
1706 12/3/04
1707 12/4/04
1708 12/5/04

preposed Formula: Mday+3= Cday
1705+3= 12/5/04

This formula will return a 0. I have tried many combinations with no luck.
Can some one help?

 

[Douglas J. Steele]

Yes, calculated fields in queries can refer to other fields in the same row.
Unfortunately, I don't see how you're deriving 12/5/04 from 1705+3, so I
can't give you a specific example.

 

[trashman]

Hey Douglas the 12/5/04 is the value of the field of the row in the adjcent
column see table example.
preposed Formula: Mday+3= Cday

 

[trashman]

Hey Douglas the 12/5/04 is the value of the field of the row in the adjcent
column see table example.
preposed Formula: Mday+3= Cday

 

[Tom Wickerath]

Although I generally prefer to avoid the use of domain aggregrate functions, the following is an
example of a parameter query that uses the DLookup function:

PARAMETERS [Enter MDay] Long, [Add how many days?] Long;
SELECT DLookUp("CDay","tblMDays","MDay = "
& [MDay]+[Add how many days?]) AS CDay
FROM tblMDays
WHERE MDay=[Enter MDay];

To use this SQL statement, create a new query. From design view, click on View > SQL View. Copy
the SQL statement shown above and paste it into the SQL View window. This SQL statement assumes
that the name of the table is tblMDays. You'll need to substitute the actual name of your table
in place of tblMDays.

Tom
____________________________________


Hey Douglas the 12/5/04 is the value of the field of the row in the adjcent column see table
example.
proposed Formula: Mday+3= Cday

____________________________________

Yes, calculated fields in queries can refer to other fields in the same row.
Unfortunately, I don't see how you're deriving 12/5/04 from 1705+3, so I
can't give you a specific example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

____________________________________


Is there a way to perform a calculation with in a query field of a column in
a table and have it call out a value in another column in the same table? The
example of the table is actual and represents one column Manufacturing days
numerically correlating to the 2nd column representing the Calendar day.
Example: 2 columns

Mday Cday
1705 12/2/04
1706 12/3/04
1707 12/4/04
1708 12/5/04

preposed Formula: Mday+3= Cday
1705+3= 12/5/04

This formula will return a 0. I have tried many combinations with no luck.
Can some one help?

 

[Dirk Goldgar]

Although I generally prefer to avoid the use of domain aggregrate
functions, the following is an example of a parameter query that uses
the DLookup function:

PARAMETERS [Enter MDay] Long, [Add how many days?] Long;
SELECT DLookUp("CDay","tblMDays","MDay = "
& [MDay]+[Add how many days?]) AS CDay
FROM tblMDays
WHERE MDay=[Enter MDay];

To use this SQL statement, create a new query. From design view,
click on View > SQL View. Copy the SQL statement shown above and
paste it into the SQL View window. This SQL statement assumes that
the name of the table is tblMDays. You'll need to substitute the
actual name of your table in place of tblMDays.

Or use a subquery:

SELECT
tblMDays.MDay,
tblMDays.CDay,
(
SELECT TOP 1 CDay FROM tblMDays AS A
WHERE A.MDay = tblMDays.MDay + 3
) AS NewCDay
FROM tblMDays;

 

[Tom Wickerath]

Thanks Dirk. I think I like your subquery better.

Tom

______________________________

Although I generally prefer to avoid the use of domain aggregrate
functions, the following is an example of a parameter query that uses
the DLookup function:

PARAMETERS [Enter MDay] Long, [Add how many days?] Long;
SELECT DLookUp("CDay","tblMDays","MDay = "
& [MDay]+[Add how many days?]) AS CDay
FROM tblMDays
WHERE MDay=[Enter MDay];

To use this SQL statement, create a new query. From design view,
click on View > SQL View. Copy the SQL statement shown above and
paste it into the SQL View window. This SQL statement assumes that
the name of the table is tblMDays. You'll need to substitute the
actual name of your table in place of tblMDays.

Or use a subquery:

SELECT
tblMDays.MDay,
tblMDays.CDay,
(
SELECT TOP 1 CDay FROM tblMDays AS A
WHERE A.MDay = tblMDays.MDay + 3
) AS NewCDay
FROM tblMDays;

 

[luty maiden]

Although I generally prefer to avoid the use of domain aggregrate functions, the following is an
example of a parameter query that uses the DLookup function:

PARAMETERS [Enter MDay] Long, [Add how many days?] Long;
SELECT DLookUp("CDay","tblMDays","MDay = "
& [MDay]+[Add how many days?]) AS CDay
FROM tblMDays
WHERE MDay=[Enter MDay];

To use this SQL statement, create a new query. From design view, click on View > SQL View. Copy
the SQL statement shown above and paste it into the SQL View window. This SQL statement assumes
that the name of the table is tblMDays. You'll need to substitute the actual name of your table
in place of tblMDays.

Tom
____________________________________


Hey Douglas the 12/5/04 is the value of the field of the row in the adjcent column see table
example.
proposed Formula: Mday+3= Cday

____________________________________

Yes, calculated fields in queries can refer to other fields in the same row.
Unfortunately, I don't see how you're deriving 12/5/04 from 1705+3, so I
can't give you a specific example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

____________________________________


Is there a way to perform a calculation with in a query field of a column in
a table and have it call out a value in another column in the same table? The
example of the table is actual and represents one column Manufacturing days
numerically correlating to the 2nd column representing the Calendar day.
Example: 2 columns

Mday Cday
1705 12/2/04
1706 12/3/04
1707 12/4/04
1708 12/5/04

preposed Formula: Mday+3= Cday
1705+3= 12/5/04

This formula will return a 0. I have tried many combinations with no luck.
Can some one help?