VBA Sorting data using several rules questions

[thape]

Hi, Steve



I asked some IT guy around they said I should use PL SQL. But I am a summer student trying to write a small program for the department and my boss want to use access(before they are using Excel but the file gets larger and macro run slow). This is my situation.

So for my table pending_temp, there is no structure. I mannually extract from other linked excel sheet. Thus the records are not arranged in the way my boss wanted. So I am writing this sorting function. It consists 40 fields, beside the 5 field using in the rule, other field are like Purchase order #, Item #, Comment, customer ,etc. thery are string or double. For some record the filed can be null.

Do you mind show me in detail how I can achieve the goal by VBA or SQL???

Thank you , Orange too


Thanks,

[Perceptus]

you should make a new thread if this isn't for the same subject. However. I would never take any suggestions from anyone in IT. if they were competent programmers then they wouldn't be IT.

[orange]

Interesting comment perceptus ---thape is a summer student with a summer job, let's keep the comments in context and focus.
However, perceptus may have realized that PL/SQL is a procedural language extension for Oracle. If you are dealing with an Oracle database, or if that is an option inside the organization, you may want to talk with the people/person who gave you your current task.
There are so many unknowns related to your post and situation that it is hard to give focused info/advice.

I think you're going to have to tell us more about the task you have; and the database structure you currently have; and a restatement of exactly what your objective is. It seems to be primarily a sort and report, but we know very little of the detail.

[thape]

thanks guys

Hi, Orange
I don't think they will allow me to change data on Oracle. Even now I just have Access, no connection to Oracle. So PL/SQL might not work in my case. There are only 4 people in my department including me, I think going to stick with Access.
The small program I am writing would be a input to a later program developed by the IT Department. Basically I am writing a sorting function to a scheduling program. Then my boss will make change on the sorted output.
My objective is sort all the data from Excel sheet using the rules given.
All the data I got is extracted from Excel. There are no structure for my table nor primary key.


hope this is clear

[orange]

thape,

I was not and am not suggesting you do anything with Oracle. PL SQL is an Oracle language. It is not for Access. If you have been given instructions to do something in Access, then carry on and forum members will offer advice and assistance. You haven't told us much, before now, about your environment. We are trying to help, but do not have a clear picture of WHAT you are trying to do or what tools you have available to you.

It sounds like you get an extract from Excel, then put this into an Access database (to manipulate the data), then either you output a file, or have a query/procedure that your boss will use to "make changes".

If you take an Excel extract and put it into a database(Access) you may have to normalize the data and make more than 1 table, then create a query or function to manipulate/sort the required data to provide the output as requested by your boss.

Since Steve has looked at your code, I think you should wait and see what his thoughts are. I don't see any harm in organizing your "extracted excel data" in Access in a way that lets you do what was requested. Perhaps Perceptus has some additional comments now that you have provided a little more information.

Do you have a clear understanding of what your boss wants?

[ssanfu]

So for my table pending_temp, there is no structure. I mannually extract from other linked excel sheet. Thus the records are not arranged in the way my boss wanted. So I am writing this sorting function. It consists 40 fields, beside the 5 field using in the rule, other field are like Purchase order #, Item #, Comment, customer ,etc. thery are string or double. For some record the filed can be null.

OK, This is an important question... Are you importing the Excel data into an Access table, or are you LINKING to an Excel spreadsheet?

I am starting to think you have linked to an Excel spreadsheet - that is why you are preforming a bubble sort.

[thape]

Hi, Orange
I am not familiar with Database.When I first got this task, I try to avoid normalize my data do the way I know might work with VB. But now I think I have to normalize my data in some way.
My boss want a "sorted" output order list that he will assign records in the table to different mills. The program I am writing is for scheduling. The idea is for planning different mills production base on the current orders we have.

[thape]

Hi, Steve

In my Access, I have linked Excel sheet. Then run some query to generate several table from the excelsheet. That's where I manipulate the data.


thanks

[orange]

thape,

If you are not familiar with database, how/why did you decide to use Access? I must admit that I am not following what your real task is.
Perhaps Steve is following your post and needs, but I just keep getting more confused.

As you probably know Excel sheets are rarely in normalized format. So, if using Access is an option, you could make some normalized tables and queries to get the info you want. I'm not saying you should, I'm just saying that is a possibility if I have understood any of your task.

[thape]

Orange,

I didn't decide to use Access. It was given as task using Access. Try to write a program for the scheduling department.

I will try to normalize my data.

thanks anyway

[ssanfu]

I had to finally give up on the code..... Trying to follow the execution when the data source is missing 35 fields..... well, I went quietly insane

While there is nothing wrong with using Access to sort an Excel spreadsheet, it is unusual.
I have used Access to read and write text/CSV files without putting any data into a table. At one point I was using Access to import data from an Excel spreadsheet, normalize the data, then after adding more data, write the normalized data back to Excel, effectively in un-normalized form.

I didn't decide to use Access. It was given as task using Access. Try to write a program for the scheduling department.

I will try to normalize my data.

You can run the same code (converted for Excel) in Excel. Just curious, is there a reason for using Access??

I have noticed a few errors. For instance, you have

rs1.Move (rs1Position)

"rs1Position" is declared as an Integer. But the MOVE argument should be a Long Integer.

Something else that *might* cause problems is this line:

rs3.FindFirst "[ODmm] =" & od1

You declared "od1" as a string (Dim od1 As String), but are missing the text delimiters.

rs3.FindFirst "[ODmm] ='" & od1 & "'"

I will try to normalize my data.

Normalizing the data will only have an effect if you import the Excel data into Access, then do what is required (sort/ print/ etc).


From your first post:

The problem I have: 1. Sometime , the function run into error at rs1.edit sometimes not????????
2. Any suggestion on this Bubble Sort idea..or some other way to achieve the task??????
3. Sometimes my code works, sometime not ??????

Without having the full dataset (Excel sheet), cannot make any recommendations to solve problems 1, 2 or 3.