Macro required for moving data between tables in specified format

[Stuart]

Hi -

I wonder if someone can help with this. It seems really complicated to automate this. I've been doing it manually for a while now and though maybe it is possible.

My data is in table "tblDataEntry" as follows (just a sample)

Acc Acc1 Acc2 Amount Amount 1 Amount 2 Desc Ready
111 222 333 20.00 2.00 22.00 Invoice1 Yes/No

I have another table called "tblSystem"
Acc Amount Desc
111 20.00 Invoice1
222 2.00 Invoice1
333 22.00 Invoice1

You can see that the 1 record has simply been copied to another table and then presented as three records where as before it was one record.

Is there some kind of macro button that could first look at the field in tblDataEntry at the field "Ready" and then if this is checked "Yes" then the macro will send the data to the tblSystem in the specified format.

I hope this makes sense.

Best regards

Stuart

 

[Access Developer]

The repeating fields in your table make me strongly suspect that your tables
are not properly 'normalized'. Unnormalized data can (as in your case) lead
to difficulties in accomplishing what you really want to do.

I'd guess that redesign of your tables will make doing what you want a great
deal simpler. Perhaps if you can clearly and simply explain what you're
trying to do, someone can assist with restructuring the data.

 

[John W. Vinson]

Hi -

I wonder if someone can help with this. It seems really complicated to automate this. I've been doing it manually for a while now and though maybe it is possible.

My data is in table "tblDataEntry" as follows (just a sample)

Acc Acc1 Acc2 Amount Amount 1 Amount 2 Desc Ready
111 222 333 20.00 2.00 22.00 Invoice1 Yes/No

I have another table called "tblSystem"
Acc Amount Desc
111 20.00 Invoice1
222 2.00 Invoice1
333 22.00 Invoice1

You can see that the 1 record has simply been copied to another table and then presented as three records where as before it was one record.

Is there some kind of macro button that could first look at the field in tblDataEntry at the field "Ready" and then if this is checked "Yes" then the macro will send the data to the tblSystem in the specified format.

I hope this makes sense.

Best regards

Stuart

I agree with Larry that your tblDataEntry structure is just plain wrong. I can
*just* believe that you're intentionally using a non-normalized table for data
entry convenience, so I'll suggest a "Normalizing Union Query".

You can create a Query such as

INSERT INTO tblSystem
SELECT Acc, Amount, Desc FROM
(SELECT Acc, Amount, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount IS NOT NULL
UNION ALL
SELECT Acc1, Amount1, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc1] IS NOT NULL AND Amount1 IS NOT NULL
UNION ALL
SELECT Acc2, Amount, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc2] IS NOT NULL AND Amount2 IS NOT NULL)

and run it from a macro in the command button's Click event.



--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Stuart]

Thanks very much for your feedback.

The reason why the tblDataEntry has been created as is purely to make
it easier for us to edit the data. You see there are many more
columns than those I used in my example and all need some kind of
update to them before being converted into the format that is suitable
for tblsystem.

I thought this was the best to achieve it making it easier for us to
do the "correction" part of the data entry before it gets to its final
destination in tblsystem.

I will give this a test and let you know how I get on.

Thanks very much for your help on this.

Best regards

Stuart

We don't use tblsystem at all in Access. Our software does various
things with this tables and its horrible to look at even with a data
extract.

Hi -

I wonder if someone can help with this.  It seems really complicated to automate this.  I've been doing it manually for a while now and though maybe it is possible.

My data is in table "tblDataEntry" as follows (just a sample)

Acc     Acc1    Acc2   Amount     Amount 1     Amount 2    Desc         Ready
111     222     333    20.00      2.00        22.00       Invoice1     Yes/No

I have another table called "tblSystem"
Acc     Amount     Desc
111     20.00      Invoice1
222     2.00       Invoice1
333     22.00      Invoice1

You can see that the 1 record has simply been copied to another table and then presented as three records where as before it was one record.

Is there some kind of macro button that could first look at the field intblDataEntry at the field "Ready" and then if this is checked "Yes" then the macro will send the data to the tblSystem in the specified format.

I hope this makes sense.

Best regards

Stuart

