How easy is this?

  • Thread starter Alastair MacFarlane
  • Start date
A

Alastair MacFarlane

Dear All,

I am trying to create a form based on a query that is updatable and keep
getting into problems. I have 2 tables:

TableA with 3 Fields:

ExpenseID (PK - Long Integer)
JobNo (Text - 5)
JobDate (Date)

and TableB with 2 Fields:

PostID (PK - Long Integer)
JobNo (Text - 5)

Ideally what I am trying to do is create an updatable query that will show
me only the job numbers in TableA that have not been 'posted' into (i.e.
appearing in) TableB.

My conclusion is to create a RIGHT JOIN between the 2 tables with TableA on
the Left side and TableB on the Right side ON TableA.JobNo = TableB.JobNo
WHERE TableB.JobNo Is Null.

This works only as far as the resulting recordset is not updatable. How can
I create a query that is updateable and excludes all the JobNo's in TableB?
As far as I am aware I can't use a sub-query because there will be more that
one value returned from TableB.

This has got me stumped.

Thanks again.

Alastair MacFarlane
 
S

Steve Schapel

Alastair,

It is not clear what the meaning of this information is. On the face of
what you have told us, it seems a bit peculiar that you would want to do
this. The only way to get the kind of behaviour you are asking for, is
if the JobNo field in TableA is uniquely indexed. Is that possible? Is
there only one record in TableA for each JobNo? If so, what is the
purpose of the ExpenseID field... it seems redundant, and the JobNo
could be the Primary Key field here. If not, then the query you are
asking for doesn't seem to make much sense to me. How about TableB?...
Does each JobNo only get posted once, i.e. is there only ever one record
in TableB for each JobNo? If so, haven't you got a Jobs table or some
such somewhere where you could have a field to record this information?
It seems strange that whether a JobNo has been posted into is being
determined based on the existence of a record in a separate table. I
obviously don't know much about your project, but it really looks to me
at the moment that it might be worth reviewing the table design.
 
A

Alastair MacFarlane

Steve,

You are right. I DO need to look at the design of my system. The user wants
to enter expenses against a JobNo and then post these at the end of the
month and then add some more expenses against that JobNo. They want the
totals of the JobNo expenses posted as well as current 'live' expenses for
the JobNo. In TableA the JobNo is not unique and neither need it be
necessarily in TableB. TableB justs lists the JobNos posted. To me TableB is
redundant and I will need to have a True/Fale field next to every entry in
TableA which states that this expense has been posted, and posted records
should not be shown. I can then total up the vales for the JobNo with the
flag set to true (ie posted) and false must be the live entries not to
displayed.

Sorry for the confusion, but you focused my thought away from the design
route I should be 'travelling' down. The system may well be moved to an mdb
VB route because the client may not want to use Access (ver long story!).

Thanks.

Alastair MacFarlane
 
S

Steve Schapel

Of course, part of the problem is the naming convention here... "TableA"
and "TableB" don't give a lot of clues about purpose. :)
 
A

Alastair MacFarlane

Chris,

Thanks for the lengthy reply and testing my scenario out for me. I should
have been clearer and I will now go into a dark room and think some thoughts
on the road ahead for the design of this system. I have also commented on
Steve's suggestions.

I appreciate your help this Sunday evening.

Alastair MacFarlane
 
C

Chris2

Alastair MacFarlane said:
Dear All,

I am trying to create a form based on a query that is updatable and keep
getting into problems. I have 2 tables:

TableA with 3 Fields:

ExpenseID (PK - Long Integer)
JobNo (Text - 5)
JobDate (Date)

and TableB with 2 Fields:

PostID (PK - Long Integer)
JobNo (Text - 5)

Ideally what I am trying to do is create an updatable query that will show
me only the job numbers in TableA that have not been 'posted' into (i.e.
appearing in) TableB.

My conclusion is to create a RIGHT JOIN between the 2 tables with TableA on
the Left side and TableB on the Right side ON TableA.JobNo = TableB.JobNo
WHERE TableB.JobNo Is Null.

