To know basics of access (1 Viewer)

Shiv

Registered User.
Local time
Tomorrow, 04:08
Joined
Dec 23, 2018
Messages
16
1.Is access compatible to work on more than 1 crore rows if size is between 10 to 15 GB.

2. Please suggest how to use D lookup for complete field in a query
 

isladogs

MVP / VIP
Local time
Today, 22:38
Joined
Jan 14, 2017
Messages
18,186
I moved this post from Sample Databases area as that isn't intended for questions.
It is also a moderated area so that's why your post didn't appear immediately.

1. The maximum size for an Access db =2GB.
I believe that 'crore' means 10 million.
Access will struggle with a table that large even if the db is less than 2GB.
Suggest the data is stored in SQL Server

2. See http://allenbrowne.com/casu-07.html
 
Last edited:

Shiv

Registered User.
Local time
Tomorrow, 04:08
Joined
Dec 23, 2018
Messages
16
I moved this post from Sample Databases area as that isn't intended for questions.
It is also a moderated area so that's why your post didn't appear immediately.

1. The maximum size for an Access db =2GB. Unsure what 'crore' means.


Sir 2 GB is limit for each access workbook or for all access workbooks.

please share a access workbook in which D lookup has used in query expression.

Actually i do not know how criteria to be defined for all values of a field
 

isladogs

MVP / VIP
Local time
Today, 22:38
Joined
Jan 14, 2017
Messages
18,186
I updated my first reply after you quoted from it.

The link I provided has examples of the use of DLookup.
Using it in a query can get very slow if you have a lot of records.

Access is a database. Excel has workbooks.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:38
Joined
Feb 28, 2001
Messages
27,001
Shiv - it is theoretically possible for Access to manage databases individually up to 2 GB, though if one ever approaches that size, it becomes VERY difficult to use efficiently.

As Colin has pointed out, if you have an aggregate of about 15 Gbytes, you are looking at not less than 8 contributing files and I would recommend not less than 10 to spread them out a bit more. However, if you have a single table that exceeds 2 GBytes in size (e.g. 200 bytes per record x 10,000,000 records) then that table plus any overhead required for it simply will not fit.

At this point, based on your initial statement, you can use Access as a "front-end" (interface) but the raw data (back-end) will PROBABLY need to be on some flavor of server, like SQL Server, ORACLE Server, or a couple of other common possibilities.

As to DLookup for something with 10,000,000 rows, you should know that EACH CALL to a DLookup does what it does by building, executing, and analyzing the results of a query. If you used VBA in a loop, each DLookup would have to search for a record among 10 million rows, and the expectation value (a statistical term) would be an average of 5 million rows visited PER QUERY. But then if you had to do something like that even once per existing row, you would be dealing with 50 million million (= 50,000,000,000,000) touches to the database. Even with indexing, 50 trillion of ANYTHING is a lot.

i do not know how criteria to be defined for all values of a field

I understand. It can be daunting. But I need some clarification because I don't know exactly what you asked. Did you mean (a) You don't understand the fields that are the three arguments of a DLookup? or (b) you have some problem fields in your data rows for which you are not sure how to build the required selection criteria? or (c) something else.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:38
Joined
Jul 9, 2003
Messages
16,245
I'd look at the actual data. Often databases evolve, in other words they are seldom designed for maximum efficiency.

This would show up as many entries in the tables consisting of unnecessary text.

For example you might have an address table with the county and/or state often repeated as a text entry.

By finding the occurrences of regularly repeating text entries and replacing the text with a key value to the text stored in a lookup table, you can make a significant reduction in the database size.

Sent from my SM-G925F using Tapatalk
 

Shiv

Registered User.
Local time
Tomorrow, 04:08
Joined
Dec 23, 2018
Messages
16
I moved this post from Sample Databases area as that isn't intended for questions.
It is also a moderated area so that's why your post didn't appear immediately.

1. The maximum size for an Access db =2GB. Unsure what 'crore' means.


Sir 2 GB is limit for each access workbook or for all access workbooks.

please share a access workbook in which D lookup has used in query expression.

Actually i do not know how criteria to be defined for all values of a field


In the attached database, i want to fetch auto and tractor txns along with with previous and next row where Auto/tractor were carrying more than 10000 and next row from auto/tractor is not having auto or tractor in the name field. Sample sheet is attached for your reference please.

Result of my requirement is shown in the result table.
 

Attachments

  • sample.accdb
    872 KB · Views: 147

isladogs

MVP / VIP
Local time
Today, 22:38
Joined
Jan 14, 2017
Messages
18,186
One possible solution attached using 2 queries:

1. qryAutoTractor - filter for Auto/Tractor only
Code:
SELECT ID, Name, Weight
FROM [Data table]
WHERE Name="Auto" Or Name="tractor";

2. qryFinal - cartesian join of above query with data table
Code:
SELECT DISTINCT [Data table].ID, [Data table].Name, [Data table].Weight
FROM [Data table], qryAutotractor
WHERE ((([Data table].ID)=([qryAutotractor].[ID]+1) Or ([Data table].ID)=([qryAutotractor].[ID]-1)) AND ((([Data table].[Name])="Auto" Or ([Data table].[Name])="Tractor")=False) AND ((qryAutotractor.Weight)>10000)) OR ((([Data table].ID)=[qryAutotractor].[ID]));

The second query is fairly messy due to the various criteria you specified
I've tested this with your supplied data & where the Auto/Tractor weight is changed to <10000

NOTE:
a) I could have got the same results more easily but they wouldn't have worked correctly with a full dataset involving vehicles <10000

b) If, as originally suggested, you have a very large dataset, this could be slow to run - but so could any alternative solution

c) No DLookup involved!
 

Attachments

  • sample_isladogs.accdb
    472 KB · Views: 147
Last edited:

Users who are viewing this thread

Top Bottom