Re: How easy is this?


"Alastair MacFarlane" <anonymous@xxxxxxxxxxxxx> wrote in message
news:uoo9TS$1FHA.2072@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>

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.


.

Relevant Pages

  • Re: IF THEN in query..Maybe IIf
    ... assuming that there is a primary key for the existing records in the data ... table, which i'll call TableA, that matches a primary key in the imported ... now turn the query into an Update query. ... if the data file has the person's home ...
    (microsoft.public.access.queries)
  • Re: Subselect Query Problem
    ... I'm testing what you posted in the Query area ... Table A actually uses an autonumber primary key. ... >FROM tableA LEFT JOIN (SELECT project, status FROM tableB WHERE ...
    (microsoft.public.access.queries)
  • Re: How to trim a record when using DB_OPEN_TABLE
    ... and TableB contains some items that need to be updated in TableA. ... EmplID ... QtrDate (primary key) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problem with Dual subselect
    ... So I've constructed a UNION QUERY as follows. ... FROM TABLEA AS A ... B.STATUS_DATE FROM [TABLEB] As B ... A Lookup property on the foreign-key field ...
    (microsoft.public.access.queries)
  • Re: use a result as a FIELD in the design grid
    ... I have a key TableA that maps each station's flow (and ... Station: FlowField: TankLevel: PumpRun: ... OK, now from the previous query, I know that the FlowField ... So in a new query grid where TableA is linked to TableB ...
    (microsoft.public.access.queries)
Loading