SQL Problem

[PW]

I have to (2) tables, Events and Errors.

Events
-------------
RecordNumber
EventDate
EventType
EventProblem
EventSolution


Errors
-------------
Event_RecordNum ber
ErrorType


When I am navigating the Events table, I want to check the Errors table to
see if an error exists for the current event.

Select RecordNumber, EventDate, EventType, EventProblem, Event Solution,
Event_RecordNum ber, ErrorType
from Events, Errors
where RecordNumber = Event_RecordNum ber

But this query returns zero rows.

Any help ?

Thanks,
PW

[John Winterbottom]

Re: SQL Problem

"PW" <pwa@NObigSPAMp ond.net.au> wrote in message
news:8q%Xc.1109 0$D7.9764@news-server.bigpond. net.au...[color=blue]
>
> I have to (2) tables, Events and Errors.
>
> Events
> -------------
> RecordNumber
> EventDate
> EventType
> EventProblem
> EventSolution
>
>
> Errors
> -------------
> Event_RecordNum ber
> ErrorType
>
>
> When I am navigating the Events table, I want to check the Errors table to
> see if an error exists for the current event.
>
> Select RecordNumber, EventDate, EventType, EventProblem, Event Solution,
> Event_RecordNum ber, ErrorType
> from Events, Errors
> where RecordNumber = Event_RecordNum ber
>
> But this query returns zero rows.
>
> Any help ?
>
> Thanks,
> PW
>
>[/color]
s

How many rows do you want to see if more than one error exists for the same
event? If you want all rows you need a left join:

select v.RecordNumber, v.EventDate, v.EventType,
v.EventProblem, v.EventSolution , e.Event_RecordN umber,
e.ErrorType
from Events as v
left outer join Errors as e on v.RecordNumber = e.Event_RecordN umber

If you want to see only unique events then you need to either aggregate the
error columns in some way, (such as with count(*)) or use a subquery to
accomplish the same thing. Here's an example with a subquery:

select v.RecordNumber, v.EventDate, v.EventType,
v.EventProblem, v.EventSolution ,
(
select count(*) from Errors as e
where e.Event_RecordN umber = v.RecordNumber
)
from Events as v

Just from an interface design point of view the latter option may be
better - you could show a list of individual events with an error count for
each one. Use conditional formatting to highlight rows where the error count
is > 0. Then allow users to drill down to see the individual errors for the
event.

[PC Datasheet]

Re: SQL Problem

Create a query that includes both tables. Join RecordNumber to
Event_RecordNum ber. Pull down Event_RecordNum ber
and ErrorType into the query grid. The query will give you the record
number and error type of all the events that had an error.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

"PW" <pwa@NObigSPAMp ond.net.au> wrote in message
news:8q%Xc.1109 0$D7.9764@news-server.bigpond. net.au...[color=blue]
>
> I have to (2) tables, Events and Errors.
>
> Events
> -------------
> RecordNumber
> EventDate
> EventType
> EventProblem
> EventSolution
>
>
> Errors
> -------------
> Event_RecordNum ber
> ErrorType
>
>
> When I am navigating the Events table, I want to check the Errors table to
> see if an error exists for the current event.
>
> Select RecordNumber, EventDate, EventType, EventProblem, Event Solution,
> Event_RecordNum ber, ErrorType
> from Events, Errors
> where RecordNumber = Event_RecordNum ber
>
> But this query returns zero rows.
>
> Any help ?
>
> Thanks,
> PW
>
>
>
>
>
>[/color]