This works only as far as the resulting recordset is not updatable. How can
I create a query that is updateable and excludes all the JobNo's in TableB?
As far as I am aware I can't use a sub-query because there will be more that
one value returned from TableB.

This has got me stumped.

Thanks again.

Alastair MacFarlane
Click to expand...

Alastair MacFarlane,

CREATE TABLE TableC_10232005_2
(JobNo TEXT(5)
,CONSTRAINT pk_TableC_10232005_2 PRIMARY KEY (JobNo)
)

CREATE TABLE TableA_10232005_2
(ExpenseID LONG
,JobNo TEXT(5)
,JobDate DATETIME
,CONSTRAINT pk_TableA_10232005_2 PRIMARY KEY (ExpenseID)
,CONSTRAINT fk_TableA_10232005_2_JobNo
FOREIGN KEY (JobNo) REFERENCES TableC_10232005_2 (Jobno)
)

CREATE TABLE TableB_10232005_2
(PostID LONG
,JobNo TEXT(5)
,CONSTRAINT pkTableB_10232005_2 PRIMARY KEY (PostID)
,CONSTRAINT fk_TableB_10232005_2_JobNo
FOREIGN KEY (JobNo) REFERENCES TableC_10232005_2 (Jobno)
)

Sample Data

TableC
1
2
3

TableA
1, 1, 11/01/2005
2, 1, 11/15/2005
3, 1, 11/30/2005
4, 2, 11/01/2005
5, 2, 11/15/2005
6, 2, 11/30/2005
7, 3, 12/01/2005
8, 3, 12/01/2005
9, 3, 12/01/2005

TableB
1, 2
2, 3


SELECT TA1.ExpenseID
,TA1.JobNo
,TA1.JobDate
FROM TableA_10232005_2 AS TA1
LEFT JOIN
TableB_10232005_2 AS TB1
ON TA1.JobNo = TB1.JobNo
WHERE TB1.JobNo IS NULL

This returns first three rows of TableA, and those are the JobNo
values not in TableB

However, this result is not updateable.


The following query does the same thing:

SELECT TA1.ExpenseID
,TA1.JobNo
,TA1.JobDate
FROM TableA_10232005_2 AS TA1
WHERE NOT EXISTS
(SELECT *
FROM TableB_10232005_2 AS TB01
WHERE TB01.JobNo = TA1.JobNo)

However, the above query was updateable.

I directly edited the resultset window, and added the following data:

10, 2, 01/01/2006, to TableA


Sincerely,

Chris O.
 
C

Chris2

Steve Schapel said:
Alastair,

It is not clear what the meaning of this information is. On the face of
what you have told us, it seems a bit peculiar that you would want to do
this. The only way to get the kind of behaviour you are asking for, is
if the JobNo field in TableA is uniquely indexed. Is that possible? Is
there only one record in TableA for each JobNo? If so, what is the
purpose of the ExpenseID field... it seems redundant, and the JobNo
could be the Primary Key field here. If not, then the query you are
asking for doesn't seem to make much sense to me. How about TableB?...
Does each JobNo only get posted once, i.e. is there only ever one record
in TableB for each JobNo? If so, haven't you got a Jobs table or some
such somewhere where you could have a field to record this information?
It seems strange that whether a JobNo has been posted into is being
determined based on the existence of a record in a separate table. I
obviously don't know much about your project, but it really looks to me
at the moment that it might be worth reviewing the table design.
Click to expand...

Steve Schapel,

I was somewhat wondering about the table organization and structure
myself.


Sincerely,

Chris O.
 
S

Steve Schapel

Alastair,

Thanks for the further explanation. After you emerge from your cave,
please let us know if you need any more help with this.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is the date between 2 dates? 3
How to check if a filed is in another table? 1
Union Query Problem 2
query with null values 2
Convert column Data Type 2
Using a combo box to change a foreign key relationship... 4
why isn't this query FAST!? I've tried EVERYTHING! (query performance issue) 5
Can a query do this? 3

Top