Append Query Issues...

[Duncs]

Hi all.

I have a database with three tables, all linked to each other:

tblMainData contains transactional data that has to be manually
processed.
tblActionDetails contains the status of each transaction that can /
has been processed.
tblStatus contains the four status codes, 1 - 4.

There is an additional tabel, [tbl_All For Team Stage 001], which contains new transactional data that needs to be appended to tblMainData to be worked.

After removing previously worked data, and updating pended accounts with new data, the remaining accounts in [tbl_All For Team Stage 001] need to be appended to tblMainData, unless the accounts already exist in tblMainData...as will be the case for Pended accounts. At the same time, I need to create a corresponding entry in tblActionDetails setting a field 'Status' to 1, indicating that the account is to be processed.

tblMainData has a 1:1 with tblActionDetails - Both tables have a
primary key that is the same in both tables.
tblActionDetails has a many:1 with tblStatus - The Status field in
tblStatus is the primary key

So, what I need is an update query to add the new data into tblMainData and insert an entry in tblActionDetails for each of the newly added added accounts. However, this is where I'm getting stuck.

I've had written an append query that would append the correct data to the table. However, as I need to exclude accounts that already exist in tblMainData & in [tbl_All For Team Stage 001], I couldn't get the query to append the data. So, I tried a different tack...I created a select query that retrieves the correct data, which works fine, and then created a separate append query that appends the data to tblMainData.

But, this is where I have my problem. I cannot get an entry created in tblActionDetails for each of the rows I have just added. Each entry in tblActionDetails should be blank, except for a Status field which should be set to '1', to signify the account can be worked.

Can anyone help?

I don't know if they will be of any use, but the SQLs for my two queries are as follows:


Select Query:
SELECT [tbl_All For Team Stage 001].PPMIP, "K" AS MeterType, [tbl_All
For Team Stage 001].[MPAN Core 1] AS MPAN, [tbl_All For Team Stage
001].CountOfAMT AS NumberOfPayments, [tbl_All For Team Stage
001].SumOfAMT AS ValueOfPayments, [tbl_All For Team Stage
001].MinOfDATEASDATE AS FirstPaymentDate, [tbl_All For Team Stage
001].MaxOfDATEASDATE AS LastPaymentDate, [tbl_All For Team Stage 001].[Meter Id (Serial Number)] AS OriginalMSN, "" AS ModifiedMSN, [tbl_All For Team Stage 001].[MinOfDate of Meter Installation 1] AS
MeterInstallDate, "" AS MeterRemovalDate, [tbl_All For Team Stage 001].
[Supplier 1 Start Date] AS SupplyStartDate, "" AS SupplyEndDate, Format
(Now(),"dd/mm/yyyy") AS Imported
FROM [tbl_All For Team Stage 001] LEFT JOIN tblMainData ON [tbl_All
For Team Stage 001].[MPAN Core 1] = tblMainData.MPAN
WHERE ((([tbl_All For Team Stage 001].PPMIP)="KACTA") AND
((tblMainData.MPAN) Is Null));

Append Query:


INSERT INTO tblMainData
SELECT qSel_NewAccountsToAppend.*
FROM qSel_NewAccountsToAppend;


I hope this all makes sense?

How can I create an associated entry in the additional table, for each of the rows I have just appended?

Your help is appreciated


Many TIA
Duncs

 

[jzwp22]

First, I don't follow your table structure. If you are only recording the current status of a transaction as implied by this statement "...tblMainData has a 1:1 with tblActionDetails", then I would just include the status field (and any other fields of interest) in tblMainData and do away with tblActionDetails. However, if you want to record the progress of a transaction as it goes through each status type then you would need tblActionDetails, but it would be the many side of a one-to-many relationship with tblMainData with a structure similar to this

tblMainData
-pkMainDataID primary key, autonumber
-other fields

tblActionDetails
-pkActionDetailID primary key, autonumber
-fkMainDataID foreign key to tblMainData
-dteTrans (transaction date)
-fkStatusID foreign key to tblStatus


Regarding your append query, you have to be able to identify those records that were just added. Do you have a field in tblMainData such as a DateAdded field?

If you change your table as I suggested above then you can just add a constant 1 to the status field when you append the records to tblMainData