Entering a 0 value in a table as a result of a query

[Paul]

Hi
This has taken me ages to work out but i'm sure there is a simple
explanation and somebody here will be able to help. I'll talk you
through it as best i can:

1. User enters dates on an initial form
2. This then opens up another form asking for shift colour
(red/blue/green) and times shift start and end on dates entered in 1st
form.
3. This is where the problem starts...I have set up queries to
capture faults in products by area eg. qa, supplier, production!
Therefore i have set up an append query to enter date (from 1st form)
for defects between dates (second form) and with shift (second
form)and faults "qa" for example. This is all working fine until
there are no faults for qa on that date between those times.
Obviously no results will come up because there are no faults, this
however is not what i want. I have to run a report of these results
and this contains charts with date along the x axis and count of
defects as the values. Therefore i still need the date and shifts to
be entered in the table along with a 0 value for "qa" defects instead
of no entry at all.

Long and short of the problem is.....i need the query to come back
with a zero result displaying date and shift rather than returning no
entries.

All help is greatly appreciated. Thanks in advance.
Paul

[Darryl Kerkeslager]

You have been using an INNER JOIN. You will need to use another type of
JOIN in the query, to get results from all of table A, even when there is no
match from table B. Access query by design will help, or look up LEFT JOIN
and RIGHT JOIN.
Darryl Kerkeslager
"Paul" <pa*************@student.shu.ac.uk> wrote:

Long and short of the problem is.....i need the query to come back
with a zero result displaying date and shift rather than returning no
entries.

[paul shepherd]

Thanks for that, will come in handy in the future but not sure it solves
my problem now!

At the moment the date and shift are entered in the form and not stored
in the table until the append query is run (at which point all these
details and number of faults are entered) I therefore can't change the
type of join as there is only one table and all other data is retreived
straight from the form. Is the only way around this to have the data
from the form entered directly into a table and then run an update query
on it? I'd rather not do this if there is another option.

Thanks
Paul
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Darryl Kerkeslager]

First off, there is almost always more than one way to do anything in
Access. Finding the best way is the difficulty.

I re-read your first post, with your second, and maybe I don't have enough
information. Could you post the append query? Also, you said in your first
post, "I need the query to come back with a zero result displaying date and
shift". What query is this? Can you post it?

Without seeing more, the only thing I can offer is for you to look at the
nz() function, which accepts a value which may be NULL, and returns a string
or numeric value if NULL.
Darryl Kerkeslager

"paul shepherd" <pa*************@student.shu.ac.uk> wrote:

At the moment the date and shift are entered in the form and not stored
in the table until the append query is run (at which point all these
details and number of faults are entered) I therefore can't change the
type of join as there is only one table and all other data is retreived
straight from the form. Is the only way around this to have the data
from the form entered directly into a table and then run an update query
on it? I'd rather not do this if there is another option.

[Bob Quintal]

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in
news:a_********************@comcast.com:

First off, there is almost always more than one way to do
anything in Access. Finding the best way is the difficulty.

I re-read your first post, with your second, and maybe I don't
have enough information. Could you post the append query?
Also, you said in your first post, "I need the query to come
back with a zero result displaying date and shift". What
query is this? Can you post it?

Without seeing more, the only thing I can offer is for you to
look at the nz() function, which accepts a value which may be
NULL, and returns a string or numeric value if NULL.
Darryl Kerkeslager

"paul shepherd" <pa*************@student.shu.ac.uk> wrote:

At the moment the date and shift are entered in the form and
not stored in the table until the append query is run (at
which point all these details and number of faults are
entered) I therefore can't change the type of join as there
is only one table and all other data is retreived straight
from the form. Is the only way around this to have the data
from the form entered directly into a table and then run an
update query on it? I'd rather not do this if there is
another option.

The issue that Paul has is that he needs to create entries for
dates where no record exists in his table. The solution is to
first create records for the date and shift, bassed on code, then
do an update query of those records which match the created
records.

--
Bob Quintal

PA is y I've altered my email address.

[paul shepherd]

Yeah Bob's right, i need to create records for date and shift where
there are no entries. Probably would've been easier to understand if
i'd worded it like that from the start. I've succeeded anyway, after
reading your first reply about the type of joins, i now create a table
when date and shift are entered and then update it with the fault
information. Not an ideal way, but like you say, there are many
different ways to do things in access and this way works.

Thanks for your help, its been much appreciated.
Paul

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!