I agree with Larry that your tblDataEntry structure is just plain wrong. I can
*just* believe that you're intentionally using a non-normalized table fordata
entry convenience, so I'll suggest a "Normalizing Union Query".

You can create a Query such as

INSERT INTO tblSystem
SELECT Acc, Amount, Desc FROM
(SELECT Acc, Amount, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount IS NOT NULL
UNION ALL
SELECT Acc1, Amount1, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc1] IS NOT NULL AND Amount1 IS NOT NULL
UNION ALL
SELECT Acc2, Amount, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc2] IS NOT NULL AND Amount2 IS NOT NULL)

and run it from a macro in the command button's Click event.

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

 

[Stuart]

Dear John

Thanks for providing the code. I will read more Append Queries.

Is it possible to keep all the records in order? As you know, 1
record in tblDataEntry relates to 3 records in tblSystem.

I have a field called JRNAL_LINE and in here they should be given a
number 1,2 or 3. Can this be done with append query?

Also, I have another table called "tblRefs" which is the accounting
system keeping track of sequence numbers. I cannot move the
sequential number to Access as there is another front end piece of
software that does all that. This is like an add-on to that. For
each of the records created during the Append Query - a reference
number should be added to the "JRNL_NO" field from "tblRefs".

Here is the sequence of events that I think should happen that might
be possible to do in this append query.

1. Macro will first lookup reference from field HIGH_JRNAL where
company is same as "COCODE" in tblRefs. This number should be added
to the record in "JRNL_NO" within tblDataEntry.
2. Then the system will amend the number in tblRefs with the same
criteria "COCODE" with the next number. This ensure the next number
used by the accounting software is correct on the other front end
product.
3. Then the records will be added to the tblsystem but will have a
1,2 and 3 added to the JRNL_LINE field.

So then the records can be related by way of the "JRNL_NO" and sorted
by this number. The "JRNL_NO" will always be unqiue.

Thanks for your help in advance.

Best regards

Stuart

Thanks very much for your feedback.

The reason why the tblDataEntry has been created as is purely to make
it easier for us to edit the data.  You see there are many more
columns than those I used in my example and all need some kind of
update to them before being converted into the format that is suitable
for tblsystem.

I thought this was the best to achieve it making it easier for us to
do the "correction" part of the data entry before it gets to its final
destination in tblsystem.

I will give this a test and let you know how I get on.

Thanks very much for your help on this.

Best regards

Stuart

We don't use tblsystem at all in Access.  Our software does various
things with this tables and its horrible to look at even with a data
extract.

I agree with Larry that your tblDataEntry structure is just plain wrong.. I can
*just* believe that you're intentionally using a non-normalized table for data
entry convenience, so I'll suggest a "Normalizing Union Query".

You can create a Query such as

INSERT INTO tblSystem
SELECT Acc, Amount, Desc FROM
(SELECT Acc, Amount, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount IS NOT NULL
UNION ALL
SELECT Acc1, Amount1, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc1] IS NOT NULL AND Amount1 IS NOT NULL
UNION ALL
SELECT Acc2, Amount, Desc
FROM tblDataEntry
WHERE [Ready] = True AND [Acc2] IS NOT NULL AND Amount2 IS NOT NULL)

and run it from a macro in the command button's Click event.

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

 

[Stuart]

John, Larry, is this something you can help me with?

Its the last part of a database entry system that has taken me months
to finish. (Still not perfect though!)

I hope you can help.

Best regards

Stuart

 

[John W. Vinson]

Dear John

Thanks for providing the code. I will read more Append Queries.

Is it possible to keep all the records in order? As you know, 1
record in tblDataEntry relates to 3 records in tblSystem.

I have a field called JRNAL_LINE and in here they should be given a
number 1,2 or 3. Can this be done with append query?

Yes; you can include a literal constant in the queries in the UNION:

INSERT INTO tblSystem
SELECT Acc, Amount, [Desc], JRNAL_LINE FROM
(SELECT Acc, Amount, [Desc], 1 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount IS NOT NULL
UNION ALL
SELECT Acc1, Amount1, [Desc], 2 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc1] IS NOT NULL AND Amount1 IS NOT NULL
UNION ALL
SELECT Acc2, Amount, [Desc], 3 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc2] IS NOT NULL AND Amount2 IS NOT NULL)

