Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-20-2016, 01:44 PM   #1
jellyberg
Newly Registered User
 
Join Date: Aug 2015
Location: Northumberland, United Kingdom
Posts: 5
Thanks: 5
Thanked 0 Times in 0 Posts
jellyberg is on a distinguished road
Question Importing from Excel and splitting into two tables - customers and orders

I've been struggling with this problem for a while now, I can't get this one worked out.

Database structure:

i.imgur.com/VzAdlKh.png

I have two tables, tbl_orders and tbl_clients.

I have an Excel spreadsheet that the company previously used to store this data - a very basic setup where each row is a new order, with a ton of headings like client name, items ordered, address etc. There are about 1500 orders in the spreadsheet.

My problem:
I want to import data from the spreadsheet so that it ends up in those two tables - tbl_orders and tbl_clients. If a client has multiple orders (most of them do) then there should be one client record linked to many orders records.
Given that there are only 1500 orders in the spreadsheet, and this is just a one off import situation, I'm not too worried about falsely classifying multiple different clients as one client because they have the same name. As clients are from unstable financial situations, their addresses and situations often change so I can't really use that or other fields to determine duplicates.
One thing to note: the spreadsheet was filled in pretty non-standardly, for example yes/no fields have a 1 for yes and are blank for no in some parts while in other parts of the spreadsheet it's 1 for yes and 0 for no.

Attempted solution:
I created a tbl_excelImport, and I've got it successfully importing from the excel document using DoCmd.TransferSpreadsheet.
I attempted to use VBA and a bunch of loops to separate tbl_excelImport into tbl_orders and tbl_clients, and tried to use a dictionary clientNames to store which clientNames already exist, and use that to check if each record's client already exists. The code isn't working as intended though - it successfully adds the client from the first record in tbl_excelImport to tbl_clients, BUT for every other client clientNames.Exists(import!ClientName) seems to return True for some reason.
View my code online here: gist.github.com/jellyberg2/1851b812a4b9fe21093f. It's not working though!

I have a feeling this is something that can be solved with a query of some sort. However I don't know SQL beyond the very basics, so I'd appreciate noobie advice if possible!

Thanks in advance.

jellyberg is offline   Reply With Quote
Old 02-20-2016, 02:10 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,211
Thanks: 89
Thanked 2,020 Times in 1,968 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Importing from Excel and splitting into two tables - customers and orders

The typical Client-Orders type scenario consists of (at least) the following tables

Client -->Order--->OrderItems<--Product

What you have set up regarding tbl_excelImport is common. The data is first brought into a working table, then a series of queries are used to select data from the working table and append that data to the appropriate table while respecting your relationships.

Here is a sample data model from Barry Williams' site.

It sounds like you have a fair amount of data cleansing to do in preparation for the move to Access.
Getting your tables and relationships designed to meet your requirements is key to a successful database application.

Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
jellyberg (02-20-2016)
Old 02-20-2016, 03:04 PM   #3
jellyberg
Newly Registered User
 
Join Date: Aug 2015
Location: Northumberland, United Kingdom
Posts: 5
Thanks: 5
Thanked 0 Times in 0 Posts
jellyberg is on a distinguished road
Re: Importing from Excel and splitting into two tables - customers and orders

Quote:
Originally Posted by jdraw View Post
The typical Client-Orders type scenario consists of (at least) the following tables

Client -->Order--->OrderItems<--Product

What you have set up regarding tbl_excelImport is common. The data is first brought into a working table, then a series of queries are used to select data from the working table and append that data to the appropriate table while respecting your relationships.

It sounds like you have a fair amount of data cleansing to do in preparation for the move to Access.
Getting your tables and relationships designed to meet your requirements is key to a successful database application.

Good luck.
Thanks for your help jdraw.

I probably should have been more specific - this database doesn't need to store what products were ordered or any details about them. It's for a foodbank, the only thing they record is how many bags a client ordered. That standard model is useful to have in mind though, thanks!

The "series of queries" approach is exactly what I'm looking for here. Could you very briefly outline what kind of queries you'd create for this import process? Sorry if this is an obvious one, as I say I'm not familiar with SQL beyond the very basics.

Thanks again.

jellyberg is offline   Reply With Quote
Old 02-20-2016, 03:28 PM   #4
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Importing from Excel and splitting into two tables - customers and orders

It looks like you could do this with an append query. I suggest just getting into the query builder select the tbl_excelImport (assuming you ran your code to get one), choose Append query from the ribbon, and specify the tbl_clients as the table your are appending to. Then just try a couple of fields to get something working. If you have the ClientName set up as a unique index in the tbl_clients the query won't append duplicate records. When you save it and then run it, it will tell you how many records it appended and how many it didn't because of violations like duplicates. Maybe you can get by with filtering this way. When you run the query from code you can turn off the warnings. If you don't like that idea let me know and I'll tell you how you can do it more proper like.

