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

 

[Jeff Boyce]

I don't have particularly clear picture of the business situation you're
working in, so the following may not apply...

Whenever I see folks 'moving' records around from one table to another, I
think of ... spreadsheets!

While moving records from one 'sheet to another is often done in Excel, it
is equally not often done (nor needs to be done) in a well-normalized
relational database. This is where your familiarity with your situation
comes in...

Is the data being stored in your 4th table ([tbl_All ...] essentially the
same data elements as are stored in your [tblMainData] table? If so, what
(business) purpose does having a separate (?new/pending/???) table fulfill?

I ask because, depending on your business need, it may be possible to
eliminate that table (and the need for a response to this question).

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

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

 

[Duncs]

Jeff,

I'll try and answer as best I can...

Spreadsheets are not an option, due to the volume of data that is
transacted. tblMainData holds the current working set of
transactions, whilst [tbl_All ...] hold a list of new transactions.
These new transactions can include:

Totally new transactions
Transactions that already exist in tblMainData, but which have have
been updated since the last refresh
Transactions that already exist in tblMainData, but which have already
been worked

Due to the run-times of the refresh, this is why we get duplicates.
So, what I need to do is:

Archive worked transactions and delete them from tblMainData
Update transactions in tblMainData, that are marked as 'Pended', with
new data from [tbl_All ...], where there is a match
Remove all transactions from tblMainData that have not yet been worked
Add any transactions from [tbl_All ...], that do not fall into the
category of Already Worked or Pended but Updated

The refresh table is automaticall created by our system, and is
overwritten each time it is run, which is once a month. So, at the
start of the month, we get a list of transactions from the previous
month and add to our main transaction table new transactions, update
pended transactions and archive worked transactions. The data in
[tbl_All ...] may not contain data that is already in tblMainData, for
example any pended transactions will remain in tblMainData until they
are worked and marked as complete. Then, at the start of the next
month, these will be archived and a new set will exist.

I hope this makes sense and clarifies things a bit for you.

Rgds

Duncs

I don't have particularly clear picture of the business situation you're
working in, so the following may not apply...

Whenever I see folks 'moving' records around from one table to another, I
think of ... spreadsheets!

While moving records from one 'sheet to another is often done in Excel, it
is equally not often done (nor needs to be done) in a well-normalized
relational database.  This is where your familiarity with your situation
comes in...

Is the data being stored in your 4th table ([tbl_All ...] essentially the
same data elements as are stored in your [tblMainData] table?  If so, what
(business) purpose does having a separate (?new/pending/???) table fulfill?

I ask because, depending on your business need, it may be possible to
eliminate that table (and the need for a response to this question).

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

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- Hide quoted text -

- Show quoted text -

 

[Jeff Boyce]

If I'm following, you're describing a "how", as in how you are addressing a
business need (e.g., new transactions in one table, "archiving worked
transactions deleted" from another table, ...).

I was really asking more for the "what" -- if you were to describe what a
'perfect' application would allow you to get done, to an 87 year old
grandmother, you wouldn't use "tables" or "archive" or ... My "87-year-old"
language might be something like:

"I need to know which transactions are new,
keep track of transactions I'm working on,
and not have to look at transactions that are finished."

The reason I'm pushing this direction is because there are ways to
accomplish these kind of tasks that would make better use of the relational
features/functions that Access offers. The "how" you've described sounds
like the way it might be done if you were limited to using spreadsheets
(yes, I understand, the volume doesn't allow this, but your description
sounds like the application is an attempt to treat Access as a "spreadsheet
on steroids").

A clearer picture of the "what" would allow folks here the opportunity to
think of "how"s that solve your issue AND better use Access.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Duncs]

If I'm following, you're describing a "how", as in how you are addressinga
business need (e.g., new transactions in one table, "archiving worked
transactions deleted" from another table, ...).

I was really asking more for the "what" -- if you were to describe what a
'perfect' application would allow you to get done, to an 87 year old
grandmother, you wouldn't use "tables" or "archive" or ...  My "87-year-old"
language might be something like:

"I need to know which transactions are new,
keep track of transactions I'm working on,
and not have to look at transactions that are finished."

The reason I'm pushing this direction is because there are ways to
accomplish these kind of tasks that would make better use of the relational
features/functions that Access offers.  The "how" you've described sounds
like the way it might be done if you were limited to using spreadsheets
(yes, I understand, the volume doesn't allow this, but your description
sounds like the application is an attempt to treat Access as a "spreadsheet
on steroids").

A clearer picture of the "what" would allow folks here the opportunity to
think of "how"s that solve your issue AND better use Access.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff, and much searching of books / internet resouorces, I found a way
to achieve what I was looking to do.

Many thanks for your help though.

Duncs

 

[Jeff Boyce]

I'll encourage you to post back the solution you found... someday someone
else will run across the same/similar issue and your solution might be just
what they're looking for!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

If I'm following, you're describing a "how", as in how you are addressing
a
business need (e.g., new transactions in one table, "archiving worked
transactions deleted" from another table, ...).

I was really asking more for the "what" -- if you were to describe what a
'perfect' application would allow you to get done, to an 87 year old
grandmother, you wouldn't use "tables" or "archive" or ... My
"87-year-old"
language might be something like:

"I need to know which transactions are new,
keep track of transactions I'm working on,
and not have to look at transactions that are finished."

The reason I'm pushing this direction is because there are ways to
accomplish these kind of tasks that would make better use of the
relational
features/functions that Access offers. The "how" you've described sounds
like the way it might be done if you were limited to using spreadsheets
(yes, I understand, the volume doesn't allow this, but your description
sounds like the application is an attempt to treat Access as a
"spreadsheet
on steroids").

A clearer picture of the "what" would allow folks here the opportunity to
think of "how"s that solve your issue AND better use Access.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff, and much searching of books / internet resouorces, I found a way
to achieve what I was looking to do.

Many thanks for your help though.

Duncs