Importing from Excel and splitting into two tables - customers and orders (1 Viewer)

jellyberg

New member
Local time
Today, 18:59
Joined
Aug 25, 2015
Messages
5
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:59
Joined
Jan 23, 2006
Messages
15,364
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.
 

jellyberg

New member
Local time
Today, 18:59
Joined
Aug 25, 2015
Messages
5
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.
 

sneuberg

AWF VIP
Local time
Today, 11:59
Joined
Oct 17, 2014
Messages
3,506
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.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 14:59
Joined
Jan 23, 2006
Messages
15,364
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.
 

jellyberg

New member
Local time
Today, 18:59
Joined
Aug 25, 2015
Messages
5
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:59
Joined
Jan 23, 2006
Messages
15,364
Glad things are progressing.

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.
 

Users who are viewing this thread

Top Bottom