Note that I'm putting brackets around the reserved word DESC (a keyword for a
query's Order By clause, meaning descending); it would be better not to use
such reserved words as fieldnames.

Also, I have another table called "tblRefs" which is the accounting
system keeping track of sequence numbers. I cannot move the
sequential number to Access as there is another front end piece of
software that does all that. This is like an add-on to that. For
each of the records created during the Append Query - a reference
number should be added to the "JRNL_NO" field from "tblRefs".

You could run a separate APPEND query appending into tblRefs. It would be
safest to run the two queries in sequence from VBA code, using a "Transaction"
to ensure that they either both run, or neither runs. Macros will not serve
your turn here, I don't think - you'll need VBA and a bit of error checking.

Here is the sequence of events that I think should happen that might
be possible to do in this append query.

1. Macro will first lookup reference from field HIGH_JRNAL where
company is same as "COCODE" in tblRefs. This number should be added
to the record in "JRNL_NO" within tblDataEntry.

You could join HIGH_JRNAL to tblDataEntry in the UNION query. It's (almost
certainly) neither necessary nor appropriate to update the "scratch"
tblDataEntry, since you can just pick up the value as needed.

2. Then the system will amend the number in tblRefs with the same
criteria "COCODE" with the next number. This ensure the next number
used by the accounting software is correct on the other front end
product.

Now that I simply don't understand at all.

3. Then the records will be added to the tblsystem but will have a
1,2 and 3 added to the JRNL_LINE field.

So then the records can be related by way of the "JRNL_NO" and sorted
by this number. The "JRNL_NO" will always be unqiue.

A Table HAS NO ORDER. It's best viewed as an unordered "bag" of records. You
can insert the JRNL_NO - once we figure out what it should contain, it can't
be simultaneously 1, 2, 3 as you described and also unique! - into the table,
and then use it in a Query sorting the data in the table; but the "order of
records in the table" is a meaningless and useless concept in Access.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Stuart]

John

When I amend the SQL statement you provided to match my actual fields
in my table I get a message that states the following:

Circular reference caused

INSERT INTO tblSystem
SELECT [ACCNT_CODE] AS ACCNT_CODE, [NAMOUNT] AS NAMOUNT, [DESCRIPTN]
AS DESCRIPTN, [JRNAL_LINE] AS JRNAL_LINE
FROM (SELECT [ACCNT_CODE], [NAMOUNT], [DESCRIPTN], 1 AS [JRNAL_LINE]
FROM tblDataEntry
WHERE [READY] = True
UNION ALL
SELECT [SUPP_CODE], [GAMOUNT], [DESCRIPTN], 2 AS JRNAL_LINE
FROM tblDataEntry
WHERE [READY] = True
UNION ALL
SELECT 271 AS [ACCNT_CODE], [VAT], [DESCRIPTN], 3 AS JRNAL_LINE
FROM tblDataEntry
WHERE [READY] = True) AS [%$##@_Alias];

You'll see it adds something to the end as well. The Alias part "AS [%
$##@_Alias];".

ACCNT_CODE is actually a text field. Also, ACC3 should actually
always be account number 271. We don't store 271 anywhere in
tblDataEntry. It should be automatically done on the append query
like you have done with JRNAL_LINE 1,2 and 3 etc.

I will throw in another technicality - the table name tblSystem could
be tblSystemABC or something like that with the last three letters
being unique. Is it possible to amend your query above to say INSERT
INTO tblsystem then lookup the "CO_CODE" field (as discussed further
below). I guess its possible if they are joined together somehow.

Now as the seperate issue of the tblRefs which I think I stated
incorrectly.

There is two fields which I have not given to you in my sample data.
These are "CO_CODE" and "JRNAL_NO". "CO_CODE" is keyed in by the user
when entering the data. "JRNAL_NO" is automatically generated from
another piece of software which other users use or I would get Access
to do the autonumbering. So what I need is before the Append Query is
run, we first lookup tblRefs WHERE CO_CODE = CO_CODE in tblDataEntry
and then lookup the number stored in "HIGH_JRNAL" which has a number
stored in it. Our software will only allow one use of that number
which is stored in the tblRefs table so I need to ensure the same data
integrity here as well perhaps by adding a "Refresh Linked" table
sequence before pulling out that number.

So this number is then placed in the JRNL_NO field on the record
stored in tblDataEntry and then the tblRefs table is then updated to
what the next number should be so that a) when Access does this again,
the next available number is selected and b) when our users who don't
use Access book a transaction, it gives them the next available number
on their entry.

