Impact Of # Of Fields/Records On DB Size
Mar 27, 2006
Not sure if this is a Table question, a General question or what exactly. I'm working with a lot of records (millions of them). Assuming each record/field contains identical data, I'm wondering which would result in a larger overall DB size:
- 10 million records, 4 fields each
- 30 million records, 2 fields each
What I have is a table something like this:
Record No./Attrib1/Attrib2/Attrib3 (~10 million records).
I could make it Record No./Attrib, which would have roughly 3 times the number of records. I say roughly because not all Attrib have values (currently zero filled but would be eliminated).
I'm hitting the maximum size for an MS 97 database (1 GB), so I'm looking to do whatever possible to stay small.
Any input/suggestions would be appreciated.
Thanks!
View Replies
ADVERTISEMENT
Sep 28, 2006
I just discovered that field lengths don't influence table size because Access doesn't pad fields.
A text field with length 20 or 255 doesn't impact the size of the table, just the data in the field does.
I did a test to confirm this.
This blew me away.
So now I ask why do we specify field length for text fields?
View 1 Replies
View Related
Nov 18, 2013
Basically in my order details table i have the following fields
Product
Unit
Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
View 4 Replies
View Related
Sep 8, 2014
I have an issue sorting the results on my Access 2010 Report. Here are the details:
- Report Record Source = "Portfolio Ranking"
- Portfolio Ranking is the name of a query
- The query includes a calculated field called "Impact Score"
- FYI: this is a web database (not sure if that limits what I can do)
I want to sort the report on the Impact Score column. However, when I view the query calculation for Impact Score, the "Sort" field says "(not sorted)" and it's greyed out, so I can't select Descending as desired.
View 3 Replies
View Related
Dec 11, 2007
I've asked this question in different forums before, and though some people say it can be done, I have not found anyone who is successful yet.
I have a report that puts down a person's address, however the address information is stored within the database in separate fields ('Address', 'City', 'State', 'ZIP'). The issue I have is with the City/State/ZIP. Some city names are longer than others, is it possible to have the fields adapt to the different word lengths.
Say the address is, Albany, NY 10023. The database would output this alright, but then if the city name is San Francisco, CA 94143, the fields are in disarray.
I have the same issue with names. Last name and first name are stored separately. I would like to display Last, First. My workaround has been to right-align the Last name, and put it adjacent to the left-aligned first name field. This works unless the person's last name is very long, disporportionately from the first name, in which case, it looks very much off center in the overall report.
Any help would be appreciated.
View 5 Replies
View Related
Dec 11, 2014
I have a datasheet on a form that is unable to show all records in the size allowed. Consequently it has a vertical scroll bar. However, the scroll bar allows scrolling to the bottom whereby there is white space almost the same size as the data records. The horizontal scroll bar does much the same.
Is it possible to just show the records and only the width of the data in them.? No extra records exist in the table to cause this.
View 2 Replies
View Related
Jun 29, 2014
I have an Access 2010 database .. and will be adding about 25,000 records a day to it. So it will get pretty big fast .. so 9 million a year. Is this too big for Access ?? Or is it more a issue of file size .. I know that Access 2010 can go up to 2 GB. I am interested in how I can maximize my performance and not have the database.The data is as follows .. One table. The other tables will be small, just this one will increase in size really.
Id .. Autonumber (primary key)
When .. datetime
Radio .. integer (indexed .. w duplicates)
Group .. integer (indexed .. w duplicates)
Type .. text 4
TransType .. text 2
I am considering a purge of old records if needed .. like only keeping the most previous 5 million records or some reasonable number. Like .. then I would think I should try to number my records backwards (using long int rather than autonumber and number records backwards on import) .. ie record 1 would be the most recent one and only keep the last X number of them for performance reasons.
View 8 Replies
View Related
Aug 29, 2005
Will keeping your field size shorter result in a smaller MDB file?
Or does Access only use as much space as there is real data in its fields.
Way back in the dBASE III days, dBASE would pad all your "real" information with as many spaces as necessary to fill up your field. I suspect that the MDB structure is probably smarter than that.
Another question on the same topic - I believe there is a maximum number of characters in a record (4000?). Can your field sizes add up to more than 4000, as long as the actual data, all combined, never totals 4000...?
Thanks............
..dc
View 3 Replies
View Related
Jun 15, 2013
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
View 1 Replies
View Related
Nov 1, 2005
Access user,
:confused:
I am looking how to generate a relation in one database between records of the same field type.
f.i. you have a database where you try to keep track of your parts.
You order a part1 but they deliver an identical part but from another brand so it has another part number.
You will store it under his correct partnumber being part2.
Due to the fact you still have stock of part1 and just recieved part2, now you will run out of stock when part1 + part2 reach zero stock.
In order to be able to monitor this you must be able to generate a relation between part1 and part2 showing that that parts are fully interchangeble.
How can you realise this in access?? Or is this impossible?
Also would it be possible to have for a certain part number multiple relations?
f.i. parts are fully interchangeble
restricted interchangeable
one way interchangeable
Thanks in advance
View 1 Replies
View Related
Aug 11, 2015
where each number corresponds to the primary key in another table, each number being a company name (not really relevant). What I want to be able to do is:
1. Produce a query table result with the Total Project Value (sum of all values) each company (305-9) is involved in. For example, in Project A and C, 308 are not involved, so the 308's Total Project value would be B+D+E+F Valuation.
2. Produce a query table result with the Average Project value. Similar to above, but taking the total and dividing it by the number of projects they were involved in. For example, 308 had a total of 259 over 4 projects, so an average of 64.75
3. Produce a query table result where it takes the most common role for each company ID. For example, Company 308 was Civil 3 times and Structural 1 time, giving 308 with Civil as their most common role.
View 4 Replies
View Related
Jul 13, 2005
We use two different systems in our office that have notebook facilities. One of them stores only a limited number of characters in the note field per record and as such, some notes actually span several records.
The other system stores the whole note in one record.
We want to transfer all the notes from the first system (1 note over several records) to the second system (1 whole note in 1 record).
I have a table with the notes from the first sytem which looks something like
Note1 - Line1 - Text
Note1 - Line2 - Text
Note2 - Line1 - Text
Note3 - Line1 - Text
Note3 - Line2 - Text
Note3 - Line3 - Text
What I want to do is, for each note, combine the separate lines into one record.
I have been able to do this in Excel with some formulas (see attachment) but am wondering if it is possible to do this in an Access Query (i.e. GroupBy the note number and have a calculated field combining the individual lines of the note, or something similar)?
View 8 Replies
View Related
May 30, 2007
i'm relatively advanced Access user but this problem has got me stumped....i've been given a table that's basically a log of operating rooms with fields designating the date, the room number, "patient in room" time and "patient out of room" time. basically, i want to calculate the turnaround time which is the "patient in room" time" minus the "patient out of room" time from the previous record (if records are sorted in sequential order).
any thoughts?
View 10 Replies
View Related
Mar 11, 2008
Another problem has thrust its ugly head into my personal universe.
I have a report to make that is to show a break down of employees hired in each month since Oct. '05 and to show what percentage of the total for that month remain.
I have created one query that grabs each of the relevant personnel;
SELECT Right(Str(Year([dbo_personnel]![originalapptdate])),4)+" -"+Str(Month([dbo_personnel]![originalapptdate]))+" : "+MonthName(Month([dbo_personnel]![originalapptdate]),True)+" '"+Right(Str(Year([dbo_personnel]![originalapptdate])),2)+" Hires" AS hire_month, [dbo_personnel]![lname]+"; "+[dbo_personnel]![fname]+IIf(IsNull([dbo_personnel]![mname]),""," "+Left([dbo_personnel]![mname],1)+".") AS name, dbo_personnel.badge, dbo_personnel.originalapptdate, IIf(IsNull([dbo_personnel]![dateoftermination]),[dbo_personnel]![assignedorg],"EOS'd") AS EOS
FROM dbo_personnel
WHERE ((Not ((dbo_personnel.badge) Like "R*" Or (dbo_personnel.badge) Like "9*")) AND ((dbo_personnel.originalapptdate)>=#10/1/2005#))
ORDER BY dbo_personnel.originalapptdate;
I've saved this query as "2-yr Service Check Baseline."
And then this query that does the count;
SELECT [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active") AS EOS, Count([2-yr Service Check Baseline].EOS) AS CountOfEOS
FROM [2-yr Service Check Baseline]
GROUP BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active")
ORDER BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active");
The output from the second query stores the Active count on odd numbered records and the terminated ones on the even numbered records.
So, to get the percent left, I'd need to sum the two count fields from the two relevant records and then divide this into the Active count.
But, in the report, how do I reference a field from a record other than the current one?
Thanx in advance for any help!
View 3 Replies
View Related
Jun 16, 2006
Newbie Need help !!!!!!
downloaded a template from microsoft for contacts played around with the form to suit myself...
the problem i have is when i enter any thing in the text box this is cloned to all records this is happening with all text fields im not a programmer so plz go easy!!!!!!
thank you
View 1 Replies
View Related
Jun 16, 2006
Newbie Need help !!!!!!
downloaded a template from microsoft for contacts played around with the form to suit myself...
the problem i have is when i enter any thing in the text box this is cloned to all records this is happening with all text fields im not a programmer so plz go easy!!!!!!
thank you
View 1 Replies
View Related
Dec 12, 2006
I have a database and I wish to be able to copy 10 out of about 20 fields into a new record automatically using a command button on a form. Similar to the way the Duplicate button or the Create New button would work but only with these selected fields. None of the fields I wish to duplicate are the primary key. The primary key is an AutoNumber field. After clicking the Save button, the primary key would increment one to confirm the save record.
Any help or suggestion would be appreciated!
Thanks!
View 3 Replies
View Related
Jun 8, 2005
I'm working on a db logging replies to a questionnaire. Judging by the responses I am receiving it appears that some sections are not completed at all. I need to be able to include these blank responses when it comes to analysis.
My design splits the questionnaire into sections, each section has a data entry form with its own underlying table. Each table has a primary key (autonumber field) which relates to each organisation that has replied.
If an organisation has failed to complete a section, I still need to create a new record in that section(table), triggering the autonumber field, hence referring back to the organisation.
I know I haven't explained this very well, but if anyone can make sense of what I'm saying and can give me any suggestions on how to make this happen, I would be most grateful.
In anticipation of your replies
Ride
View 4 Replies
View Related
Jun 19, 2015
In my table, I got text fields and numeric fields, however, sometimes, user put an empty field in some records which I disliked. I try to put some checking such as following syntax to check those empty field but nothing happened.
1) If len(NAME) = 0 THEN error message : for TEXT field checking
2) IF len(name) = NULL then error MESSAGE : for TEXT field CHECKING
3) IF NAME = "" then error message : for text field checking
4) IF LEN(AMT) = 0 THEN error message : FOR NUMERIC field checking
5) IF LEN(AMT) = NULL then ERROR MESSAGE : for numeric field checking
6) IF AMT = "" THEN error message : for numeric field checking
7) IF AMT = NULL then ERROR message : for numeric field checking
View 1 Replies
View Related
Dec 18, 2013
It have several fields, and the fields that I want to filter is ItemNo and SellingPrice.The problem is ItemNo has many different SellingPrice. I put a parameter in SellingPrice field as >=75 only for ItemNo "49565", but still i want to show other record in ItemNo and do not have filter in SellingPrice field.
View 3 Replies
View Related
Jul 18, 2013
I have a table called tblAuthorizations. It appears as follows
AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits
1374006036.........356679..........20255102....... ...4/22/13......7/21/13.....0..............416
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520
504564357...........469432..........20254788...... ....4/22/13......7/21/13....0..............520
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520
I work in mental health, and when we put in a request for a consumer to receive PSR & BST services, we put the above information into our database. Each record identifies which consumer the authorization is for, a start date and end date for them to receive services, and the amount of two types of services (PSR and BST). When one period ends, we request another period, such as in the first three records above, and then the final 2 for another consumer.
What I need to be able to tell is how their service authorizations change. For instance, if I were to compare the first and second records, it would tell me that PSRunits decreased 4/22/13-7/21/13 from 1/21/13-4/21/13 levels.
I have a query that displays the records for each consumer for the current period, but I can't figure out how to identify the previous period's number of PSRUnits or BSTUnits.I've tried adding the following fields, but this isn't working.
PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])
View 2 Replies
View Related
May 20, 2015
I currently have a query that uses a function to get the current users email address. This is matched with a "Teacher Email" address in a table of students to only show students which match the logged in teacher. I'd like to modify this so the teacher can see any student in their department. Is there a way to do this without creating a separate "Teachers" table that would contain a department and email?
View 5 Replies
View Related
Dec 10, 2007
I currently have a database that is missing various pieces of information, we are getting a temp in to complete this. I don't want the temp adding data directly into Access so I have exported the table that needs updating into Excel.
How can I then import the new data without altering current data.
I know this is no problem for new records, but if I have a current record with a few fields of information missing how can I just import information into the blank fields. This can't be a manual process as there are hundreds of records.
Any comments would be much appreciated!
Chris
View 1 Replies
View Related
Aug 24, 2005
Hi,
I have a query which finds the averages for 10 diferent fields grouped on a different field.
I would like to produce a chart comparing these 10 vaules in a bar chart, 1 chart for each value in the grouped by field.
To do this I think I need to have the fields as rows, with the grouped field values as fields.
Does anyone know how to do this without writing a module from sctrach, or can I produce the chart I want from the data in the format I already have it in.
Thanks in anticipation
Sue
View 3 Replies
View Related
Sep 22, 2007
Hi
I have a single form for which I need to create 2 records from in the same table.
On the second record when created I need to change some of data contained in the fields - How do I do this:
I give an example. On my form I have a tick box - if true it enables 2 text boxes and will need to create 2 records within a single table.
I need the first record to include the data contained in the first text box and the second record to contain the data from the second text box.
I have tried to use update queries but have had no luck. Anybody have any ideas?
thanks
View 6 Replies
View Related
Sep 23, 2007
I have this basic question (obviously not so basic for me): how to pull out only unique records from two fields.
More details - two fields with names of competitors in a tournament (Winner or Loser) and i need to build a query to have all players names participating in the tournament regardless if they have won or lost in one list.
View 4 Replies
View Related
