Picking Order by Order in a linked Excel file

Question

Hi

I get orders in an excel file. It may be one order with one order line. It can also be an order with multiple order lines. I manage to load order number and shop if the Excel file only contains one order number. I also manage to load order lines if the Excel file contains only one order (only one order number)

But how do I do to handle each order number separately and add these orders to order lines if the file contains several different order numbers.

The pic below shows the Excel file (with descriptions)

---

The Access App handles Orders via a MainForm and SubForm when doing it manually,

Best // Peter Forss Stockholm GMT +1.00

Answer 1

But how do I do to handle each order number separately and add these orders to order lines if the file contains several different order numbers.

Hi Peter,

The import functions are "the greatest common factor", so they can handle standard imports, but hardly (if not) exceptions.

For that reason I never use them. I developped my own import functions from csv-files (convert xls/xlsx to csv)

The recipe is simple:

Read line by line through the csv-file. For each line use the Split function to give the individual cells. Depending on the content of special cells, you can add an orderline to a new order, add different orderlines to an existing or new order, etc.

In this way I can import almost anything. For parts of the process you can develop specialized functions. For instance the processing of the file (including error handling) is just like:

    While (Acive_file(filename,progress_type,line_read))
        ...
    Loop

Happy Newyear.

Imb.

Answer 2

Create a link to the Excel file and execute two separate INSERT INTO statements, firstly inserting distinct rows into the referenced Orders table and then into the referencing OrderLines table:

INSERT INTO Orders(OrderNr,OrderDate,Store)
SELECT DISTINCT OrderNr,[Date],Store
FROM [LinkedExcelTable];

INSERT INTO OrderLines(OrderNr,ArtNo,Qty)
SELECT OrderNr,[Art No],Qty
FROM [LinkedExcelTable];

For a demonstration of how to import data from Excel and decompose it into a set of correctly normalized tables see DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file firstly imports the Excel data into a single 'master' table, then executes a set of 'append' queries in a specific order to insert the data into a set of normalized tables, including those which model a many-to-many relationship type, giving a brief explanation of each stage of the operation.

---

Ken Sheridan, Stafford, England

Answer 3

You have a one-to-many relationship between 'Ordernr' and 'Art no'. So one 'Ordernr' can have many 'Art no' entries. In ACCESS you should have the following tables and fields:

Orders table:

1. OrderID (Primary key AutoNumber Data Type)
2. Ordernr
3. Date
4. Store

Art table:

1. ArtID (Primary key AutoNumber Data Type)
2. Ordernr
3. Art no
4. Qty

In the Database Tools--> Relationships window create a one-to-many realtionship between the Order table 'Ordernr' field and the Art table Ordernr field.

Import your data from EXCEL into the Orders table using:

1. Ordernr
2. Date
3. Store

Import your data from EXCEL into the Art table using:

1. Ordernr
2. Art no
3. Qty

In your Order Subform, create a Record Source query including all Art table fields and a textboxs bound to each field. The default value of the SubForm 'Ordernr' textbox will be:

=Forms![Your Main Form Name]![Ordernr]

In your Order main form there should be a Master/Child relationship using 'Ordernr' in your Subform.

Answer 4

Thank you all!

This is such a great forum.

---

Best // Peter Forss Stockholm GMT +1.00