D
Duncs
I hope that makes sense!
I have four tables as follows:
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.
[tbl_All For Team Stage 001] contains new transactional data that
needs to be appended to tblMainData to be worked.
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
I'm trying to write a query that will take data from [tbl_All For Team
Stage 001], a new table, and append it to tblMainData, whilst at the
same time, creating an entry in tblActionDetails, with a 'Status'
value of 1, to indicate that it is a new account.
[tbl_All For Team Stage 001] may contain some data that is already in
tblMainData, so that data should be excluded. I couldn't get an
append query working to do this, so I split it into two queries...one
to select the transactions to be processed from [tbl_All For Team
Stage 001], minus those already in tblMainData, and then a second one
that uses the results of this query to append the data to tblMainData.
So, my queries SQL looks like this:
Select Query:
INSERT INTO tblMainData ( PPMIP, MeterType, MPAN, NumberOfPayments,
ValueOfPayments, FirstPaymentDate, LastPaymentDate, OriginalMSN,
ModifiedMSN, MeterInstallDate, MeterRemovalDate, SupplyStartDate,
SupplyEndDate, Imported )
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?
So, my question is, I need the append of new data to tblMainData, to
also create a corresponding entry in tblActionDetails with the
'Status' field set to 1.
Your help is appreciated
Many TIA
Duncs
I have four tables as follows:
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.
[tbl_All For Team Stage 001] contains new transactional data that
needs to be appended to tblMainData to be worked.
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
I'm trying to write a query that will take data from [tbl_All For Team
Stage 001], a new table, and append it to tblMainData, whilst at the
same time, creating an entry in tblActionDetails, with a 'Status'
value of 1, to indicate that it is a new account.
[tbl_All For Team Stage 001] may contain some data that is already in
tblMainData, so that data should be excluded. I couldn't get an
append query working to do this, so I split it into two queries...one
to select the transactions to be processed from [tbl_All For Team
Stage 001], minus those already in tblMainData, and then a second one
that uses the results of this query to append the data to tblMainData.
So, my queries SQL looks like this:
Select Query:
INSERT INTO tblMainData ( PPMIP, MeterType, MPAN, NumberOfPayments,
ValueOfPayments, FirstPaymentDate, LastPaymentDate, OriginalMSN,
ModifiedMSN, MeterInstallDate, MeterRemovalDate, SupplyStartDate,
SupplyEndDate, Imported )
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?
So, my question is, I need the append of new data to tblMainData, to
also create a corresponding entry in tblActionDetails with the
'Status' field set to 1.
Your help is appreciated
Many TIA
Duncs