• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

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
0
Anthony6890
Asked:
Anthony6890
  • 13
  • 9
1 Solution
 
Rey Obrero (Capricorn1)Commented:
why is it important that you have this record at the bottom of the table?
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
0
 
Anthony6890Author Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
<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.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Anthony6890Author Commented:
Text File.

Ok, I will try the query suggestion now.
0
 
Anthony6890Author Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
if your queries in the Union are all based on the same table, yes you can use select *
0
 
Anthony6890Author Commented:
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...
0
 
Anthony6890Author Commented:
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...
0
 
Rey Obrero (Capricorn1)Commented:
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.
0
 
Anthony6890Author Commented:
2 tables, each table has 166 fields.
0
 
Rey Obrero (Capricorn1)Commented:
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.
0
 
Anthony6890Author Commented:
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...
0
 
Rey Obrero (Capricorn1)Commented:
Where did you read that???

can you upload a copy of your db?
0
 
Anthony6890Author Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
you don't have to include the data,
create a new db, import the 2 tables without the records

upload the created db
0
 
Anthony6890Author Commented:
Ok, here you go.  This has the two tables I'm trying to union.
Table_Union_Example.accdb
0
 
Rey Obrero (Capricorn1)Commented:
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.
0
 
Anthony6890Author Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
< 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.
0
 
Anthony6890Author Commented:
I appreciate your suggestions but considering the company requires a fixed width layout file and these are the minimum field needed rearchitecturing this isn't an option.

I figured out a way to force the record to be last using vba anyway.
0
 
PatHartmanCommented:
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.
0
 
Anthony6890Author Commented:
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.
0
 
Anthony6890Author Commented:
The proposed solutions were unrealistic for the needs of the project and it was figured out using vb coding.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now