ACCESS Program

[johnseito]

Hi all !

Just want to know from your experience !



Can access be a tool that can be use to create sophisticate programs that deals with very large files or is there limitations as to what it can do ?
I know the Access database capacity is 2 GB (unless new version is changed) and then it can't hold anymore after 2 GB.
When I say sophisticated program, I meant program that can be use to analyze and solve complex problems with records that are in the hundreds of thousands.

Because records are in the hundreds of thousands, a query statement, or many query statement running on it to produced result can slow down the machine.
This is only hundreds of thousands of records, if records reaches million it will be even more slow using Access query I believe.

Query statement are not programming code like VBA, C++ or JAVA and I believe Query statement has limitation and I feel that if Access rely heavy on just Query statements
then I believe Access is limited as well.

Do you agree ? I really don't know ACCESS's limitation is why I ask this from the experts who used ACCESS often.

[John_G]

The 2GB file size limit refers to the size of any one Access database file. Using split databases and linked files can get around that limitation by having one front-end database link to several back end data files.

If you are a good programmer and know how to utilize the capabilities of the machine Access is running on, with respect to available memory, you can write some pretty complex programs (I have) using VBA.

What do you mean by "query statement"? In Access, all queries are written in SQL - but that is true for most major database systems as far as I know. SQL statements form an integral part of VBA - in fact VBA would be of very little use without them, so to say that query statements are not programming code is really not correct (IMO).

Your question is very broad and somewhat vague, though. Whether Access is a suitable solution for any particular problem depends very much on what the requirements are. However, if you are looking at large amounts of data, then you should consider SQL-Server for the data (back end), and MS Access for the front end.

I really can't say much more without knowing more about what your particular requirements are.

HTH

John

[orange]

I agree with John_G. You questions are very general. Tell us about a proposed database/application you have in mind -- readers need context to respond to your questions.

[johnseito]

Ok here is what I mean by complicated program, and maybe in situation that are more complicated. But this is
one example:

I have a table, it can be for example 700k or 900k lines of records, and other times less, as less as 20k.
In this table, I care mostly about two fields for calculation purpose, one is ID, the other is events.

The user wants to know with a built in program (in Access) that can calculate the given table, how many events
are assigned to each ID, and their breakdown of each events assigned. In additional in TOTAL of all events assigned to each ID and to all ID.
(a pivot table in excel would know this) I would image that with an SQL query this can be very time consuming process and would have taken
a toll on memory and CPU to calculate.

The program in addition would also allow users to enter a percent of how many events they want to see. So if a user enters 21.43% (regardless of % enter it should work),
the program should pick out 21.43% from the table file (so if the table has 874,577 records,
874,577 * 21.43% = 187421.9 rounding up, the resultant output should be 187,421 records). The resultant output should make sure that
certain criteria are met. The criteria is that all ID and every event are shown in the resultant output. In additional to all ID, and every event that are shown,
the additional rule is that if a ID have more event assigned versus less for other event, that ID and that event should be proportional be consider more in the resultant output.

The Table has not reach over a million but the user wants a program that is flexible with just in case the table can have more than 1 million records.
Right now I am doing this is excel and for a 700k to 900k records (and depending on the given data at that time), I think it takes 3 to 4 mins to calculate and come out with a output to the user.

I used VBA to do this, I don't think SQL query without VBA can do this. I was just wondering how we could do this in ACCESS, and how we could loop through the
records, tables, columns and rows in ACCESS and using if then, else statement as well as other programming ability to make decisions. Could ACCESS do this more efficiently ?

Attached is an example of the ID and events, A, B, C are IDs, and 1a, 2b, 3a are events.

Thanks !!!

[June7]

The image shows data in non-normalized structure.

Access 2010 can do pivot tables (functionality partially removed with 2013). Also CROSSTAB query can pivot data. I don't know how fast Access would perform these operations with over million records. I've never worked with db that large. A lot depends on your network and server. The TOP N PERCENT qualifier can restrict dataset. However, if you want random record selection, that is trickier.

What do you mean by 'loop through'? IIf() function expression can be used in query to evaluate data on each record and return a value based on condition.

[John_G]

Hi -

Does your diagram shows the output, i.e. what you are trying to achieve (it certainly looks like that)? If so, and it is a summary based on up to a million records, then Access should be able to handle that. That diagram appears to be based on only a very small sample of your data, as the number at the lower right (87457) is relatively small.

What do the input (source) records look like? That is VERY important in determining how complex your task will be.

Can you provide more details, please>

John

[johnseito]

I don't know how fast Access would perform these operations with over million records.

So far I haven't seen 1 million or more than a million records. I only see 600k to 700k, but I was thinking that if we could produce a program that can handle over a million records
as a precaution that would be more safer as sometimes you never know how many records you can have or end up having. If is over 1 million records, Excel can't handle it as well
since excel is limited to just about 1 million records.

The TOP N PERCENT qualifier can restrict dataset.

The top N percent however I don't believe can be applied those rules and conditions ?

What do you mean by 'loop through'? IIf() function expression can be used in query to evaluate data on each record and return a value based on condition.

In Excel VBA, you could loop through rows and columns, example below, I was wondering can Access do that ?
I feel the IIF() function in Query are limited, maybe I am wrong !! :-)

Do 

     If cells(x,1) = this  do this
    x = x +1 
Loop until cells(x, 1) = "StOP"

[johnseito]

Does your diagram shows the output, i.e. what you are trying to achieve (it certainly looks like that)?

no, my diagram doesn't show the output, is just an visual example diagram of the kind of datas we are working with.

