UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Append Query, Any Versions    
 
   
swarnamjvasudeva...
post Mar 30 2018, 06:16 AM
Post#1



Posts: 10
Joined: 25-October 17



Hi,
Sorry for my basic doubt.
I am new to Access 2013.
I would like to make a table by appending multiple fields for multiple tables.
The common field is supplier code between to be append table,

The table looks like this
[supplier code]
[supplier name]
[jan18 receipt]
[jan18 reject]
[feb18 receipt]
[feb18 reject]
......
......
......so on.

Receipt data is from one table for each month.
Reject data is from another table for each month.
It is not necessary that all supplier supply every month. So there may be null data in particular month. And so for reject.....

How to make append these data to common table?

Pl help
Go to the top of the page
 
DanielPineault
post Mar 30 2018, 07:22 AM
Post#2


UtterAccess VIP
Posts: 6,774
Joined: 30-June 11



You need to first normalize your data so your structure is sound and static. You should not be adding field every month, ... this break normalization rules and you will quickly hit the limits of 255 columns/fields in a table, then what?! So properly design things to ensure you solution will be rock solid.


Why would you need to store Supplier Code and Supplier Name? You should have a supplier table with a primary key (SupplierId perhaps) and use it as the foreign key in this new table you are trying to design.

At first glance, I'd probably be doing something like

YourTableName
-----------------
EntryId
SupplierId
EntryType (1-accepted, 2-rejected)
EntryDate


Suppliers
----------
SupplierId
SupplierName
SupplierCode
...


Also, if this data is already in other tables, why not simply use a query to gather this information? No need to append it to another table and duplicate the data unnecessarily.

You get the idea I think.
Go to the top of the page
 
Jeff B.
post Mar 30 2018, 07:27 AM
Post#3


UtterAccess VIP
Posts: 10,271
Joined: 30-April 10
From: Pacific NorthWet


If you are "adding multiple fields for multiple tables", that sounds more like what you'd have to do if you were limited to using a spreadsheet.

As Daniel has pointed out, Access is a relational database. Check out resources here at UA re: normalization and relational database design.
Go to the top of the page
 
GroverParkGeorge
post Mar 30 2018, 07:37 AM
Post#4


UA Admin
Posts: 35,534
Joined: 20-June 02
From: Newcastle, WA


We have an excellent set of starter articles that will help you understand what Daniel and Jeff are telling you about valid relational database table design.

Go to the top of the page
 
swarnamjvasudeva...
post Mar 30 2018, 07:55 AM
Post#5



Posts: 10
Joined: 25-October 17



Thank you all for your insights.....I understand. Why would I go for appended table. ..?
I will go through the starters more.

Thanks all!
Go to the top of the page
 
zaxbat
post Mar 30 2018, 08:51 AM
Post#6



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


This is advanced stuff....but if you have a lot a lot of data then it could be worth the time to make a nice VBA routine to loop through all input tables one at a time and append them to one table.


Quasi code would be something like this (quasi code does not actually run....but shows the types of functions and basic flow of logic)

CODE
private sub PutAllMyFilesTogether()

DIM i as integer
DIM myfile, mySqlTxt as string
myFileList = List_Of_Files("(like *receipt*) OR (like "*reject*)")

docmd.setwarnings false

for i = 1 to myFileList.filecount

   myfile = myfilelist.filenames(i).fname

   if i = 1 then
      mySqlTxt = "SELECT   field1, field2, field3, field4 INTO mytmptable FROM myfile RIGHT JOIN myHeaderFile on myfile.keeToHeader = myHeaderFile.ID;"
   else
      mySqlTxt = "INSERT INTO mytmptable SELECT   field1, field2, field3, field4 INTO mytmptable FROM myfile RIGHT JOIN myHeaderFile on myfile.keeToHeader = myHeaderFile.ID;"
   endif

   docmd.runsql mySqlTxt
next i

docmd.setwarnings true

end sub




Now, if you have less than 30 files....i would probably not try to find a routine to make your file list.....I would just manually enter the filenames into an array first, and then go through the For Next loop.


All of this assumes that the file structure of the reject and receipt files are identical and that they both connect to the same header file.
This post has been edited by zaxbat: Mar 30 2018, 08:56 AM
Go to the top of the page
 
GroverParkGeorge
post Mar 30 2018, 10:01 AM
Post#7


UA Admin
Posts: 35,534
Joined: 20-June 02
From: Newcastle, WA


We simply do NOT want to create spreadsheet style tables in Access. That's fundamental and any experienced Access developer should know that.





Go to the top of the page
 
zaxbat
post Mar 30 2018, 10:37 AM
Post#8



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


my example was to make it into a header table and a detail table.....no columns though I got lazy and just said field1, 2, 3 because i don't know what fields are in his detail records.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd August 2019 - 11:23 PM