Thanks very much for your help. It is greatly appreciated.

Dear John

Thanks for providing the code.  I will read more Append Queries.

Is it possible to keep all the records in order?  As you know, 1
record in tblDataEntry relates to 3 records in tblSystem.

I have a field called JRNAL_LINE and in here they should be given a
number 1,2 or 3.  Can this be done with append query?

Yes; you can include a literal constant in the queries in the UNION:

INSERT INTO tblSystem
SELECT Acc, Amount, [Desc], JRNAL_LINE  FROM
(SELECT Acc, Amount, [Desc], 1 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount IS NOT NULL
UNION ALL
SELECT Acc1, Amount1, [Desc], 2 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc1] IS NOT NULL AND Amount1 IS NOT NULL
UNION ALL
SELECT Acc2, Amount, [Desc], 3  AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc2] IS NOT NULL AND Amount2 IS NOT NULL)

Note that I'm putting brackets around the reserved word DESC (a keyword for a
query's Order By clause, meaning descending); it would be better not to use
such reserved words as fieldnames.

Also, I have another table called "tblRefs" which is the accounting
system keeping track of sequence numbers.  I cannot move the
sequential number to Access as there is another front end piece of
software that does all that.  This is like an add-on to that.  For
each of the records created during the Append Query - a reference
number should be added to the "JRNL_NO" field from "tblRefs".

You could run a separate APPEND query appending into tblRefs. It would be
safest to run the two queries in sequence from VBA code, using a "Transaction"
to ensure that they either both run, or neither runs. Macros will not serve
your turn here, I don't think - you'll need VBA and a bit of error checking.

Here is the sequence of events that I think should happen that might
be possible to do in this append query.

1.  Macro will first lookup reference from field HIGH_JRNAL where
company is same as "COCODE" in tblRefs.  This number should be added
to the record in "JRNL_NO" within tblDataEntry.

You could join HIGH_JRNAL to tblDataEntry in the UNION query. It's (almost
certainly) neither necessary nor appropriate to update the "scratch"
tblDataEntry, since you can just pick up the value as needed.

2.  Then the system will amend the number in tblRefs with the same
criteria "COCODE" with the next number.  This ensure the next number
used by the accounting software is correct on the other front end
product.

Now that I simply don't understand at all.

3.  Then the records will be added to the tblsystem but will have a
1,2 and 3 added to the JRNL_LINE field.

So then the records can be related by way of the "JRNL_NO" and sorted
by this number.  The "JRNL_NO" will always be unqiue.

A Table HAS NO ORDER. It's best viewed as an unordered "bag" of records. You
can insert the JRNL_NO - once we figure out what it should contain, it can't
be simultaneously 1, 2, 3 as you described and also unique! - into the table,
and then use it in a Query sorting the data in the table; but the "order of
records in the table" is a meaningless and useless concept in Access.

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

 

[John W. Vinson]

John

When I amend the SQL statement you provided to match my actual fields
in my table I get a message that states the following:

Circular reference caused