Since I see you are using a function to convert the ages to numbers you may have to do this in the append query. I'd try it without first. If you need to do these conversions you can put ToNumber function in a module, declare it public, and then use it in expression in your append query.

After you get this working I recommend trying switching to just linking to the spreadsheet rather than importing it. You might be able to save yourself from the bloat you will get by importing it. When you import, your database will increase by the size of the imported spreadsheet each time you import it and you won't get the space back until you compact and repair. Given the relative small size (1500 orders) of the spreadsheet this may not be a concern.

If you run into problems you can upload your database and spreadsheet I'd be glad to set up a query for you.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 02-20-2016 at 04:27 PM.
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
jellyberg (02-21-2016)
Old 02-20-2016, 04:20 PM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,211
Thanks: 89
Thanked 2,020 Times in 1,968 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Importing from Excel and splitting into two tables - customers and orders

My approach would be to cleanse the data before breaking things into your final tables.
Further to sneuberg's advice, I'd try to resolve the Clients to remove the uncertainty. Ideally you'll identify your clients uniquely, but that my be extreme/unattainable in your case. In most databases you uniquely identify the records in each of your tables. But this isn't most databases, so you'll have to "wing it" as best you can.

What info do you have/need to identify your Clients? What exactly is a "bag"? Are all "bags" equivalent -- I wouldn't think so - but I'm not in that business. Get your tables designed, and make sure the design will handle any requirements you have for the database. If necessary mock up some test data and make sure you can answer any reporting and/or queries that you need. Adjust your data model/testing until it satisfies the need.
Identify each of the records in your working table because you may have to run multiple queries against the working table to populate your final tables and you will need something to keep the pieces in sync.

Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
jellyberg (02-21-2016)
Old 02-21-2016, 05:51 AM   #6
jellyberg
Newly Registered User
 
Join Date: Aug 2015
Location: Northumberland, United Kingdom
Posts: 5
Thanks: 5
Thanked 0 Times in 0 Posts
jellyberg is on a distinguished road
Re: Importing from Excel and splitting into two tables - customers and orders

Thanks so much for the advice sneuberg and jdraw.

I've set up an append query that appends clients from tbl_excelImport to tbl_clients and it works a treat! The append query runs a couple of functions I cooked up to strip extra spaces and fix capitalisation, it is exactly what I was after. Awesome!

The next task is presumably to set up a second query that appends orders to tbl_orders. This is itself should be quite similar to the clients one, BUT I'm not sure how I can set tbl_orders.ClientID to the value of the corresponding tbl_clients.ClientID.
My instinct is to use a DLookup or FindFirst or something to find the record in tbl_clients and set the tbl_orders.ClientID to that record's tbl_clients.ClientID... bit of a tongue twister!
I'll let you know how that goes. If there's an simpler or more effective way of doing this I'd love to hear your thoughts!

jdraw: Re "What exactly is a bag". The foodbank distributes bags of food (with a fairly balanced mix of whatever has been donated to the foodbank recently). Bags are distributed based on the needs of each client - a client with more dependent family members will obviously need more. It's important that the database stores the number of bags distributed, as this statistic is required when applying for grants etc. However the contents of the bags is immaterial. Hopefully that clears that up!

sneuberg: thanks for the kind offer to create the queries for me. I think I'm going to get there, thanks to your great advice, but if I do get stuck I know where to come for help!

Thanks again to you both. What a great, friendly community.
jellyberg is offline   Reply With Quote
Old 02-21-2016, 05:55 AM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,211
Thanks: 89
Thanked 2,020 Times in 1,968 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Importing from Excel and splitting into two tables - customers and orders

Glad things are progressing.

Quote:
The next task is presumably to set up a second query that appends orders to tbl_orders. This is itself should be quite similar to the clients one, BUT I'm not sure how I can set tbl_orders.ClientID to the value of the corresponding tbl_clients.ClientID.
That is part of why I suggested
Identify each of the records in your working table because you may have to run multiple queries against the working table to populate your final tables and you will need something to keep the pieces in sync.

You need something that can be used to identify the same Client when selecting different fields for different tables eg Client and Bags Received.


__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Tags
excel , import , query , table , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
linking customers and orders Sumaping General 6 05-09-2012 01:10 AM
Importing an Excel sheet - want two tables out of it cropduster Tables 1 05-01-2012 06:49 PM
Customers and Orders tables - simple relationship MikeLeBen Tables 54 03-04-2011 07:16 AM
Importing One Excel Sprdsht into Several Tables nexshark Tables 1 07-17-2007 06:45 PM
Importing web orders?? cyd00 Modules & VBA 1 08-01-2002 06:51 PM




All times are GMT -8. The time now is 03:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World