I have a table containing items and their prices. I need to run a SELECT Query which can return the last 5 prices for each item.
My Query is:
SELECT item, podate, price
FROM PurchaseOrders
GROUP BY item, podate, price
ORDER BY item, podate DESC, price
Let's say my Item A001 returned 12 records. I need only the 1st 5 records in my Report.
Can anybody help pls???????????
Hello,
On a form I have a way to search for a record by using a listbox that has a Distinct Row Query from the table that the main table that the form is bound to. It looks for the Sample ID's that are associated with the samples that we test. The list ends at record 87877. We are WAY past that number in our numbering scheme but the list box does not display all the records. When the users type anything over 87877 the auto complete doesn't work and the last record shown in the list is 87877. Does anyone have any idea how to make all records show? The users use this to navigate quickly between samples but now it is broken. Is there some sort of limit?
Thanks
Greg
Hallo Everyone,
I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.
aravind.s
Hello everybody....
I was given a task by my boss to create an appraisal system for our department here in one of the local schools in the Philippines.
Anyway, what I created first a form from which all subjects being offered will be displayed. I did this by having a main form (bind to personal info table) and a subform that displays all the subjects (bind to subjects table). Then, the user will simply click on the appropriate rows bearings the subejct that the students wish to enrolled and when the save button is click, such will be saved into the enrol table.
My questions is, what control will I use to allow me to select multiple rows? and how will I be able to save selected rows into the enrol table?
Thanks in advance and more power...
~sirjhun
Hi I am a noob and I'm having problems performing a particuler form.
I need a form that will auto-increment records as they are ented from 1 to 20 but once I have receached 20 I need it to start over at 1. So no record will have a higher number than 20 in this particular field.
Any help would be greatly apprciated.
:)
Hi there!
I`ve been using Access DB for a forum for about two years now, but from time to time I have to download the db and delete about a 1000 posts (+/-).
The reason for this is that the db want accept more posts unless I do so.
My question is how do I "extend" the db to accept more posts?
Thx in advance,
Sarre
I have a query that returns a set of records which details stock items that are older than a date given in a form. However i need to limit the results to the number of items held in stock
i.e say i want to look at stock over 1 year old, i get a list of all the stockids, and the date added.
Say there is a stock level of 3 for a particular stockid the results should be limited to the first 3 records that are over 1 year old. Rather than the whole list of dates I get now.
Any help or direction with searching terms would be appreciated
thanks:confused:
All, using 2010. I have a database that is split into FE and BE. The FE is on users desktop. The supervisor wants to give limit access to the database to one other user on the network without giving them access to the entire db. There is not an user table in the database or a login form at the moment. So what can I do to provide limited access to only one form?
View 1 Replies View RelatedHello,
I have created a query using the query builder by concatenationg several fields using the expression builder. Once concatenated, the total number of characters is greater than 255, and is therefore truncated. Since this is a query field and not a table field, there must be a way around this limitation. I can't even create the query and dump the results into a memo field because the dump will still only contain 255 char.
I could probably create the query using VBA, which creates a table containg a memo field, which is then populated by a variable containing the concatenated fields, but I would like a simpler solution.
Any ideas on how I can generate a query field that contains more than 255 char? The query is used to populate a report.
Thanks in advance.
Ken
Hi There,
I was wondering, in a form (to add records in the Table) with a date field, can I limit that field to only previous dates. This will reduce errors in the table, no future dates anymore.
Thanks in advance,
In importing data from Excel 2002 to Access 2000, I have several columns of text data that has more than 255 characters; however, importing into Access does not give me the option to redefine those data fields from "Text" to "Memo". As a result, Access truncates the data to 255 characters.
Is there anyway to work around this issue by still using the Excel file? I have saved the Excel file as an HTML file and this seems to work, but it is an extra step that I would like to avoid.
Thanks for any help.
Hi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check
Y ACD 2 x x boxes)
Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query
Y ACD 2 8/8/05 9/8/05 10/8/05 performs calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. So for month 1 & 2 I get dates for Lot X and for Lot Y I get dates for month 1 & 3. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
Thanks,
CB
I'm using Access 2000 and want to filter the datas in a form based on a query. The query written as an SQL string has already a length of about 2000 characters.
Now I want to filter this datas with information I get from a combo-box, and changed the RecordSource with additional statements in the WHERE part. Therefore the new RecordSource information can exceed the 2048 characters.
So I thought to use the filter-argument of the form instead. But the problem I have now is, that I would have to use "((First(T_Softwareversion.Softwareversionsname))>='B14')" as part of the filter argument. With using this I get the runtime error 3096. Using this as part of the WHERE part in the RecordSource works, but it works not using it in the filter argument.
Does anyone know a workaround for the first or the second problem?
thanks for any advice.
Rogenmoser
We have created a database where data entry happens on the first form. So far all is working well on this form except users complain there is not enough space for comments. I set up a column for comments, but it is limited to 255 characters. What/How else can I set this up to provide a lot more room?
View 1 Replies View RelatedAlright, I'm trying to read and parse a file that'll be outputted monthly by another system that i cannot change (csv would be my prefered choice) so i'll havta deal with what i got. I'm not entirely sure how to go about this, but my intent is to have a form in my database for a user to bring up a open "*.txt" which is a width limited text file. the other system outs puts this file with a header like the following:
Date: 06-08-06 [08:53] recdate4) Receipts by order, position and sequence Page: 1
CFM Production Company Comp: 100
-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
Supplier | Name | Item | Order | Position | Seq. | Receipt | Ordered | Delivered Qty | Del. Date | Rec. Date |
---------+-------------------------------------+------------------+--------+----------+------+---------+---------------+---------------+------------+------------|
100048 | RUSH ELECTRONICS | 53182 | 131810 | 1 | 3 | 204355 | 100000.0000 | 6800.0000 | 02-17-2006 | 01-06-2006
100048 | RUSH ELECTRONICS | 53182 | 131810 | 1 | 4 | 204440 | 100000.0000 | 3755.0000 | 02-17-2006 | 01-09-2006
100088 | MILLOMAT STAMPINGS | 10000893 | 132183 | 2 | 1 | 206065 | 19.0000 | 19.0000 | 05-11-2005 | 02-08-2006
100148 | PROSCIENCE INC | 10006712 | 132711 | 2 | 1 | 204083 | 135.0000 | 135.0000 | 01-03-2006 | 01-04-2006
182708 | RELIZON | 10008087 | 133187 | 2 | 1 | 204202 | 1000.0000 | 1000.0000 | 06-02-2005 | 01-04-2006
199402 | MIC INDUSTRIAL LTD | 06508 | 133996 | 1 | 1 | 205056 | 2304.0000 | 2304.0000 | 01-10-2006 | 01-20-2006
100088 | MILLOMAT STAMPINGS | 10007738 | 134398 | 9 | 1 | 205862 | 63.0000 | 63.0000 | 09-02-2005 | 02-06-2006
100032 | SPECIALTY GASKETS INC. | 10008184 | 134525 | 1 | 1 | 205812 | 150.0000 | 120.0000 | 04-25-2006 | 02-03-2006
100032 | SPECIALTY GASKETS INC. | 10008184 | 134525 | 1 | 2 | 207516 | 150.0000 | 30.0000 | 04-25-2006 | 03-07-2006
100032 | SPECIALTY GASKETS INC. | 10008184 | 134525 | 2 | 1 | 208138 | 120.0000 | 120.0000 | 03-17-2006 | 03-16-2006
199309 | BARZELLE | C0C3301 | 134707 | 1 | 9 | 207121 | 572.0000 | 40.0000 | 11-21-2005 | 02-28-2006
199309 | BARZELLE | C0C3301 | 134707 | 1 | 10 | 207253 | 572.0000 | 23.0000 | 11-21-2005 | 03-02-2006
199309 | BARZELLE | VE2333 | 134707 | 7 | 1 | 205893 | -15.0000 | -15.0000 | 11-08-2005 | 02-06-2006
100103 | DON PARK INCORP | 50986 | 134820 | 2 | 1 | 204551 | 30.0000 | 30.0000 | 09-29-2005 | 01-11-2006
100103 | DON PARK INCORP | 50987 | 134820 | 3 | 1 | 204551 | 30.0000 | 30.0000 | 09-29-2005 | 01-11-2006
Sorry about the ugly post of the outputted file, but it is width limited by 255 characters and has verticle bars and spaces sepearating the fields i want. I want to ignore the headings. in addition the column titles since i will already know where they're going to go (hard coding it). can someone explain how to parse line by line as a new record in access db?
- defy
As you 'll see my files, have one report into my database and i need to print it like in the SHEET SAMPLE Word document. My problem is how can I force the record excess, for example the 10 at the footer to print "In tranfer" with the sub total in previous! and in the next page to print the continuous sub total (From transfer) at the place of the 11th record meaning the first row on the detail of report and to continue on the last page to print in the last row the grand total (TOTAL).2nd problem is when the record for example is 2 to continue to print blank row to the last one and print the grand total (TOTAL).
P.S.1 I have posted it and the original sheet as it must be on print in, Excel file and it is important to print it with gridlines.
P.S.2 As you try to open the database please ignore the message saying the start form is missing
I have a database for scheduling students' for tests. They can take up to six tests in a day. There are about 80 different tests that they can take.
In my table, I created columns titled Test1, Test2, Test3, etc. They are lookup columns and I chose to enter my own values, putting in the tests titles for the values in each column.
When I add these drop-down lookup fields onto the form, it will only display 37 of these values. When I go back to the table and select "edit list items," it shows that it did cut the list off at 37, even though originally it allowed me to enter all 80-ish titles.
Anyway. It appears that there are limited values you can have in a lookup column, though after doing a lot of searching online I can't find anything to indicate that is true.
It seems to me that it would be smarter to set this up with two different tables, storing the reg info in one table and the test titles in another table. However, I am having a hard time figuring out the relationship aspect of this solution and how to make it pull up the correct values for queries/reports as well.
I'd like to run an Update Query on a limited number (or percentage) of records from a Button on a Form.
[URL] .....
But instead of opening a Form, I want to Run an Update Query instead.
I have database that i am working on , i want to give a user a time limited accsess ( one week ) to the reports section for example !!
after a certain date he will require a password or the report button will be inactive ...
-at program start up , it will check for today's date if it is greater or equal to (lets say ) 24-9-2013 then it will change a field in an X table from "yes" to "no" ,
-after that the code will check table X for the yes no field , if yes then open Form A if no then open form B / or make report button inactive
how to do that in VB ... and is there is a better way to do it ? can it be done in the macro builder ?
The column in my list-box did not show all the text i save in the table field. is there any limited size for list-box's column to show the data ?
View 2 Replies View RelatedNot sure if what I want to do is possible, or at least possible the way things are set up.
I have a massive table - c. 6 million rows. It contains data along these lines:
Plan#, Item, Price, Description, Colour, Value, Location, etc.
The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).
To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.
Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).
I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.
Any help or suggestions would be much appreciated.
Thanks!!!
I'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.
One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code
Code:
Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 3 Then
Me.AllowAdditions = False
End If
End Sub
to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":
Data Entry
Allow Additions
Allow Deletions
Allow Edits
Allow Filters
I am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
I have three tables (SkillList), (SubSkillList), and (SubSkillList2) to populate 3 combo boxes - cboSkillList, cboSubSkillList and cbosubSkillList2. I want each limited by the proceeding. The first works fine (SubSkillList) IS limited by (SkillList). I'm unable to get the third to return a value. It's returning...
Syntax error (missing operator) in query expression '[SubSkillID] = Class A'
where "Class A" is the text of the selected value in the table (SubSkillList) - can't determine where it's failing (?)
Code as follows:
Private Sub cboSkillList_AfterUpdate()
With Me![cboSubSkillList]
If IsNull(Me!cboSkillList) Then
.RowSource = ""
Else
[code]....
Hello All,
I have a small problem which I feel I should be able to solve but the hours of trying are proving me wrong!
From a choice of 16 events, competitors can enter as many as they like providing they are eligible. For each event they receive performance marks which are duly stored in a table. Some compete in 1 event others in 5 or 6. (I do not have a control showing ‘number of events entered’ and have tried several options, all unsuccessful, to create a ‘count’ of events entered. Is there a way?) My main problem is, from each individuals’ records, how do I select only the highest 3 marks from the classes each individual entered.
I’d be very grateful for any advice/guidance.
Bomac.