[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Append Query to Force Record to Bottom of Table

Posted on 2016-11-14
23
Medium Priority
?
53 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 40

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

825 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