Query Question- Number Size
Apr 18, 2007I code a field that shows different codes. Most of the codes are 5 digits but there are some in there that are 4 digits. What would I type in my query to just show the fields that have 5 digits.
View Replies
ADVERTISEMENT
Large Field Size = Larger Overall Database Size?
Aug 29, 2005Will 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
Getting DB Size Error When Running Query
Feb 12, 2013I am modifying some queries in a DB which is only 411,000 KB. It still has plenty of room left before reaching 2GB. When I go through and test the queries, some run fine with no issues.Then I get to one of the last queries and get this error: "The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage on the disk to store the query itself."
I still have about 1.5 GB of RAM available when this query is running, so it doesn't appear to be a RAM issue, and the DB still has plenty of room left to store the result set of this query.
MDE File Size Growing And Query Rewritting Itself
Aug 29, 2005I have a strange issue. First off I have a front end in Access 2000 as a MDE file linked to a mdb data file.
Issue number 1: The MDE file almost doubles in size daily. Yes, the mde file not mdb (it grows slowly and expectedly as data is added and deleted) . I expected the MDE file to stay a static size. Is this normal, what causes this?
Issue number 2: I had on 3 occasions a query in the mde file suddenly pop up with a ”cannot repeat table name ‘Nameoftable’ in FROM clause”. I go into the mde file, SQL query view and delete the extra table name and all is fine for awhile. After the second time I did away with that quey and rewrote the query for my form a different way. It’s come back once again now for anther table. Has anyone heard of Access doing this?
I also note that the modified date of the file keeps changing. I am wondering if someone is tampering. My client that has this software is very naïve.
The MS site has no information regarding either behaviour.
Any ideas?
Jim
Apend Query Problem Text Size
May 25, 2006Hello - the world
this the 2nd time i tried this and hope someone can help
I have a form that i need to insert text into the text is held in a table and the amount of text can vary from 30characaters to 1,000's
now i have a drop down box with the following
SELECT [Wording].[wordingid], [Wording].[wordingnme], [Wording].[wording] FROM Wording;
i select one and then while that item is highlighted , i push a button append and it inserts in to my form
The table settings on the form are memo and the table settings on the wording are memo , i can see the info in the raw table its the append bit
so wording - fishing would be wordingnme and the text I want to apply would be "gone fishing etc "
works a treat but I only get the first 255 chars of a long wording
I can insert as many wordings into my form but size limt of 255 so its not the form
any ideas ?????:confused:
Query That Result Incremental Number, With No Auto-Number
Sep 10, 2006Well, here's my database :
Name : String
Address : String
I want to add an automatically generated column, "no.", to show which number it's in. Like this :
No. Name Address
1. A Unknown
2. B Unknown2
The problem is, I can do that with Auto-Number, but if the user delete row 1, the number in row 2 is still 2, not 1.
Is it possible to generate query that have "no." column in it ?
Modules & VBA :: Sequence Number In Query Use A Starting Number
Sep 20, 2014creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5
---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------
The Higher The Database Size, The Slower The Query Process?
Jul 10, 2007I am developing a database and testing it with dummy data. With 50,000 records, the query process using SQL command is just OK. But if I double the total records, the query process takes longer time. The query is to make recapitulation using SUM of SQL command based on certain criteria. Any idea to make the query faster?
Thanks.
Database File Size Getting Larger From Search Query
Apr 4, 2008I'm having a problem with my database filesize getting larger with the more searches i do. At first i couldn't figure out how it was getting so big, but then i tried a few things and came to the conclusion that every time i do a search it increases its filezile.what made me notice this at first is that the file size of the original copy of my db is 13MB, but if i Save As a different name, like Assets_Backup, then the size of the new file is only 2MB, and after i do some searches it increases again.here is a file with just my search form and search query so you can see the code for them. i removed everything else to shrink the file down, which also made the search form not function properly, but i thought the design and code might be enough.is there something that i can change to keep it from remembering every search?thanks for any help.--------------------after searching the topic a bit more i've found that compacting the database will shrink it down to 2MB, but is this something that will have to be done on a regular basis, or is there a way for it to do it on its own?
View 5 Replies View RelatedDatabase Size Limited To 2GB / Query Multiple Database Without Linking Tables?
Sep 7, 2011I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
Queries :: How To Insert A Prompt For Number In A Query To Calculate Against Another Field In Query
Jul 15, 2014I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedNumber Query
May 13, 2005Hi All,
I am trying to perform a query on two tables, with the result being a Dept Name & Dept Number. One table consists of an Index Number & Dept Name, the other table has Dept Number. The index Numbers & Names 14 thru 37 in first table correspond to Dept Numbers 1 thru 24 in second table.
Sorry if this is an easy one..
Thanks in advance :)
Kappa
Number Of Records In A Query
Dec 20, 2005Hi.
I have a form (frmdetails) to store job details and another form (frmMaterials) to add materials to each job. They are both linked via a field Project_No
I have a query (QryTotal)that when run will list the materials used for each project, that is all OK.
What I am tryng to do, is add a text box to frmdetails, that will just tell me how many materials have been booked against each job. Just a running total that will tell me that for project number 100000, 4 items have been booked.
I have tried using subforms (using =count(*) ) but this doesn't seem to be working. I also added =Count(QryTotal!Material) to a text box but this doesn't work.
Can someone help?
Thanks.
Frank.
Number Results In A Query
Nov 22, 2006I would like to create a seperate field for each record in my query which would number the results of the query in ascending order, say from 1 to 100.
Is it possible to do this in a query?
Number Of Days Query Help
Mar 6, 2008I imported an large Excel file with a date column consisting of 3 different date formats and need help on a query to extract the number of days. Examples of the date on that column:
7/6/2006
7/1/06-7/31/06
7/1-7/5
Appreciate any advise.
Query For Even/Odd Number In ACCESS
Nov 10, 2004Can anyone help me which I'm trying to query a field called address number that contain only number for an address. I want to query out all the even and odd number from this field. The result should be two separate report of all the even number with street address and odd numbe with street address.
Thanks.
Number Of Fields In A Query
Aug 27, 2015I have a question regarding the number of fields in a query. I have a query that is looking at production scrap by categories. There are fifteen categories of type of scrap and in addition to these I am using the query to work out the total % of total scrap for each category. My problem is there are not enough fields to do all the calculations. Is there a way of adding more or is there a better way of doing what I am trying to achieve?
View 10 Replies View RelatedFixed Number Of Records In A Query
Apr 4, 2007Hey all, i just would like to know if there is a way to have a fixed number of records in a query.
Let's say, example (a), a query returns 2 records (qry1); i would like to have my query (qry2) to have 5 records, so the first two records will appear and then 3 blank records. (b) A query (qry1) returns 4 records, then this query(qry2) should return the 4 records plus a blank record
.........qry1.......qry2
a)|>record1....|>record1
...|>record2....|>record2
...|*______....|>______
....................|>______
....................|>______
....................|*______
.........qry1.......qry2
b)|>record1....|>record1
...|>record2....|>record2
...|>record3....|>record3
...|>record4....|>record4
...|*______....|>______
....................|*______
Why do i want to do this?
Because i want my report to show 5 records so when i print it i have those blank spaces to fill if necessary.
I want to have this:
______
lrecord1l
lrecord2l
l______l
l______l
l______l
Instead of:
______
lrecord1l
lrecord2l
What im doing now is creating a temporary table and add the records to it plus blank records.
What i used to do was add blank records in the same table meting the criteria of the report, so they will appear. After close the report, those records would be deleted.
I would like to know if theres another way, like a command in sql, or maybe some property on the report. What ever is most efficient and a good database practice. Thanks in advance
Query To Extract Greatest Number
Aug 14, 2007I have a table that keeps track of how many times an account is listed. I want to write a query that will only return the greatest number of occurences for that account. For example if acct 12345 has 3 occurences in the database I only want to return the information that is associated with the #3 occurence. As stated there is a field that calculates the number of occurences for each account so in this case there would be a record for 1, 2, and 3 occurence so I only want to see #3. Hope that makes sense. Thanks!
View 3 Replies View RelatedCan You Query By Week Number Rather Than Between Dates
May 17, 2005Is it possible to query using the week number in the year as criteria. For example instead of putting 13/05/05 to 20/05/05 can we use week 12 - the idea is to save on keying info in each time.
View 5 Replies View RelatedOrder 2 Different Fields In Query By Number
Sep 2, 2005i have a query which looks at 2 fields: "employee number" & "agency number", i would like the query to list the employee numbers in order first (1,2,3...) then the agency numbers in order (100, 101, 102...). ive changed the ascending/ descending order for them both but doesnt help. Its mainly the form that looks at this query i would like to fix so that is shows all employee records in order of employee number first and then underneath all agency numbers in order of their number (some employee numbers & agency numbers are null) hope this makes sense , any help would be great
View 7 Replies View RelatedQuery - Retrieve A Set Number Of Records
Nov 4, 2005Hi all,
I want to retrieve a set number of records from a table, using a query. I'm not sure of how to do this.
I can't use the ID, because the table gets its data replaced contantly by imports (so the ID doesn;t start at 1...)
I want to be able to retrieve, say, the first 25 records on that table.
Any help is appreciated
Auto Number Results Of A Query
Nov 7, 2005Hi,
Ive done a search for this and couldnt find any suitable answers. Is it possible to add a function to a query that auto numbers each row returned (so if your query has 10 results, it numbers the lrows 1 to 10)??
Thanks
Greg.
Query With Varying Number Of Parameters
May 19, 2006Greetings,
I am trying to write a parameter query ... I have multiple fields the users *could* search, but they may want to search only one, and I am having a struggle figuring this out.
Ex. fields to search are Gender, Age, Date, etc., and I have created a parameter query that includes parameters for each of these fields. However, if I want to see all the females, thus leaving the other two parameters blank, I have no records in my result set. Clearly, I am doing something wrong -- I have searched the forum extensively before posting.
Thanks in advance, I am sure I am being simple and missing somethign....
Why Is My Query Displaying A Number Not Word
Sep 12, 2006Here is my relationships and a copy of my SQL for this query. Why is it displaying the PK number (RaceClass) instead of the word.
My table for RaceClass has the number, the query has the number but my form has the word.
Many thanks for any help.
Tee.
Adding Sequence Number To A Query
Apr 3, 2007I have a query that captures date values from a table based on a between function that works fine. What I would like to do is associate a sequence number fro each date value returned and either perform a make table or leave it as a select query. If I create a table I know that I aam able to add an autonumber field but the query is executed frequently and performing the addition of the autonumber field is not feesible. Any thoughts how I can generate the sequence number each time the query is executed?
Thanks