INSERT INTO tblSystem
SELECT [ACCNT_CODE] AS ACCNT_CODE, [NAMOUNT] AS NAMOUNT, [DESCRIPTN]
AS DESCRIPTN, [JRNAL_LINE] AS JRNAL_LINE
FROM (SELECT [ACCNT_CODE], [NAMOUNT], [DESCRIPTN], 1 AS [JRNAL_LINE]
FROM tblDataEntry
WHERE [READY] = True
UNION ALL
SELECT [SUPP_CODE], [GAMOUNT], [DESCRIPTN], 2 AS JRNAL_LINE
FROM tblDataEntry
WHERE [READY] = True
UNION ALL
SELECT 271 AS [ACCNT_CODE], [VAT], [DESCRIPTN], 3 AS JRNAL_LINE
FROM tblDataEntry
WHERE [READY] = True) AS [%$##@_Alias];

You'll see it adds something to the end as well. The Alias part "AS [%
$##@_Alias];".

You're trying to alias the field [NAMOUNT] under the "new" name [NAMOUNT], and
the same for several other fields. That's what's circular - you can't reuse
the same name. If you're not renaming the field just select it without the AS
clause.

ACCNT_CODE is actually a text field. Also, ACC3 should actually
always be account number 271. We don't store 271 anywhere in
tblDataEntry. It should be automatically done on the append query
like you have done with JRNAL_LINE 1,2 and 3 etc.

If ACCNT_CODE is Text and you need to insert a literal value, enclose it in
quotemarks. I'm perplexed though - you say "we don't store 271" but then say
"it should be automatically done on the append query" - what do you want
inserted into ACCNT_CODE?

I will throw in another technicality - the table name tblSystem could
be tblSystemABC or something like that with the last three letters
being unique. Is it possible to amend your query above to say INSERT
INTO tblsystem then lookup the "CO_CODE" field (as discussed further
below). I guess its possible if they are joined together somehow.

You'll have to construct the entire SQL string in VBA code then. You can't
change the tablename in a parameter.

Now as the seperate issue of the tblRefs which I think I stated
incorrectly.

There is two fields which I have not given to you in my sample data.

These are "CO_CODE" and "JRNAL_NO". "CO_CODE" is keyed in by the user
when entering the data. "JRNAL_NO" is automatically generated from
another piece of software which other users use or I would get Access
to do the autonumbering. So what I need is before the Append Query is
run, we first lookup tblRefs WHERE CO_CODE = CO_CODE in tblDataEntry
and then lookup the number stored in "HIGH_JRNAL" which has a number
stored in it. Our software will only allow one use of that number
which is stored in the tblRefs table so I need to ensure the same data
integrity here as well perhaps by adding a "Refresh Linked" table
sequence before pulling out that number.

So this number is then placed in the JRNL_NO field on the record
stored in tblDataEntry and then the tblRefs table is then updated to
what the next number should be so that a) when Access does this again,
the next available number is selected and b) when our users who don't
use Access book a transaction, it gives them the next available number
on their entry.

I'm going to have to driink a cup or two of coffee and read this over again.
Right now I'm completely confused about where you're getting these values and
where you want to store them. I'll be back though...
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Stuart]

John

Thanks for getting back to me. I've had enough coffee for today.

Do you think I would be better posting on the VBA access forum? I say
this because of the part about the table name being "tblSystemABC"
with the string "ABC" coming from tblDataEntry in field "CO_CODE".

The tblsystem fields and tblDataEntry fields are almost the same with
the exception of "GAMOUNT", "NAMOUNT" and "VAT" which should always be
inserted into tblSystem under the one field "AMOUNT". The two tables
share the same field "CO_CODE" and "ACCNT_CODE" but I could always
change these in the tblDataEntry table if needed.

I think I caused the confusion regarding ACCNT_CODE 271. I didn't use
this in my sample data but the query would insert the data for two
"ACCNT_CODE" which is stored in tblDataEntry and the 3rd insertion
would always be the "VAT" amount (and other fields) and "ACCNT_CODE"
271 automatically as it will never change.

The tblRefs part would be the last and we're almost there with the
solution. I appreciate the education your giving me here.

tblRefs has "CO_CODE" and "HIGH_JRNAL" as field as well as other
fields.

I am saying that before we run the amend query to put the data from
tblDataEntry to tblSystem we should first fill in the field "JRNAL_NO"
by refreshing the linked table "tblRefs" and then extracting the value
from "HIGH_JRNAL" where the "CO_CODE" is the same as the record in
tblDataEntry and placing it in the record under "JRNAL_NO" and then
automatically updating "tblRefs" with the next number in the sequence
so add 1 to the number extracted before then it should move right on
to the insertion of the data records into the tblSystemABC (or
whatever CO_CODE is per tblDataEntry record). This is the only way I
can think to do it in order to reduce the time between the system
extracting the reference number because I am conscious that someone
else in the office can book a journal while this sequence of events is
being undertaken so the linked table tblRefs should be refreshed as
often as needed. I do actually have a code for refreshing linked
tables on open so I can use that to add to the control button to call
this sequence of events.

