Null value is paramet query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a newbie so bear with me. I have a database that is set up to track all
job by projectid.
I created a multiple parameter query to show all the amount spent from
different tables for the one id.
Then I have my suppliers table that has null values in the date field (enter
date is my parameter). So when I try to put the supplier table in with the
other table. when I run my query nothing shows up if there is a null
value.Just because of that one table.

So what I want to be able to do is if my projectid is null for that field or
the date is null for that field, I still want to see the rest of the results
for the projectid.

I hope you can understand this I hope I am explaining it correctly. If you
need anything pasted over here I would be more than happy.

Thanks for the help
 
Post the SQL of the query as you've designed it so that we can see how your
tables and fields are related.
 
You're using a cartesian query that is trying to return a record for every
combination of records in each table. As such, if there is a null value in a
field, that record may not show up in the resulting query, which I believe
is what you're seeing (or not seeing, as the case may be).

Are you sure you need to use the query this way? What are you trying to
select/display? I would assume that there is a field that can be used to
join the tables (common to all the tables) and that the query could be
modified accordingly?
--

Ken Snell
<MS ACCESS MVP>


JamiLynn said:
SELECT [parameter 2nd subcontracts].SumOfEstimatedCost, [Parameter
ER].SumOfInvoiceAmount, [Parameter Expenses].SumOfAmountSpent, [Parameter
Labor 3].Total, [Parameter Material].SumOfAmountSpent, [Parameter
Supplier].SumOfAmountSpent
FROM [parameter 2nd subcontracts], [Parameter ER], [Parameter Expenses],
[Parameter Labor 3], [Parameter Material], [Parameter Supplier];

Ken Snell said:
Post the SQL of the query as you've designed it so that we can see how your
tables and fields are related.

--

Ken Snell
<MS ACCESS MVP>

track
all field
or If
you
Click to expand...
Click to expand...
 
I can't tell all the specifics from your query's SQL statement, but perhaps
you can get what you want this way:

SELECT [parameter 2nd subcontracts].SumOfEstimatedCost, [Parameter
ER].SumOfInvoiceAmount, [Parameter Expenses].SumOfAmountSpent, [Parameter
Labor 3].Total, [Parameter Material].SumOfAmountSpent, [Parameter
Supplier].SumOfAmountSpent
FROM (((([parameter 2nd subcontracts] INNER JOIN
[Parameter ER] ON [parameter 2nd subcontracts].ProjectID =
[Parameter ER].ProjectID) INNER JOIN [Parameter Expenses]
ON [parameter 2nd subcontracts].ProjectID = [Parameter Expenses].ProjectID)
INNER JOIN [Parameter Labor 3] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Labor 3].ProjectID)
INNER JOIN [Parameter Material] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Material].ProjectID)
INNER JOIN [Parameter Supplier] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Supplier].ProjectID;

This may or may not be the correct joins, but it should get you started on a
direction.
--

Ken Snell
<MS ACCESS MVP>


JamiLynn said:
I am trying to display the sum of money spent per job using projectid and
date as criteria. This is to track cost of job.
The projectID joins my tables
Hope I am making sense
Thanx
Jami

Ken Snell said:
You're using a cartesian query that is trying to return a record for every
combination of records in each table. As such, if there is a null value in a
field, that record may not show up in the resulting query, which I believe
is what you're seeing (or not seeing, as the case may be).

Are you sure you need to use the query this way? What are you trying to
select/display? I would assume that there is a field that can be used to
join the tables (common to all the tables) and that the query could be
modified accordingly?
--

Ken Snell
<MS ACCESS MVP>


JamiLynn said:
SELECT [parameter 2nd subcontracts].SumOfEstimatedCost, [Parameter
ER].SumOfInvoiceAmount, [Parameter Expenses].SumOfAmountSpent, [Parameter
Labor 3].Total, [Parameter Material].SumOfAmountSpent, [Parameter
Supplier].SumOfAmountSpent
FROM [parameter 2nd subcontracts], [Parameter ER], [Parameter Expenses],
[Parameter Labor 3], [Parameter Material], [Parameter Supplier];

:

Post the SQL of the query as you've designed it so that we can see
Click to expand...
how
your
tables and fields are related.

--

Ken Snell
<MS ACCESS MVP>

