Solved

Access Append Query to Force Record to Bottom of Table

Posted on 2016-11-14
23
23 Views
Last Modified: 2016-11-19
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
Comment
Question by:Anthony6890
  • 13
  • 9
23 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886413
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886429
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886449
<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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886504
Text File.

Ok, I will try the query suggestion now.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886530
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886537
if your queries in the Union are all based on the same table, yes you can use select *
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886683
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886787
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886793
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886794
2 tables, each table has 166 fields.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886844
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:Anthony6890
ID: 41886869
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886879
Where did you read that???

can you upload a copy of your db?
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886881
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886883
you don't have to include the data,
create a new db, import the 2 tables without the records

upload the created db
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886888
Ok, here you go.  This has the two tables I'm trying to union.
Table_Union_Example.accdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886913
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41886916
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41886985
< 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
 
LVL 1

Accepted Solution

by:
Anthony6890 earned 0 total points
ID: 41886992
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41888310
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41888386
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
 
LVL 1

Author Closing Comment

by:Anthony6890
ID: 41894078
The proposed solutions were unrealistic for the needs of the project and it was figured out using vb coding.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now