Can you help when you've a sufficient amount caffeine in you to tackle
this?

Best regards and thanks again

Stuart

John

When I amend the SQL statement you provided to match my actual fields
in my table I get a message that states the following:

Circular reference caused

INSERT INTO tblSystem
SELECT [ACCNT_CODE] AS ACCNT_CODE, [NAMOUNT] AS NAMOUNT, [DESCRIPTN]
AS DESCRIPTN, [JRNAL_LINE] AS JRNAL_LINE
FROM (SELECT [ACCNT_CODE], [NAMOUNT], [DESCRIPTN], 1 AS [JRNAL_LINE]
FROM tblDataEntry
WHERE [READY] = True
UNION ALL
SELECT [SUPP_CODE], [GAMOUNT], [DESCRIPTN], 2 AS JRNAL_LINE
FROM tblDataEntry
WHERE [READY] = True
UNION ALL
SELECT 271 AS [ACCNT_CODE], [VAT], [DESCRIPTN], 3  AS JRNAL_LINE
FROM tblDataEntry
WHERE [READY] = True)  AS [%$##@_Alias];

You'll see it adds something to the end as well. The Alias part "AS [%
$##@_Alias];".

ACCNT_CODE is actually a text field.  Also, ACC3 should actually
always be account number 271.  We don't store 271 anywhere in
tblDataEntry.  It should be automatically done on the append query
like you have done with JRNAL_LINE 1,2 and 3 etc.

I will throw in another technicality - the table name tblSystem could
be tblSystemABC or something like that with the last three letters
being unique.  Is it possible to amend your query above to say INSERT
INTO tblsystem then lookup the "CO_CODE" field (as discussed further
below).  I guess its possible if they are joined together somehow.

Now as the seperate issue of the tblRefs which I think I stated
incorrectly.

There is two fields which I have not given to you in my sample data.
These are "CO_CODE" and "JRNAL_NO".  "CO_CODE" is keyed in by the user
when entering the data.  "JRNAL_NO" is automatically generated from
another piece of software which other users use or I would get Access
to do the autonumbering.  So what I need is before the Append Query is
run, we first lookup tblRefs WHERE CO_CODE = CO_CODE in tblDataEntry
and then lookup the number stored in "HIGH_JRNAL" which has a number
stored in it.  Our software will only allow one use of that number
which is stored in the tblRefs table so I need to ensure the same data
integrity here as well perhaps by adding a "Refresh Linked" table
sequence before pulling out that number.

So this number is then placed in the JRNL_NO field on the record
stored in tblDataEntry and then the tblRefs table is then updated to
what the next number should be so that a) when Access does this again,
the next available number is selected and b) when our users who don't
use Access book a transaction, it gives them the next available number
on their entry.

Thanks very much for your help.  It is greatly appreciated.

Yes; you can include a literal constant in the queries in the UNION:

INSERT INTO tblSystem
SELECT Acc, Amount, [Desc], JRNAL_LINE  FROM
(SELECT Acc, Amount, [Desc], 1 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount IS NOT NULL
UNION ALL
SELECT Acc1, Amount1, [Desc], 2 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc1] IS NOT NULL AND Amount1 IS NOT NULL
UNION ALL
SELECT Acc2, Amount, [Desc], 3  AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc2] IS NOT NULL AND Amount2 IS NOT NULL)

Note that I'm putting brackets around the reserved word DESC (a keywordfor a
query's Order By clause, meaning descending); it would be better not touse
such reserved words as fieldnames.

You could run a separate APPEND query appending into tblRefs. It would be
safest to run the two queries in sequence from VBA code, using a "Transaction"
to ensure that they either both run, or neither runs. Macros will not serve
your turn here, I don't think - you'll need VBA and a bit of error checking.

You could join HIGH_JRNAL to tblDataEntry in the UNION query. It's (almost
certainly) neither necessary nor appropriate to update the "scratch"
tblDataEntry, since you can just pick up the value as needed.