I am a newbie so bear with me. I have a database that is set up to track
all
job by projectid.
I created a multiple parameter query to show all the amount spent from
different tables for the one id.
Then I have my suppliers table that has null values in the date field
(enter
date is my parameter). So when I try to put the supplier table in
Click to expand...
with
the
other table. when I run my query nothing shows up if there is a null
value.Just because of that one table.

So what I want to be able to do is if my projectid is null for
Click to expand...
that
field
or
the date is null for that field, I still want to see the rest of the
results
for the projectid.

I hope you can understand this I hope I am explaining it
Click to expand...
correctly.
If
you
need anything pasted over here I would be more than happy.

Thanks for the help
Click to expand...
Click to expand...
Click to expand...
 
In general, the answer to your question is probably yes...you can sum using
the projectID as the grouping field. With respect to your question about the
Date Submitted field and how to handle that, do you really need that date
value at all for what you're doing? If not, you could leave it
out..otherwise, what you suggest for the criterion expression may be the
answer. I just don't know enough about your entire setup to give a
definitive answer -- you will need to try it and see if it works.

--

Ken Snell
<MS ACCESS MVP>

JamiLynn said:
Got it thank I will let you know how it goes. Got another question for you.
In my suppliers query I don't have the date submitted entered into my table
for alot of the records but i do have the project id. Is there anyway to have
it still sum up all the records for that project id using the parameter, like
put is null in the date part.
Jami

Ken Snell said:
I can't tell all the specifics from your query's SQL statement, but perhaps
you can get what you want this way:

SELECT [parameter 2nd subcontracts].SumOfEstimatedCost, [Parameter
ER].SumOfInvoiceAmount, [Parameter Expenses].SumOfAmountSpent, [Parameter
Labor 3].Total, [Parameter Material].SumOfAmountSpent, [Parameter
Supplier].SumOfAmountSpent
FROM (((([parameter 2nd subcontracts] INNER JOIN
[Parameter ER] ON [parameter 2nd subcontracts].ProjectID =
[Parameter ER].ProjectID) INNER JOIN [Parameter Expenses]
ON [parameter 2nd subcontracts].ProjectID = [Parameter Expenses].ProjectID)
INNER JOIN [Parameter Labor 3] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Labor 3].ProjectID)
INNER JOIN [Parameter Material] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Material].ProjectID)
INNER JOIN [Parameter Supplier] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Supplier].ProjectID;

This may or may not be the correct joins, but it should get you started on a
direction.
--

Ken Snell
<MS ACCESS MVP>


JamiLynn said:
I am trying to display the sum of money spent per job using projectid and
date as criteria. This is to track cost of job.
The projectID joins my tables
Hope I am making sense
Thanx
Jami

:

You're using a cartesian query that is trying to return a record for every
combination of records in each table. As such, if there is a null
Click to expand...
value
in a
field, that record may not show up in the resulting query, which I believe
is what you're seeing (or not seeing, as the case may be).

Are you sure you need to use the query this way? What are you trying to
select/display? I would assume that there is a field that can be used to
join the tables (common to all the tables) and that the query could be
modified accordingly?
--

Ken Snell
<MS ACCESS MVP>


SELECT [parameter 2nd subcontracts].SumOfEstimatedCost, [Parameter
ER].SumOfInvoiceAmount, [Parameter Expenses].SumOfAmountSpent, [Parameter
Labor 3].Total, [Parameter Material].SumOfAmountSpent, [Parameter
Supplier].SumOfAmountSpent
FROM [parameter 2nd subcontracts], [Parameter ER], [Parameter Expenses],
[Parameter Labor 3], [Parameter Material], [Parameter Supplier];

:

Post the SQL of the query as you've designed it so that we can
Click to expand...
see
how
your
tables and fields are related.

--

Ken Snell
<MS ACCESS MVP>

I am a newbie so bear with me. I have a database that is set up to
track
all
job by projectid.
I created a multiple parameter query to show all the amount
Click to expand...
spent
from
different tables for the one id.
Then I have my suppliers table that has null values in the
Click to expand...
date
field
(enter
date is my parameter). So when I try to put the supplier table
Click to expand...
in
with
the
other table. when I run my query nothing shows up if there is
Click to expand...
a
null
value.Just because of that one table.

So what I want to be able to do is if my projectid is null for that
field
or
the date is null for that field, I still want to see the rest
Click to expand...
of
the
results
for the projectid.

I hope you can understand this I hope I am explaining it correctly.
If
you
need anything pasted over here I would be more than happy.

Thanks for the help
Click to expand...
Click to expand...
Click to expand...
 
Thank you,
Let me give you a little info about what I am doing. I work for a
construction company.
Each location has subcontractors, materials, suppliers, rentals, labor etc.
I am tracking all the money spent on these locations.
I enter everything that comes and give the project id that it is related to.
Sometimes we have a second phase of a job and still using the same projectid
the only way to track the 2nd phase is to do it by start date and end date.
Everything else works fine with what I have done so far.
I did take the date parameter and put in a fictious date field in the
suppliers to make it come up and it did . I just thought there was another
way.
Jami

Ken Snell said:
In general, the answer to your question is probably yes...you can sum using
the projectID as the grouping field. With respect to your question about the
Date Submitted field and how to handle that, do you really need that date
value at all for what you're doing? If not, you could leave it
out..otherwise, what you suggest for the criterion expression may be the
answer. I just don't know enough about your entire setup to give a
definitive answer -- you will need to try it and see if it works.

--

Ken Snell
<MS ACCESS MVP>

JamiLynn said:
Got it thank I will let you know how it goes. Got another question for you.
In my suppliers query I don't have the date submitted entered into my table
for alot of the records but i do have the project id. Is there anyway to have
it still sum up all the records for that project id using the parameter, like
put is null in the date part.
Jami

Ken Snell said:
I can't tell all the specifics from your query's SQL statement, but perhaps
you can get what you want this way:

SELECT [parameter 2nd subcontracts].SumOfEstimatedCost, [Parameter
ER].SumOfInvoiceAmount, [Parameter Expenses].SumOfAmountSpent, [Parameter
Labor 3].Total, [Parameter Material].SumOfAmountSpent, [Parameter
Supplier].SumOfAmountSpent
FROM (((([parameter 2nd subcontracts] INNER JOIN
[Parameter ER] ON [parameter 2nd subcontracts].ProjectID =
[Parameter ER].ProjectID) INNER JOIN [Parameter Expenses]
ON [parameter 2nd subcontracts].ProjectID = [Parameter Expenses].ProjectID)
INNER JOIN [Parameter Labor 3] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Labor 3].ProjectID)
INNER JOIN [Parameter Material] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Material].ProjectID)
INNER JOIN [Parameter Supplier] ON
[parameter 2nd subcontracts].ProjectID = [Parameter Supplier].ProjectID;

This may or may not be the correct joins, but it should get you started on a
direction.
--

Ken Snell
<MS ACCESS MVP>


I am trying to display the sum of money spent per job using projectid and
date as criteria. This is to track cost of job.
The projectID joins my tables
Hope I am making sense
Thanx
Jami

:

You're using a cartesian query that is trying to return a record for
every
combination of records in each table. As such, if there is a null value
in a
field, that record may not show up in the resulting query, which I
believe
is what you're seeing (or not seeing, as the case may be).

Are you sure you need to use the query this way? What are you trying to
select/display? I would assume that there is a field that can be used to
join the tables (common to all the tables) and that the query could be
modified accordingly?
--

Ken Snell
<MS ACCESS MVP>


SELECT [parameter 2nd subcontracts].SumOfEstimatedCost, [Parameter
ER].SumOfInvoiceAmount, [Parameter Expenses].SumOfAmountSpent,
[Parameter
Labor 3].Total, [Parameter Material].SumOfAmountSpent, [Parameter
Supplier].SumOfAmountSpent
FROM [parameter 2nd subcontracts], [Parameter ER], [Parameter
Expenses],
[Parameter Labor 3], [Parameter Material], [Parameter Supplier];

:

Post the SQL of the query as you've designed it so that we can see
how
your
tables and fields are related.

--

Ken Snell
<MS ACCESS MVP>

I am a newbie so bear with me. I have a database that is set up to
track
all
job by projectid.
I created a multiple parameter query to show all the amount spent
from
different tables for the one id.
Then I have my suppliers table that has null values in the date
field
(enter
date is my parameter). So when I try to put the supplier table in
with
the
other table. when I run my query nothing shows up if there is a
null
value.Just because of that one table.

So what I want to be able to do is if my projectid is null for
that
field
or
the date is null for that field, I still want to see the rest of
the
results
for the projectid.

I hope you can understand this I hope I am explaining it
correctly.
If
you
need anything pasted over here I would be more than happy.

Thanks for the help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top