Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27

    Batch Imports - Perfoming a batch import from a subform that is connected to the parent form

    I am looking for a way to create a button on a subform that will perform a batch import of data into the subforms table that is specific to each record in the table of the parent form.



    Let me explain.

    I have two tables: tbl_Accounts and tbl_ServiceLocations.

    In the tbl_Accounts table I have the following fields:
    AccountID (PK)
    AccountName (Short Text)
    AccountPhone (ShortText)
    AccountFax(Short Text)
    AccountBillingStreet1 (Short Text)
    AccountBillingStreet2 (Short Text)
    AccountCity (Short Text)
    AccountState (Short Text)
    AccountZipcode (Short Text)

    In the tbl_ServiceLocations table, I have the following fields:
    ServiceLocationID (PK) (Autonumber)
    AccountID (FK ) (number)
    LocationName
    LocationStreet1
    LocationStreet2
    LocationCity
    LocationState
    LocationZipcode

    I have created a 1 to many relationship between the two tables using the AccountID and stipulating "Enforce Referential Integrity" & "Cascade Update Related Fields." I also chose option number 3 under Join type.
    I have create a form from the tbl_Accounts table and placed a subform inside that form using the tbl_ServiceLocations table. On the subform (it is a tabbed subform-dont know if that makes a difference) I have linked the Master and Child fields in the data property sheet to the AccountID in the parent form.

    Everything works fine. I can flip through each of the tabs on the sub form and enter data which will be automatically tied to the tbl_Account table by AccountID. Specifically meaning, I can add a service location (Location Name, Street Address, City...blah blah blah) that is directly related to the specific Account Name inside the tbl_Account table. Again, everything works just fine.

    However, I have some accounts that have 100's even thousands of service locations. So my question is, how do I place a button on the "ServiceLocation Subform" to give the use the option to do batch import of many service locations into just that one specific Account name?

    Finally, I have two spreadsheets. 1. tbl_Accounts which populates the data in that table just fine. 2. I also have another spreadsheet with Account Name, Location Name, Street Address...blah blah blah.. that works just fine in the service locations table.

    One caveat here - the Account Names in the tbl_Account Table spreadsheet & the Account Names in the tbl_ServiceLocations spreadsheet differ slightly (this is due to the export process of the main data source)

    Any advice or guidance is always appreciated.

    Thanks!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your caveat makes this impossible to answer. At the very least would need to see some examples of what 'differ slightly' means
    .

  3. #3
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Quote Originally Posted by Ajax View Post
    your caveat makes this impossible to answer. At the very least would need to see some examples of what 'differ slightly' means
    .
    Yep...I was afraid of that. Which means...I need to clean up the data first.... to reflect exact AccountNames in both tables.....which means I need to research finding an excel formula that will do that for me. I have over 3500 accounts and this is not going to be easy

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show us a couple of examples of related account names? You don't have to do it in Excel. it can be done in Access (you import into a temporary table, you "fix" the data then run the final append).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Yep...I was afraid of that.
    As requested, provide some examples of what 'differ slightly' means.

    If differences are one has spaces, one doesn't - just remove the spaces from the one that has them. If one has 'Ltd' and the other 'Limited', you might replace Ltd with Limited or remove both ltd and limited. Can't advise until we know what the issue really is.

    If this is an ongoing process, consider creating an 'alias' table to provide a common value. Perhaps something like

    tblAliases
    AliasPK autonumber
    Alias text
    Common long - links back to a common PK


    AliasPK...Alias................Common
    1...........Smiths Limited...1
    2...........Smiths Ltd.........1
    3...........W Smiths...........1


    If you already have records, then Common would be a foreign key to the relevant record

  6. #6
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Ha! Good news...I was able to successfully get the account names exported as exactly the same! This is great news because I have many more tasks to do that require same AccountNames.

    Anyway, given this news any ideas for my original question?

    One addition....I am adding to my imports... Meaning...along with having many service locations with each account name, I also have many contacts for each account.

    I created a third table called tbl_AccountContacts.
    Fields:
    AccountContactsID (PK) autonumber
    AccountID (FK) number
    FirstName shorttext
    LastName shorttext
    OfficePhone shorttxt
    MobilePhone shorttext
    Email shorttext

    On another tab (next to the service locations tab) of the subform in my Parent Accounts form, I placed a subform called frm_AccountContacts_Sub. Just as the ServiceLocations subform, I have linked the AccountContacts subform to the parent by using the Master/Child property sheet and set it to AccountID of the tbl_Accounts table. This also works just fine when I manually type in a new contact and it is automatically tied to the respective account. However, just as the ServiceLocation issue, I have many contacts to go with each account. (FirstName, LastName, OfficePhone, MobilePhone, Email etc...)

    I am assuming the Contacts import and the ServiceLocation import solution would be the exact same?

    I can upload spreadsheets if it helps?

    Also, I am open to changing the philosophy behind the design...meaning if it helps to create 3 separate "source" tables i.e. tbl_AccountsMaster, tbl_AccountContactsMaster, and tbl_AccountServiceLoactionsMaster to import all the respective data into...and then create Append Query's for each "target" tables (tbl_Accounts, tbl_Contacts, tbl_ServiceLocations), I will do it...I just need to know how ...

    Anyway, as always...I am very grateful for any ideas.

    Thanks,
    -mgm

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    still little to go on with regards practicalities but suggest you could do the following

    1. use transferspreadsheet to link to your spreadsheet
    2. use a query to append the data to your master table - this will have a criteria to limit what records are appended based on account name
    3. requery the subform

    repeat for contacts

    what I don't understand is why not just import everything in one go? why does it have to be a button on the subform to do one account at a time?

    your query in 2 above would link to your accounts table so you can pick up the ID to populate the FK in your other tables.

  8. #8
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Quote Originally Posted by Ajax View Post
    still little to go on with regards practicalities but suggest you could do the following
    what I don't understand is why not just import everything in one go? why does it have to be a button on the subform to do one account at a time?
    I absolutely would love to do it all in one shot. However I'm not sure how to do that? Are you saying to create one huge spreadsheet with all fields from all three tables (Accounts, Contacts, ServiceLocations) and combine all the data from my 3 existing spreadsheets and then do one massive import. Afterwards, create append queries to parse out the data to the respective tables?

    If I did this, how would I import 500 service locations to one Account? or 150 contacts to one account using one spreadsheet?

    Sorry for being so dense...my mind is just one dimensional I suppose... lol

  9. #9
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    **UPDATE**

    I have managed to upload all three tables successfully into the database using the same "AccountName" for tbl_Accounts, tbl_Contacts, and tbl_ServiceLocations.

    Now I am trying to "update or append" (which ever one works best) the AccountID to reflect the same in all three tables.

    As a test, I manually typed in the AccountID number that was established from the Account import, into the [tbl_Contacts].[AccountID] field. This field is joined to tbl_Account through a relationship. As you can tell by my attached screenshots, I have an issue where there are multiple contacts for many of the accounts. The good news is, when I do manually enter (update) the AccountID field in the Contacts table...it works! As you can see by the attached screenshots.

    However, notice the screenshot in which I have marked with a red and blue pen. You will see that the issue I am having. How can I use a query to place the coorosponding AccountID from tbl_Accounts into the respective AccountID in tbl_Contacts? I am fairly confidant, once I jump this hurdle, I can do the exact same thing for the ServiceLocation table.

    Thank you very much!
    -mgm

    Click image for larger version. 

