No records...query help

[Tara]

I'm trying to create a query that will pull any records for expense
withdrawls from the beginning of the year through a start date set by the
user. These withdrawls will then be used as a beginning balance for a more
detailed look at withdrawls and earnings from the start date through whatever
end date the user sets. I don't have any problems with the creating the
query in theory, but I've run across an issue that I can't overcome. If the
user puts in 01/01/2009 as the start date, no records are pulled by the query
because no withdrawls were made that day. In this case, I need it to show a
record for each staff person with the date as 01/01/2009 and the request
amount as $0. This way, if the user wants to pull a detailed listing from
the beginning of the year rather than just for a set amount of time, they'll
have an accurate beginning balance.

Any help is welcome!

 

[KARL DEWEY]

Post the table and field names with datatype and sample data.

 

[Tara]

Thanks for getting back with me Karl. Here is the info you need...

tblBucketUsed

Fields:

BucketID - AutoNumber
EmployeeID - Number
DateRequested - Date/Time
AmtRequested - Currency

Example:

BucketID EmployeeID DateRequested AmtRequested
1 93 02/12/2009
$35
2 87 02/16/2009
$73
3 57 02/06/2009
$52

 

[KARL DEWEY]

Try these two queries --
EmployeeList --
SELECT EmployeeID
FROM tblBucketUsed
GROUP BY EmployeeID;

SELECT EmployeeID, IIF([DateRequested] Is Null, [Enter start date],
[DateRequested] AS Stat_Date, IIF([DateRequested] Is Null, 0, [AmtRequested]
FROM EmployeeList LEFT JOIN tblBucketUsed ON EmployeeList.EmployeeID =
tblBucketUsed.EmployeeID
WHERE [DateRequested] >= [Enter start date];

 

[Tara]

Thanks for the suggestion Karl, but it didn't quite give me the results I was
looking for. I did come up with an alternative though. It seems to work,
but I'm not sure if it's a good way to go or not. Would you mind giving me
your opinion?

I created a query that uses the employeeID from tblStaff and then uses
expressions to create 2 additional fields, DateRequested and AmtRequested.
DateRequested has a default value of 01/01/2009 and AmtRequested has a
default value of $0. I then unioned this with another query that pulls this
same data from tblBucketUsed. So this way, it "creates" records for
employees that don't have any existing records and gives me the option of
adding those records to any that may actaully exist.

Like I said, it gives me the results I need, but I'm not sure it's "proper"
or even stable to do it this way.

Thanks for any feedback.

Try these two queries --
EmployeeList --
SELECT EmployeeID
FROM tblBucketUsed
GROUP BY EmployeeID;

SELECT EmployeeID, IIF([DateRequested] Is Null, [Enter start date],
[DateRequested] AS Stat_Date, IIF([DateRequested] Is Null, 0, [AmtRequested]
FROM EmployeeList LEFT JOIN tblBucketUsed ON EmployeeList.EmployeeID =
tblBucketUsed.EmployeeID
WHERE [DateRequested] >= [Enter start date];

--
KARL DEWEY
Build a little - Test a little

Thanks for getting back with me Karl. Here is the info you need...

tblBucketUsed

Fields:

BucketID - AutoNumber
EmployeeID - Number
DateRequested - Date/Time
AmtRequested - Currency

Example:

BucketID EmployeeID DateRequested AmtRequested
1 93 02/12/2009
$35
2 87 02/16/2009
$73
3 57 02/06/2009
$52

 

[KARL DEWEY]

If it gives you the correct results then it is proper. You might check for
duplicates in output. Follow it with a totals query to avoid the dupes.
--
KARL DEWEY
Build a little - Test a little

Thanks for the suggestion Karl, but it didn't quite give me the results I was
looking for. I did come up with an alternative though. It seems to work,
but I'm not sure if it's a good way to go or not. Would you mind giving me
your opinion?

I created a query that uses the employeeID from tblStaff and then uses
expressions to create 2 additional fields, DateRequested and AmtRequested.
DateRequested has a default value of 01/01/2009 and AmtRequested has a
default value of $0. I then unioned this with another query that pulls this
same data from tblBucketUsed. So this way, it "creates" records for
employees that don't have any existing records and gives me the option of
adding those records to any that may actaully exist.

Like I said, it gives me the results I need, but I'm not sure it's "proper"
or even stable to do it this way.

Thanks for any feedback.

Try these two queries --
EmployeeList --
SELECT EmployeeID
FROM tblBucketUsed
GROUP BY EmployeeID;

SELECT EmployeeID, IIF([DateRequested] Is Null, [Enter start date],
[DateRequested] AS Stat_Date, IIF([DateRequested] Is Null, 0, [AmtRequested]
FROM EmployeeList LEFT JOIN tblBucketUsed ON EmployeeList.EmployeeID =
tblBucketUsed.EmployeeID
WHERE [DateRequested] >= [Enter start date];

--
KARL DEWEY
Build a little - Test a little

Thanks for getting back with me Karl. Here is the info you need...

tblBucketUsed

Fields:

BucketID - AutoNumber
EmployeeID - Number
DateRequested - Date/Time
AmtRequested - Currency

Example:

BucketID EmployeeID DateRequested AmtRequested
1 93 02/12/2009
$35
2 87 02/16/2009
$73
3 57 02/06/2009
$52



:

Post the table and field names with datatype and sample data.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to create a query that will pull any records for expense
withdrawls from the beginning of the year through a start date set by the
user. These withdrawls will then be used as a beginning balance for a more
detailed look at withdrawls and earnings from the start date through whatever
end date the user sets. I don't have any problems with the creating the
query in theory, but I've run across an issue that I can't overcome. If the
user puts in 01/01/2009 as the start date, no records are pulled by the query
because no withdrawls were made that day. In this case, I need it to show a
record for each staff person with the date as 01/01/2009 and the request
amount as $0. This way, if the user wants to pull a detailed listing from
the beginning of the year rather than just for a set amount of time, they'll
have an accurate beginning balance.

Any help is welcome!

 

[Tara]

Great! Thanks for the feedback.

If it gives you the correct results then it is proper. You might check for
duplicates in output. Follow it with a totals query to avoid the dupes.
--
KARL DEWEY
Build a little - Test a little

Thanks for the suggestion Karl, but it didn't quite give me the results I was
looking for. I did come up with an alternative though. It seems to work,
but I'm not sure if it's a good way to go or not. Would you mind giving me
your opinion?

I created a query that uses the employeeID from tblStaff and then uses
expressions to create 2 additional fields, DateRequested and AmtRequested.
DateRequested has a default value of 01/01/2009 and AmtRequested has a
default value of $0. I then unioned this with another query that pulls this
same data from tblBucketUsed. So this way, it "creates" records for
employees that don't have any existing records and gives me the option of
adding those records to any that may actaully exist.

Like I said, it gives me the results I need, but I'm not sure it's "proper"
or even stable to do it this way.

Thanks for any feedback.

Try these two queries --
EmployeeList --
SELECT EmployeeID
FROM tblBucketUsed
GROUP BY EmployeeID;

SELECT EmployeeID, IIF([DateRequested] Is Null, [Enter start date],
[DateRequested] AS Stat_Date, IIF([DateRequested] Is Null, 0, [AmtRequested]
FROM EmployeeList LEFT JOIN tblBucketUsed ON EmployeeList.EmployeeID =
tblBucketUsed.EmployeeID
WHERE [DateRequested] >= [Enter start date];

--
KARL DEWEY
Build a little - Test a little


:

Thanks for getting back with me Karl. Here is the info you need...

tblBucketUsed

Fields:

BucketID - AutoNumber
EmployeeID - Number
DateRequested - Date/Time
AmtRequested - Currency

Example:

BucketID EmployeeID DateRequested AmtRequested
1 93 02/12/2009
$35
2 87 02/16/2009
$73
3 57 02/06/2009
$52



:

Post the table and field names with datatype and sample data.
--
KARL DEWEY
Build a little - Test a little


:

I'm trying to create a query that will pull any records for expense
withdrawls from the beginning of the year through a start date set by the
user. These withdrawls will then be used as a beginning balance for a more
detailed look at withdrawls and earnings from the start date through whatever
end date the user sets. I don't have any problems with the creating the
query in theory, but I've run across an issue that I can't overcome. If the
user puts in 01/01/2009 as the start date, no records are pulled by the query
because no withdrawls were made that day. In this case, I need it to show a
record for each staff person with the date as 01/01/2009 and the request
amount as $0. This way, if the user wants to pull a detailed listing from
the beginning of the year rather than just for a set amount of time, they'll
have an accurate beginning balance.

Any help is welcome!