Now that I simply don't understand at all.

A Table HAS NO ORDER. It's best viewed as an unordered "bag" of records.. You
can insert the JRNL_NO - once we figure out what it should contain, it can't
be simultaneously 1, 2, 3 as you described and also unique! - into the table,
and then use it in a Query sorting the data in the table; but the "order of
records in the table" is a meaningless and useless concept in Access.

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

 

[Stuart]

John

Do you still think this is still something you can help me with? I
didn't manage to find the answer on my own today. I found something
on using a string at the end of the table needs dynamically.

Thanks and best regards

Stuart

John

Thanks for getting back to me.  I've had enough coffee for today.

Do you think I would be better posting on the VBA access forum?  I say
this because of the part about the table name being "tblSystemABC"
with the string "ABC" coming from tblDataEntry in field "CO_CODE".

The tblsystem fields and tblDataEntry fields are almost the same with
the exception of "GAMOUNT", "NAMOUNT" and "VAT" which should always be
inserted into tblSystem under the one field "AMOUNT".  The two tables
share the same field "CO_CODE" and "ACCNT_CODE" but I could always
change these in the tblDataEntry table if needed.

I think I caused the confusion regarding ACCNT_CODE 271.  I didn't use
this in my sample data but the query would insert the data for two
"ACCNT_CODE" which is stored in tblDataEntry and the 3rd insertion
would always be the "VAT" amount (and other fields) and "ACCNT_CODE"
271 automatically as it will never change.

The tblRefs part would be the last and we're almost there with the
solution.  I appreciate the education your giving me here.

tblRefs has "CO_CODE" and "HIGH_JRNAL" as field as well as other
fields.

I am saying that before we run the amend query to put the data from
tblDataEntry to tblSystem we should first fill in the field "JRNAL_NO"
by refreshing the linked table "tblRefs" and then extracting the value
from "HIGH_JRNAL" where the "CO_CODE" is the same as the record in
tblDataEntry and placing it in the record under "JRNAL_NO" and then
automatically updating "tblRefs" with the next number in the sequence
so add 1 to the number extracted before then it should move right on
to the insertion of the data records into the tblSystemABC (or
whatever CO_CODE is per tblDataEntry record).  This is the only way I
can think to do it in order to reduce the time between the system
extracting the reference number because I am conscious that someone
else in the office can book a journal while this sequence of events is
being undertaken so the linked table tblRefs should be refreshed as
often as needed.  I do actually have a code for refreshing linked
tables on open so I can use that to add to the control button to call
this sequence of events.

Can you help when you've a sufficient amount caffeine in you to tackle
this?

Best regards and thanks again

Stuart

John

When I amend the SQL statement you provided to match my actual fields
in my table I get a message that states the following:

Circular reference caused

INSERT INTO tblSystem
SELECT [ACCNT_CODE] AS ACCNT_CODE, [NAMOUNT] AS NAMOUNT, [DESCRIPTN]
AS DESCRIPTN, [JRNAL_LINE] AS JRNAL_LINE
FROM (SELECT [ACCNT_CODE], [NAMOUNT], [DESCRIPTN], 1 AS [JRNAL_LINE]
FROM tblDataEntry
WHERE [READY] = True
UNION ALL
SELECT [SUPP_CODE], [GAMOUNT], [DESCRIPTN], 2 AS JRNAL_LINE
FROM tblDataEntry
WHERE [READY] = True
UNION ALL
SELECT 271 AS [ACCNT_CODE], [VAT], [DESCRIPTN], 3  AS JRNAL_LINE
FROM tblDataEntry
WHERE [READY] = True)  AS [%$##@_Alias];

You'll see it adds something to the end as well. The Alias part "AS [%
$##@_Alias];".

ACCNT_CODE is actually a text field.  Also, ACC3 should actually
always be account number 271.  We don't store 271 anywhere in
tblDataEntry.  It should be automatically done on the append query
like you have done with JRNAL_LINE 1,2 and 3 etc.

I will throw in another technicality - the table name tblSystem could
be tblSystemABC or something like that with the last three letters
being unique.  Is it possible to amend your query above to say INSERT
INTO tblsystem then lookup the "CO_CODE" field (as discussed further
below).  I guess its possible if they are joined together somehow.

Now as the seperate issue of the tblRefs which I think I stated
incorrectly.

There is two fields which I have not given to you in my sample data.
These are "CO_CODE" and "JRNAL_NO".  "CO_CODE" is keyed in by the user
when entering the data.  "JRNAL_NO" is automatically generated from
another piece of software which other users use or I would get Access
to do the autonumbering.  So what I need is before the Append Query is
run, we first lookup tblRefs WHERE CO_CODE = CO_CODE in tblDataEntry
and then lookup the number stored in "HIGH_JRNAL" which has a number
stored in it.  Our software will only allow one use of that number
which is stored in the tblRefs table so I need to ensure the same data
integrity here as well perhaps by adding a "Refresh Linked" table
sequence before pulling out that number.

So this number is then placed in the JRNL_NO field on the record
stored in tblDataEntry and then the tblRefs table is then updated to
what the next number should be so that a) when Access does this again,
the next available number is selected and b) when our users who don't
use Access book a transaction, it gives them the next available number
on their entry.

