?
Solved

Access Append Query to Force Record to Bottom of Table

Posted on 2016-11-14
23
Medium Priority
?
40 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 9
23 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 38

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

777 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