Need help with array

[programmingbeginner]

My company has a table full of orders. The customer is an unique order number but each item has a unique sku number. I want to use an array assign order 1 to each line item with that order number and push into a table. I am new to programming and have no idea where start.
Array. Customer order. Sku
1. 12 3 40. And
1. 12340. And-1
1. 12340. And-2
2. 23300. 123
2. 23300. 1244

[June7]

I don't understand. Why should each line have 1 assigned?

[programmingbeginner]

For 1 order to be shipped for that customer and than for a different customer I would have a 2.

[John_G]

Hi -

I don't see why you need the 1 and 2 either - the order number already tells you which customer it is, since (I hope) the customer ID is kept in the Orders table.

Can you provide a bit more information on what you are trying to do?

[programmingbeginner]

We are going to a different system and it will have to be uploaded differently. There will be one customer number and the sku numbers in different columns. I am trying to turn out current data into that format. Is there a way I can upload on here what I have and what I need

[June7]

Can attach files to post. Click 'Go Advanced' below the post editor window. Size limit of 500KB for uncompressed files, 2MB for zip.

You want to assign a new identifier for each customer? This will not be easy in query or form. Review http://allenbrowne.com/ranking.html

Otherwise, might need VBA procedure using recordset object to cycle through records and update field with new value.

[John_G]

Hi -

Have you done an analysis of your data to determine what tables you will need, and what fields they will have? You need to have some idea of your database structure before you can import.

John

[orange]

programmingbeginner,

You're going to have to explain/describe your situation; what data you have; and what new data structure you need.
We can't guess at your requirements. Please try to tell readers in plain English.

Good luck.

[programmingbeginner]

I am currently exporting the data from the table in access and doing the rest in excel. I want access to be able to do it all. I hope the visual helps because I am having trouble explaining since I do not know really what to ask for. I was just informed that it is called an array. Thank you so much for all your help

[June7]

Technically, every table is an array (most are 2-dimensions). An array is also a VBA programming object. I doubt it will be useful for your situation.

What you show as desired output is a non-normalized data structure (3-dimension array). If a field is first populated with the Order sequence, a CROSSTAB query could produce that result.

Here is one possible way to populate the Order field:

UPDATE Tablename SET [Order] = DCount("*","Tablename","[Customer Code]='" & [Customer Code] & "' AND [SKU Code]<'" & [SKU Code] & "'")+1;

What do you mean by 'going to a different system' - are you abandoning the Access database? I would find it alarming if you want to migrate to a setup that requires non-normalized data structure. This structure will give you lots of frustration down the road. What is this other system?

How many possible SKU values could each customer have? A table/query is limited to 255 fields.

[orange]

programmingbeginner,

Maybe June understands your setup and needs, but I don't. You're going to have to try harder to tell us in plain English WHAT you are trying to do.

If you can't describe it, I don't think you can design/build it.

Let me give you a scenario:

I'm in front of you in a McDonalds line. I ask you "What you're working on"? What would you tell me, in plain English? And I'm not going to quit if you say, "Bugger off".

[John_G]

Programmingbeginner -

Everything you need can probably be done in Access; in fact the Customer - Orders - Products scenario is one of the commonest used to teach Access and databases in general.

What is the table/field structure you are currently using? For this scenario, you need at least four tables (Customer, Product, Order and Order_Detail).

In your screen diagram, I don't see any data for an order number - you won't get far without an order number and order table.

What do you need the "Order" values (in red) for? I see no useful purpose for it, and what determines which line is "1" and which is "2"?

If you are thinking about making a table that looks like your bottom line - don't. As June7 pointed out, you can get that result using a query.

So, please show us your current structure (tables/fields) and we can go from there.

John

[programmingbeginner]

I am getting a conversion Failure. How do I fix it?

[June7]

Change the Ranking field to a number type.

I thought Customer_Code and SKU_Code would be text (SKU_Code shows a hyphen). If the fields are not text, then remove the apostrophe delimiters from the DCount arguments.

[programmingbeginner]

Thank you June7. It is doing exactly what I wanted. You have been a life saver!!