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.
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.