Thanks very much for your help.  It is greatly appreciated.

Dear John
Thanks for providing the code.  I will read more Append Queries.
Is it possible to keep all the records in order?  As you know, 1
record in tblDataEntry relates to 3 records in tblSystem.
I have a field called JRNAL_LINE and in here they should be given a
number 1,2 or 3.  Can this be done with append query?
Yes; you can include a literal constant in the queries in the UNION:
INSERT INTO tblSystem
SELECT Acc, Amount, [Desc], JRNAL_LINE  FROM
(SELECT Acc, Amount, [Desc], 1 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc] IS NOT NULL AND Amount IS NOT NULL
UNION ALL
SELECT Acc1, Amount1, [Desc], 2 AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc1] IS NOT NULL AND Amount1 IS NOT NULL
UNION ALL
SELECT Acc2, Amount, [Desc], 3  AS JRNAL_LINE
FROM tblDataEntry
WHERE [Ready] = True AND [Acc2] IS NOT NULL AND Amount2 IS NOT NULL)
Note that I'm putting brackets around the reserved word DESC (a keyword for a
query's Order By clause, meaning descending); it would be better not to use
such reserved words as fieldnames.
Also, I have another table called "tblRefs" which is the accounting
system keeping track of sequence numbers.  I cannot move the
sequential number to Access as there is another front end piece of
software that does all that.  This is like an add-on to that.  For
each of the records created during the Append Query - a reference
number should be added to the "JRNL_NO" field from "tblRefs".
You could run a separate APPEND query appending into tblRefs. It would be
safest to run the two queries in sequence from VBA code, using a "Transaction"
to ensure that they either both run, or neither runs. Macros will notserve
your turn here, I don't think - you'll need VBA and a bit of error checking.
Here is the sequence of events that I think should happen that might
be possible to do in this append query.
1.  Macro will first lookup reference from field HIGH_JRNAL where
company is same as "COCODE" in tblRefs.  This number should be added
to the record in "JRNL_NO" within tblDataEntry.
You could join HIGH_JRNAL to tblDataEntry in the UNION query. It's (almost
certainly) neither necessary nor appropriate to update the "scratch"
tblDataEntry, since you can just pick up the value as needed.
2.  Then the system will amend the number in tblRefs with the same
criteria "COCODE" with the next number.  This ensure the next number
used by the accounting software is correct on the other front end
product.
Now that I simply don't understand at all.
3.  Then the records will be added to the tblsystem but will have a
1,2 and 3 added to the JRNL_LINE field.
So then the records can be related by way of the "JRNL_NO" and sorted
by this number.  The "JRNL_NO" will always be unqiue.
A Table HAS NO ORDER. It's best viewed as an unordered "bag" of records. You
can insert the JRNL_NO - once we figure out what it should contain, it can't
be simultaneously 1, 2, 3 as you described and also unique! - into the table,
and then use it in a Query sorting the data in the table; but the "order of
records in the table" is a meaningless and useless concept in Access.
--
             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com