Sorting Sequence
Jan 21, 2008
Hi there,
Not sure whether I am in the right forum. It is Table, Form and Query.
Have a table which index to sort on 4 fields - Proj, SubProj, Team, SubTeam.
1. I used this Table on a form, the data seems to be in random sequence instead of above.
2. Then I tried to sort it in a query which showed up fine ( as required) in the actual running of the query. However when I used this query in a form, the data doesn't sort as expected.
3. With above 1 and 2, I have also tried the Order Of in the Form properties which still do not do the trick.
can anyone shed somelight on this please.
BTW, After I first created the form, I just rename the record source in form property. Don't know whether this make any differences in the above result.
View Replies
ADVERTISEMENT
Mar 10, 2006
Hi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.
Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..
I'd apreciate some help with this :) Thanks
Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
View 5 Replies
View Related
May 25, 2006
I have duplicate loan numbers in a table. I want to create a query that will assign a number so the the duplicates will have a new field called sequence.
example
LNNUM SEQ
999999 5
999999 4
999999 3
999999 2
999999 1
888888 3
888888 2
888888 1
777777 2
777777 1
Any suggestions?
View 2 Replies
View Related
Mar 20, 2007
Hi all,
I have encountered some issues with the dates.
For e.g, Jan-06, Feb-06, April-06, May-06
Is there any way to determine that Mar-06 is missing from the list. The listing will be growing.
Cause I need to have a culmulative counter so Jan-06 to Feb-06 will be counted as 2. But if there is a break in between, in this case, Mar-06, the counter will be set to 0, so for Apr-06, the counter will be 1.
Have been troubled by this issue for a long time, I appreciate all help given, thanks!
View 3 Replies
View Related
Apr 4, 2007
Hi... i have a excel file..... from which i have imported records.... after importing.... the records sequence in my table has changed..... can anyone tell me how to preserve the sequence that i had in excel..........
View 3 Replies
View Related
Oct 27, 2005
I tried this question a few weeks ago, but I'm trying again.
I have 2 Access 2002 files. One has a sort order of ascii and one has a sort order of international. I need them to both have the same sort order. It doesn't matter which one. Right now I get the above error message on one of them.
How can I change the sort order so that I can import Paradox files like I used to?
:eek:
View 11 Replies
View Related
Aug 15, 2007
Hi,
Its good to be back after a long time. Hope everyones fine.
I have a query and would appreciate any help.
I have a master-table (tblM) and two related tables (tbl1, tbl2) with one-to-one relation.
Relation1 : tblM.ID (autonumber) related to tbl1.caseID (number;LongInteger)
Relation2 : tblM.ID (autonumber) related to tbl2.caseID (number;LongInteger)
Relations are cascaded (referential integrity imposed).
I import data from excel and feed fields of tblM (I dont feed ID field from excel as it generates autonumber). At a time I feed 150 records (daily).
I have a front-end based on query from three underlying tables. Data-entry-operators do not work on tblM data as it is readonly in the front-end. They enter data in the tbl1 and tbl2 columns.
I noticed as they start entering data in tbl1 columns, the tbl1.caseID column gets data from tblM.ID on its own. Same happens in the case of tbl2.
My observation is :
I find that tblM.ID is not equal to the number of records available in tblM. (ie tblM.ID is more/less than number of records in the table. So the tblM.ID generated is not in serial.
Next time as I start importing data from excel file to tblM it gives me error and not allowing further import.
Any help!
Thanks and regards,
Prodigy.
View 5 Replies
View Related
Aug 29, 2006
Hi,
I have a table with 400,000 (approx) records in the following format:
a(PK:AutoNum) - b(text)-c(text)-d(Num)-e(text) - f (text - unique/random)
A B C D E F
1 - 6767 - P2 - 1 - 24/992 - 34341212
2 - 6767 - P2 - 1 - 24/993 - 87657483
3 - 6767 - P2 - 1 - 24/995 - 98764536
4 - 6767 - P2 - 2 - 24/996 - 87543297
5 - 6767 - P2 - 2 - 24/998 - 98674635
6 - 6767 - P2 - 2 - 24/999 - 34546576
7 - 6767 - P2 - 2 - 25/001 - 98768547
8 - 6767 - P2 - 2 - 25/002 - 46576897
9 - 6767 - P2 - 2 - 25/004 - 62536475
I need to create a new field in a query that acts like an autonumber, that resets based on an increase in field (d).
ie:
1 - 6767 - P2 - 1 - 24/992 - 34341212 - 1
2 - 6767 - P2 - 1 - 24/993 - 87657483 - 2
3 - 6767 - P2 - 1 - 24/995 - 98764536 - 3
4 - 6767 - P2 - 2 - 24/996 - 87543297 - 1
5 - 6767 - P2 - 2 - 24/998 - 98674635 - 2
6 - 6767 - P2 - 2 - 24/999 - 34546576 - 3
7 - 6767 - P2 - 2 - 25/001 - 98768547 - 4
8 - 6767 - P2 - 2 - 25/002 - 46576897 - 5
9 - 6767 - P2 - 2 - 25/004 - 62536475 - 6
I initially thought I could make an IIF expression that basically says: if [d] in this row is the same as [d] in the previous row then add 1 to the previous record [new number], otherwise, enter 1 in this record [new number] (thereby resetting it)....
I have searched the forums for 'numbered sequences' (amongst other things), and it appears that either the Dcount or Dmax function appear to be the solution, but I'm having some trouble applying it to this problem. I was hoping someone can help me through it.
Cheers
View 1 Replies
View Related
Oct 30, 2007
I'm running an update query and I get this error - Function Sequence Error. (#0)
I have an excel spreadsheet that I imported into a table. I am trying to update the part table with date from the spreadsheet. The part id is unique and thats how the tables are joined. I only need to update 5 fields. The table I'm trying to update is a linked table.
I can't figure out what I need to do to make the necessary updates.
View 1 Replies
View Related
Dec 30, 2013
I work for a non profit agency, and we offer a Representative Payee service (paying bills for those who can't handle their own money). I've created a database that keeps track of everyone's finances, and gives us all the reports we need. The only thing I have not been able to do is to actually print the checks.
I have a report that pulls up the data (in check format ... including making $1.00 say one dollar and 00/100). But I'm stuck trying to give the checks a check number. I can't figure out how to pick a number to start with, and then add numbers sequentially.
View 6 Replies
View Related
Aug 7, 2007
I want to create a custom sequence when deleting a record but I'm having a problem.
What I want to do is prompt the user for the reason he/she is deleting the record and then archive this so it can be viewed in a log at a later time. This extra step is not as easy as I thought it would be.
What I did was put some coding into the On Delete event of the form. The code opens a form with a text box. The user can enter the reason for the delete in the text box and click OK or click cancel to abort the deletion.
My problem here is opening another form from the On Delete event. As soon as the form is open, the code just passes to the next step of the sequence and deletes the record.
There are 2 approaches that I've thought of but I'm not happy with either.
Approach 1:
In the On Delete event, set Cancel to True. That way the deletion is not handled by the delete sequence but rather by the new form that opens. The problem is that deleting a record from another form is not that simple. What I've tried is to execute a SQL statement that grabs the ID of the record from the open form and delete it. The problem is that although the record is deleted, all the fields on the form say #Deleted in them which is not that pretty. (Maybe there's some other way to do the deletion without this problem that I'm not aware of...?)
Approach 2:
Use an Inputbox. Unlike a custom form, the Inputbox delays execution of the code so the code can determine from the results (i.e. should it cancel the delete if the user clicked Cancel). The problem with Inputboxes is that they are not very flexible in the layout, they look pretty "packaged" (i.e. they look to me like the programmer didn't bother creating a means to enter a value). There's no way to place the buttons where you want or have it do better validation (e.g. I want the FORM to check that there's at least a few words describing why the user is deleting the invoice rather than have the form with the record get the input message passed back and then give an error and then pop open the Inputbox again)
I think that especially when you're creating an application with anything financial, you'll want to log a deleted invoice including the date, amount, possibly the client's name, invoice number, reason for deletion and the login name of the person doing the deletion. All of these are easy to record other than the reason.
Help would be much appreciated.
SHADOW
View 5 Replies
View Related
Mar 30, 2006
Hi!
1. If accidently a record or two in a sequence are deleted, how will you know that this has happened! What type of validation can be enforcedto prevent this?
2. How do you make your Tables, Quries, Forms, Reports, Macro tamper proof?
Please help!
Orsonros
View 2 Replies
View Related
Apr 3, 2007
I 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
View 3 Replies
View Related
Jun 7, 2006
I need to create a button that generates a sequential four digit requisition number and places that number in a field along with some text. I cannot use autonumber because this number is not and cannot be the primary key. In addition, I need to be able to reset the number to 0001 at the beginning of each federal fiscal year. Here are the details:
reqtype: An option group that has these choices: PC, TL, and RQ
actionyear: A drop down box that lists the current fiscal year: 2007
reqnumber: A field that, when the button is clicked, combines a text prefix, reqtype, and actionyear with a four digit sequential number.
The end result should look like this: SEC-07-PC0010 and be recorded in the reqnumber field.
My code writing abilities are limited. Could someone please help me out. Thanks.
View 1 Replies
View Related
Sep 6, 2004
Hi,
I have 2 single-page Access reports. The first report has the following fields:
FirstName, LastName, StreetName, HomeNumber. The second report has the following fields:
MerchantA, MerchantB, MerchantC.
The data for the first report are taken from tblBuyers, the data for the second report are taken from tblMerchandise.
I need to print 2 copies of the first report and 4 copies of the second report for the first Buyer, then 2 copies of the first report and 4 copies of the second report for the second Buyer and so on. Can I help on this problem ?
Rgds
View 1 Replies
View Related
Feb 1, 2005
Hello, I have no idea what to search for to see if someone else has already asked this question. I've tried several things with no luck. So here goes...
I work in a library. We deal with thousands of journal subscriptions--we cancel our subscriptions and order new journals all of the time. Every paper journal we have is assigned a unique number, called a ZP number. When we throw out a journal, that ZP number can be assigned to a new journal.
I would like to find the earliest missing number in the sequence without having to check manually. Is there a way I can do a query or something to find missing numbers in the sequence? The ZP numbers start with 1 and go up to 9999.
So, I have two fields: Title and ZP#.
How can I do this?
This is a cloned table, used solely for assigning ZP#'s, so it can be altered in any way--including adding records with blank titles for each missing number.
Thanks,
Siena
View 8 Replies
View Related
Jul 2, 2005
Hi guys
Well Im stuck!
I have to produce a report (preview first)that acts like a cross tab query. By this is mean that it reports specific data in a 12 period sequence for each year. Therefore I have to set up dates which are NOT normal calendar dates ie one could be 02/05/05 to 29/05/05 then the next would be 30/06/05 to 24/07/05
These dates are issues at the start of a contract year. I have to therefore produce the report for each period that is given to me ( not an easy one is it) I have considered many options but I cant quite get the final effect
Any Ideas ??
gareth
View 4 Replies
View Related
Jun 27, 2006
Is there a way to make a query that has several steps that have to be run in sequence, rather than all at the same time?
For example, I want to create a table that is created from 3 other tables that I download.
The first step would be to append all titles from the first downloaded table, not allowing duplicates.
The second step would be to append all titles from the second downloaded table, not allowing duplicates.
And the same with the third step.
It would be nice to be able to do this with one query, but since it gives me an error because I am trying to do the same thing three times to the same field, I can't. I would really like to avoid having to save this as three different queries, like I've had to do with other multi-step queries.
If there's a way to do this, I'll be able to apply it to several other multi-step queries I have.
Also, if that is possible, is it also possible to have several types of queries in one? Like an append query with several update queries, performed in sequence?
(Note: I'm creating these queries in Design View--I don't know enough to write my own queries in sql, except for my one union query--which I had help with--from here, I think. I can, and often do, alter sql queries that others have written for my own purposes, though.)
Thanks,
Siena
View 5 Replies
View Related
May 8, 2007
Hi
I've searched the forum and only found one thread which covers this but doesn't help me.
I have a table with peoples individual information in it.
Every person has a unique ID number.
I want to find the missing ID numbers when I delete a person so I can keep the numbers in sequence.
I am not using autonumber. The reason for this is coloured wristbands. Each wristband has a unique number and they are broken into 4 colours. each colour has a number range 1 - 300, 301 - 600, 601 - 900, 901 - 1200. Each person in the table has to have an ID number matching a wristband. The people are also broken into 4 colour categories. This means that not every range is filled before i need to start using the next range.
Hope that makes sense.
Snab
View 7 Replies
View Related
Sep 17, 2013
i want to know such a method that if i give a number, the database find me the next available number from the given list in the table.for example, i have a table having list of numbers like 6500, 6501, 6502 etc, I give the number 6500, the database should give me the next available number in sequence from that given list in the table.
View 7 Replies
View Related
May 6, 2014
I have a RTE 94 happening due to a piece of code running on a subform current-event which looks at a text box on the parent form. I understand this is due to the order of loading of the forms initially and all works fine when moving to other records once all are opened. How can I overcome this happening on first opening?
I am thinking I need to set a counter on the subform on load event to say 1, then when the subform current-event fires add some code so if the counter is 1, then do nothing, if 0 then run the code. When the mainform opens reset the counter to 0, then requery the subform. The form will now see the counter is set to 0 for the rest of the time, so will then always run the code.
Not sure how to go about setting out the code as I guess there needs to be some form of Public variable involved?
View 4 Replies
View Related
Oct 13, 2014
I'm creating a table with times of the day from 08:00 to 17:00 in 15 minute increments.
I'm going to use the table as a lookup to make it easier to enter an appointment time.
I don't really want to type them all by hand - does access have an equivalent to excel's drag and autofill a sequence? is it just as easy todo it in excel then cut and paste it?
View 6 Replies
View Related
Jan 24, 2015
I am writing a deposit recording database for archaeological layers. My units are recorded in one table, and then the relationships are recorded in a separate table. I have limited the recording of relationships to be either "same_as" or "below".
So in tblContexts is have say: 1,2,3,4,5,6
in tblcontextrels I have say : 2 is below 1; 3 is below 1; 4 is below 3; 1 is below 5; 6 is the same as 5
I want to view the deposits on screen in their stratigraphic sequence. So I think I need to write a piece of code to run through "tblContextrels" which will then create a hierarchy attribute. This attribute can then be used to sequence the context deposits I am looking at.
View 2 Replies
View Related
Nov 12, 2013
I would like to insert a sequence number into a query that currently returns 7367 rows.
Here's what it looks like now:
ID_NUM
DTE_FIN_TRANS_RCV
1322
9/27/2012
1322
7/10/2012
1011
7/2/1999
1011
9/22/1999
1011
10/21/1999
Here's the desired outcome with sequences:
ID_NUM
DTE_FIN_TRANS_RCV
SEQUENCE
1322
9/27/2012
1
1322
7/10/2012
2
1011
7/2/1999
1
1011
9/22/1999
2
1011
10/21/1999
3
All I need is a way to number rows with a value higher than the previous for that particular ID_NUM.
View 4 Replies
View Related
Feb 1, 2013
I'm trying to code for a 'duplicate record' button on a form that, as well as copying the values of all the controls of the previous record to the new record, makes a certain control (which contains a number) increment as appropriate.
There are two controls on the form which contain the start and end values of a numerical sequence (of potentially any length) e.g. the range 48-82 shows 48 in text box boxREGSEQSTART and 82 in the text box boxREGSEQEND OR the single value 99 shows 99 in text box boxREGSEQSTART and text box boxREGSEQEND is empty. In a record, the value of boxREGSEQEND would always be higher than that of boxREGSEQSTART or would be blank.
The appropriate incrementing action would be:
After clicking 'Duplicate Record' button, If there is no value in boxREGSEQEND in the record being copied, then in the new duplicated record the value of boxREGSEQSTART should be 1 greater than the value of boxREGSEQSTART in the record it has been copied from.
i.e. the record being copied is records a single item, a single value, and the next record should show the sequence moved on by one
OR
If there is a value in boxREGSEQEND in the record being copied, then in the new duplicated record the value of boxREGSTART should be 1 greater than the value of boxREGSEQEND in the record it has been copied from.
i.e. the record being copied is recording multiple items, a sequence, but the next record should still show the sequence moved on by one
I can get the duplication to work no problem, and I can get the incrementing to work fine for the first set of conditions, but can't for the life of me get it to increment when there is a range!
The logic (in my head, expressed as best I can in the macro language ) for the whole sequence of actions goes like so:
RunMenuCommandCommand SelectRecordRunMenuCommandCommand CopyRunMenuCommandRecordsGoToNewRunMenuCommandCommand SelectRecordRunMenuCommandCommand Paste
If [boxREGSEQEND]=[IsNull] Then
GoToControlControl Name boxREGSEQSTARTSetValueItem = [boxREGSEQSTART]
Expression = [boxREGSEQSTART]+1Else
GoToControlControl Name boxREGSEQSTARTSetValueItem = [boxREGSEQSTART]
Expression = [boxREGSEQEND]+1GoToControlControl Name boxREGSEQENDSetValueItem = [boxREGSEQEND]
Expression = [boxREGSEQEND]=Null
Which I would assume would tell the program to copy the current record, make a new one and paste the copy, then if there was a range recorded in the original, start the numbering of the new record one number higher than the end of that range and clear the way in the new form in case it also records a range, otherwise just increment the sequence along by one.
View 3 Replies
View Related
Mar 23, 2006
Hi -
Suppose I have a generic query that returns a group of records.
Is there some way to generate a field containing the number sequence of each record? E.g. if there are 10 records, generate the numbers 1 through 10?
Must be a simple way to do this, I'm just a bit dense today...
Thanks,
-g
View 1 Replies
View Related