so rows such as A, B, C, D represent IDs, and column such as 1a, 2b, 3b are events.

the program is suppose to allow users to enter a % and the program would pick out a number, % of the total population but making

sure all the rules, conditions, and criteria's are met. The program is suppose to pick randomly, and the rules are that every ID, and Events

are in the resultant population, and that if ID have more event's more of that ID and event's will be in the output.

If so, and it is a summary based on up to a million records, then Access should be able to handle that.

Is not just a summary of the numbers, but have the program be able to pick out the data, a certain % of total volume and making sure the rules, criteria, and conditions are met.

That diagram appears to be based on only a very small sample of your data, as the number at the lower right (87457) is relatively small.

yes is small, because this is just a example and that number can change, either bigger, a lot bigger or a lot smaller. So your program is suppose to be flexible
in able to handle different volumes of data.

What do the input (source) records look like? That is VERY important in determining how complex your task will be.

the input source will be a table, with rows and columns, but the only two column that are very critical are the ID and Event column that determine the selection condition,
and criteria and rules that should be met.

[johnseito]

Hi June7,

I am not sure what you mean by the bottom quote. :-)

The image shows data in non-normalized structure.

Is just a pivot table from Excel of the kind of records that we are dealing with.

So for example, ID A has a total of 110 events,

57 from event 7a,
2 from event 9a,
32 from event 17a,
9 from event 19a,
4 from event 21a,
6 from event 25a.

The rule, condition and criteria of the program after a user enter a % for example 21.43% is that the program should pick out 21.43% of the total population making sure all ID, and events are in the output,
and that if a ID has more events, more ID and events should be taken as well. It should relatively be weighted.

[johnseito]

John,

as the number at the lower right (87457) is relatively small.

Regarding your quote, actually if you create a program that works for 100 records (given the same rules, condition, and criteria), that doesn't mean it will work for
100,000 or 1000, or 24562.

There are chances where your program may just work for the given data and number of data, however when you change the input data with a change in
volume your program depending on if you tested it for it to work for the other volumes may end up crashing. Maybe you disagree with me, but
I have witness it crashed when I created program that works for some input but not others, so I have to make adjustment and test it with the different inputs.

:-)

[John_G]

the input source will be a table, with rows and columns, but the only two column that are very critical are the ID and Event column ....

I'm very confused about your use of singular and plural -

What fields are in each row of your source data? Is it one ID and one event column (plus other fields) in each row, or does one row contain an ID and multiple events? As I said, that is important in determining the complexity of your task.

John

[johnseito]

More like this !! :-) Thanks !!!

Is it one ID and one event column (plus other fields) in each row

[John_G]

The rule, condition and criteria of the program after a user enter a % for example 21.43% is that the program should pick out 21.43% of the total population making sure all ID are in the out, all events are in the output,
and that if a ID has more events, more ID and events should be taken as well. It should relatively be weighted.

In my opinion, you are looking at an extremely difficult if not impossible task, and it wouldn't matter what program you are using.

- You want to select (using your example) 21.43% of the records, and you said randomly. OK, you then check that the criteria (all ID's and all events) are met - what happens if the criteria are not met?

- The "weighting" concept is much more difficult; how can you determine what the weighting factors are without analyzing the entire data set first? Using the random selection to determine the weighting factors is not valid (IMO) And then what do you do with those weighting factors, i.e. how do you apply them to the selection process?

Do you see what I am getting at here? You have to be able to answer these questions in some detail, and establish the resulting algorithms, before you can even begin to determine if Access is the appropriate tool to use. (My guess is that it will work, but it will require a pretty good programmer who can use some VBA programming tricks to speed things up.)

So, take some time to analyze your selection and weighting processes (in other words, if you had to do it by hand, what exactly would you do?), and then see how well Access/VBA might do it.

John

[John_G]

Hi -

Referring to your post #12 - ("more like this!")

That makes life a whole lot simpler! A million rows sounds like a lot, but isn't really in the overall scheme of things. One crosstab query could generate your diagram as above for the whole dataset. (Not as pretty of course, and how long it would take is another issue entirely).

But with one ID - Event per row, it's easy to come up with counts for various analyses and decisions.

John

[johnseito]

what happens if the criteria are not met?

Then is a program that you can't trust to say it will give you an accurate data set.

are without analyzing the entire data set first?

Yes you are correct, you have to analyze the data set first and know the counts of each ID with it's event/s.

Using the random selection to determine the weighting factors is not valid (IMO)

What is IMO ?

And then what do you do with those weighting factors, i.e. how do you apply them to the selection process?

so for example if ID A has 5 events weighted accordingly as:

1a = 100 1b = 234 1c = 34 1d = 341 1e = 23

total is 100 + 234 + 34 + 342 + 23 = 733
733 * 21.43% = 157 to select from
so you have to pick each of the events, 5 of them in total to reach 157.

100* 21.43 = 21 taking 21 from this event
234*21.43 = 50 from this event
34*21.43 = 7 from this event
342 * 21.43 = 73 from this event
23*21.43 = 5 from this event

Total event taken are 156, which is lesser than the required 157, so you add one to which ever event you want
to make it equal to 157.

But you have to do this to all the ID's and ID's associated events. So with the example of 87457 and user enter 21.43%
you have to select 18742 items, make sure all ID and Events in the pool as well as is random.

But keep in mind that this total number in a table can be higher and lower, as high as 800k and as lower as 20k.

So you think this could be done in Access and done efficiently like with 800k in less than 5 minutes?
I know that Query by itself wouldn't be able to do it as it will take a lot of time to process.