Access Append Query to Force Record to Bottom of Table
Hi All,
I need to append a trailer record to a table that I've populated in Access. I need to force a specific row of data to the bottom of the table. I will be appending data to the table and this data needs to go to the bottom. The row I'm trying to append has three values from another table with totals from three columns.
When I currently go to append the record, the record becomes the second record of the table, there is already a header record that I've appended first. Then I have data under the header record and I need to end with this trailer record. The table I'm appending to is simply a formatting table, meaning I use this table to export as a fixed width text file- I don't do any further analysis with it.
Anyone have any suggestions on how I can do this?
-Anthony
I need to append a trailer record to a table that I've populated in Access. I need to force a specific row of data to the bottom of the table. I will be appending data to the table and this data needs to go to the bottom. The row I'm trying to append has three values from another table with totals from three columns.
When I currently go to append the record, the record becomes the second record of the table, there is already a header record that I've appended first. Then I have data under the header record and I need to end with this trailer record. The table I'm appending to is simply a formatting table, meaning I use this table to export as a fixed width text file- I don't do any further analysis with it.
Anyone have any suggestions on how I can do this?
-Anthony
ASKER
The vendor that I will be remitting this file to, requires the record be at the bottom- for processing purposes on their side.
Will the Order By clause work for an append query as well?
Will the Order By clause work for an append query as well?
<The vendor that I will be remitting this file to, requires the record be at the bottom- for processing purposes on their side. >
what type of file?
like I said use a query and export the query to the file.
what type of file?
like I said use a query and export the query to the file.
ASKER
Text File.
Ok, I will try the query suggestion now.
Ok, I will try the query suggestion now.
ASKER
Do I have to specify each column in the union query? There are 166 columns in use in the main table. Or can I do a Select* to use all the columns?
if your queries in the Union are all based on the same table, yes you can use select *
ASKER
Rey, the number of columns have to exactly match in all of the tables? I thought that if the column headings were the same that the Union query would work...
ASKER
Rey this doesn't work as I'me getting an error of too many fields defined when I try to execute the union. Each table has about 166 fields...
how many tables are involved?
<I thought that if the column headings were the same that the Union query would work>
Yes and the columns Data type must match.
<I thought that if the column headings were the same that the Union query would work>
Yes and the columns Data type must match.
ASKER
2 tables, each table has 166 fields.
any more detailed explanations..
union query can take up to 255 fields
from access help specification
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of ANDs in a WHERE or HAVING clause 99
Number of characters in an SQL statement approximately 64,000
see if you have exceeded any of those listed above
also.. do a compact and repair of your db.
union query can take up to 255 fields
from access help specification
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of ANDs in a WHERE or HAVING clause 99
Number of characters in an SQL statement approximately 64,000
see if you have exceeded any of those listed above
also.. do a compact and repair of your db.
ASKER
From what I was reading, the union query takes the total number of columns between both tables and if they sum to over 255 then it doesn't work, regardless of whether the columns are the same heading or not...
Where did you read that???
can you upload a copy of your db?
can you upload a copy of your db?
ASKER
Found it on a couple of websites online, just google it.
I cannot upload it, way too big and too much confidential information in it.
I cannot upload it, way too big and too much confidential information in it.
you don't have to include the data,
create a new db, import the 2 tables without the records
upload the created db
create a new db, import the 2 tables without the records
upload the created db
ASKER
Ok, here you go. This has the two tables I'm trying to union.
Table_Union_Example.accdb
Table_Union_Example.accdb
your table is not properly design.
you have fields that are repeated several times, fields with number prefix
I suggest you change the design of your tables.
you have fields that are repeated several times, fields with number prefix
I suggest you change the design of your tables.
ASKER
I would if I could, you don't understand the file structure I'm dealing with. I have to remit a file that requires that dependents of an employee be sent horizontally and not vertically. This is the only way that I can effectively create this file.
I'm guessing though that I'm right and you are experiencing the same issue with defining too many fields?
I'm guessing though that I'm right and you are experiencing the same issue with defining too many fields?
< you don't understand the file structure I'm dealing with.>
on the contrary, I fully understand.
< I have to remit a file that requires that dependents of an employee be sent horizontally and not vertically. This is the only way that I can effectively create this file.>
That is not correct. there other ways you can do this.
first is revise your table structure..
if you can't do it, I suggest you hire somebody to correct your table design.
on the contrary, I fully understand.
< I have to remit a file that requires that dependents of an employee be sent horizontally and not vertically. This is the only way that I can effectively create this file.>
That is not correct. there other ways you can do this.
first is revise your table structure..
if you can't do it, I suggest you hire somebody to correct your table design.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rey's advice was sound. You are allowing an export requirement to dictate a table design and that is the wrong approach. Normalize your table and then flatten the many-side table and create the header and trailer in the export routine.
I read and write a number of bizarre flat file formats, many of which require multiple records of different formats to create a transaction. Typically you will need to use VBA Print to write these outputs rather than the TransferText method.
I read and write a number of bizarre flat file formats, many of which require multiple records of different formats to create a transaction. Typically you will need to use VBA Print to write these outputs rather than the TransferText method.
ASKER
I agree with you Pat, but considering my time constraint, I had to work with what I had.
I can only use Rey's suggestion when I have time to rearchitecture the db itself. For now, I was able to get everything to work quite well and very quickly without losing the integrity of the data.
I can only use Rey's suggestion when I have time to rearchitecture the db itself. For now, I was able to get everything to work quite well and very quickly without losing the integrity of the data.
ASKER
The proposed solutions were unrealistic for the needs of the project and it was figured out using vb coding.
you can have that record to show at the bottom, using a query with an Order By clause on the field that you can assign a value for this record to always show as the last record in your query