Name:	contacts-populated.PNG 
Views:	12 
Size:	87.3 KB 
ID:	43929Click image for larger version. 

Name:	contacts-not-populated.PNG 
Views:	12 
Size:	47.9 KB 
ID:	43930Click image for larger version. 

Name:	tbl_accounts.PNG 
Views:	12 
Size:	89.1 KB 
ID:	43931Click image for larger version. 

Name:	tbl_contacts.PNG 
Views:	12 
Size:	110.2 KB 
ID:	43932

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Are you saying to create one huge spreadsheet
    definitely not.

    how can I use a query to place the coorosponding AccountID from tbl_Accounts into the respective AccountID in tbl_Contacts?
    as stated in post #7

    'your query in 2 above would link to your accounts table so you can pick up the ID to populate the FK in your other tables.'

  11. #11
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Quote Originally Posted by Ajax View Post
    definitely not.

    as stated in post #7

    'your query in 2 above would link to your accounts table so you can pick up the ID to populate the FK in your other tables.'
    Yep understood. I get it completely...and after hours of researching through google and others, I can't figure out ...how do I write the Append Query to do that?

    Can you point me in the right direction? I know how to create an append and/or update query...but I don't know what SQL code to use to define the criteria to:

    1. Compare both tables (tbl_Accounts & tbl_Contacts)
    2. Copy the [tbl_Accounts].[tbl_AccountID] (PK) into [tbl_Contacts].[tbl_AccountID] (FK)
    3. Where (criteria) [tbl_Accounts].[tbl_AccountName]=[tbl_Contacts].[tbl_AccountName] <---- I am not even sure that "WHERE" statement is the right approach?

    Also, as a side question... since I have already created the tables and imported the data into them (the [tbl_Contacts].[AccountID] - FK ) is still blank...
    Do I use an append query or an update query?

    Again, many thanks!
    -mgm

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You need tocreate an Update query in which you join the tbl_Accounts .AccountName to tbl_Contacts.AccountName and update tbl_Contacts!AccountMasterID to tbl_Accounts.AccountID

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    ***UPDATE***

    Thank you Ajax and VLad for all your help! I really appreciate everything you guys have done to help me out. You taught me quite a bit here with this issue and I can't thank you enough!

    I have good news...

    I have it all working! When I posted this question, I posted the same question in another forum and earlier today, a forum expert - PLog (thank you PLog !) gave me the code to do exactly what you guys have been telling me to do all along. I pasted it below for your review and maybe I can pay it forward a little by helping anyone else out there that may be struggling with this.

    NOTE: Change the respective tbl names to reflect yours

    Three steps...
    1. BACK UP BACK UP BACK UP before doing anything

    2. Make sure the data in the accounts table does NOT contain any duplicate account names. The following SQL snippit will help you discover duplicates:

    SELECT tbl_Accounts.AccountName, Count(tbl_Accounts.AccountID) AS CountOfAccountID
    FROM tbl_Accounts
    GROUP BY tbl_Accounts.AccountName
    HAVING (((Count(tbl_Accounts.AccountID))>1));
    3.Run the following update query to update the child tables to reflect the master table primary ID.

    UPDATE tbl_Accounts INNER JOIN tbl_ServiceLocations ON tbl_Accounts.AccountName = tbl_ServiceLocations.AccountName SET tbl_ServiceLocations.AccountID = [tbl_Accounts].[AccountID]
    WHERE (((tbl_ServiceLocations.AccountID) Is Null));

    Alrighty then...now I am off to enjoy a nice shot of some fine Kentucky Bourbon from my collection to celebrate!

    Cheers!
    -mgm

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    that "other" place is here
    https://www.access-programmers.co.uk.../#post-1742736

    mgmercuio, you should read this
    https://www.excelguru.ca/content.php?184
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Quote Originally Posted by Micron View Post
    Thanks for the reply Micron!

    Yep...I understand the philosophy behind cross-posting. This is why I came back here to thank VLad and Ajax for their hard work! In no way would I have been able to resolve this without their guidance and help! They plowed the road that led me to the river. In doing my research on this issue, including pursuing other forums, I learned a great deal...which in the end...is what it is all about. So just know...Vlad and Ajax's expertise was responsible for my success and for that reason, I can't thank them enough. This forum and all the others did what their intended purpose is designed to do ... which is to create a good community of like minded people and in the process...educate and assist poor illiterate slobs like me I find it very inspiring to know that there is communities like this out here willing to help out us noobs that cant program their remote control

    It is in the name of that spirit that I made my follow-up post on here to Thank VLad and Ajax as well as post the last piece of the puzzle. My intent was to do my part and provide some knowledge share to help the community. I actually ran a chat forum years ago along with doing a radio and TV show in my town directly aimed at helping novice computer users back in the day and I can't enough good things about the community knowledge base.

    Please know I am very grateful and fully acknowledge all the tremendous effort everyone puts into these forums.

    Respectfully,
    Matt

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  2. Batch importing PDF form data into access
    By bjd1020 in forum Import/Export Data
    Replies: 8
    Last Post: 06-17-2013, 12:20 PM
  3. Batch import
    By brandonze in forum Programming
    Replies: 6
    Last Post: 03-24-2011, 10:13 AM
  4. Batch Import and Update from DBF
    By Lady_Jane in forum Import/Export Data
    Replies: 7
    Last Post: 02-08-2011, 02:57 PM
  5. Batch import .txt
    By hchui in forum Import/Export Data
    Replies: 1
    Last Post: 10-14-2010, 08:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums