Conditionally Numbered Sequence
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 Replies
ADVERTISEMENT
Dec 10, 2007
I have a table with quite a number of records.
In this table I have a field [Qnumber], which gives me a sequential number.
The field size is Long Integer. So my records are numbered 1, 2, 3, ....n
What I want to achieve is to select only the even numbered records in this table, i.e. Qnumber = 2, 4, 6, ...... etc.
I can't find a way to do this.
Would appreciate any help on this.
View 3 Replies
View Related
Sep 21, 2004
I have a report listing all individuals within my database by their score. I need to select the top say 40 people from the list, instead of counting down 40 from the top how could I put a number beside each name? Any help would be greatly appreciated.
View 12 Replies
View Related
Dec 14, 2004
I have a inventory list that i export from another programe
it exports the lines like so:
ITEM DESCRIPTION PRICE QUANTITY (this line does not get exported)
1021 60BL+40PI+20SL 11$ 120
1022 50BU+30SL+10BZ 8$ 90
CR5415 50BZ+50BL+50SL 5$ 150
Now the program only calculates the quantitys when a order is submited into it.
it does not subtract the quantity on the color in description.(i am the one that mannualy added the text in description wiche is my pysical inventory)
I am able to export the lines from the orders to another table wich looks like so:
1021 05SL 11$ 5
1021 10PI+10SL 11$ 20
1021 02BL+5PI+5SL 11$ 12
Now the description is again me just mannualy typing the text into the order in description and then the quantity.
the program then just minuses the quantity from the inventory list.
now what i hope to acomplish is be able to take the invetory list, and substract the LineOrders Description from it,
and then just give me the new inventory list.
I figured it goes somthing like this but im not to sure.
If [InventoryItem]=[LineOrderItem]
Then
Compare
If __@@ from [InventoryList]is the same as __@@ from [LineOrder] then subtract ##__ from ##__
And i hope to get at the end my new inventory list in this case it would be
1021 58BL+25PI+05SL
I dont mind about the total quantity since the other program does that for me.
If anybody can help me it would be really apreciated.
I hope i have made sense, if i have not please tell me and i will try again.
thank you
Mark
View 14 Replies
View Related
Jan 9, 2014
My friend extracted some text from a PDF file, pasting it into Excel, and needs to find certain pieces of information. Problem is, the text came out as a long string instead of being broken into cells.
So, what we need to do is find, within the text, the 1st, 3rd, 5th, 7th, 9th, 11th and 13th times the word "Principal" appears, and then report back the name that follows. Within the text, it would read something like
(1234 Principal John Doe)
The number in front of it will change or be in a different format, there are other parentheses in the text (varying number of times within the text), and the name changes, of course. We want to extract the name following the word "Principal"... but only the odd-numbered times they appear. Some cells will have as few as six "Principal" entries, others as many as fourteen.
View 5 Replies
View Related
Oct 24, 2013
I am trying to create a named hierarchy based off a table from an Oil & Gas Program ARIES. The code below is as far as I could get:
Code:
SELECT dbo_AC_ECONOMIC.PROPNUM
, dbo_AC_ECONOMIC.SEQUENCE
, dbo_AC_ECONOMIC.SECTION
, dbo_AC_ECONOMIC.QUALIFIER
, dbo_AC_ECONOMIC.KEYWORD
, dbo_AC_ECONOMIC.EXPRESSION
, (SELECT COUNT(*)
[Code] ....
The results are as such:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1
TEST1.......4................2.................TAG ............."..................22................ ..1
TEST1.......4................3.................TAG ............."..................22................ ..2
TEST1.......4................4.................TAG .............SHRINK.........1...................1
TEST1.......5................1.................TAG .............ATX...............5.................. .1
TEST1.......5................2.................TAG .............ATX...............5.................. .2
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1
TEST2.......4................2.................TAG ............."..................22................ ..1
The desired Results:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST...KEYWORD2
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST1.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
TEST1.......4................3.................TAG ............."..................22................ ..3........GTC/GAS_3
TEST1.......4................4.................TAG .............SHRINK........1...................1.. .......SHRINK_1
TEST1.......5................1.................TAG .............ATX..............5................... 1........ATX_1
TEST1.......5................2.................TAG .............ATX..............5................... 1........ATX_1
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST2.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
First I will give background on the table and fields. Then I will explain the overall goal for creating of the TEST and KEYWORD 2 fields.
BACKGROUND
PROPNUM: UNIQUE ID
SECTION: Set of data responsible for a certain function EX: section 4 - expenses, section 5 - interest, etc.
SEQUENCE: The order of the sytanx in that propnum's section
QUALIFIER: Qualifies multiples set of syntax per section to differentiate other work (NOT REALLY IMPORTANT for the query)
KEYWORD: A specific word that the program recognizes and treates the expression according to the key word * the quotes keyword is a continuation line and represent the keyword above it*
EXPRESSION: are the variables that are treated by program according to the keyword
OVERALL GOAL: The main goal is to have a spreadsheet of variables used by the program to calculate it's end result. Which means KEYWORD & EXPRESSION by PROPNUM. THE PROBLEM is that the only way to tell that a quote keyword belongs is by having the sequence and section lined up. So my solution is to rename the quote keyword with the primary keyword and a number.
View 2 Replies
View Related
Jan 19, 2015
I have a union query with 2 fields: Order and Row_Heading_Full.
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType
UNION SELECT ("25") AS [Order], ("Totals") AS Row_Heading_Full
FROM DummyClientType;
The purpose is to pull the Order and Row_Heading_Full fields from a table and add another entry that with "25" as the Order and "Totals" as the Row_Heading_Full at the end of the list.
I want it to be ordered sequentially based on the Order field. But instead it is ordered like this: 1, 10, 11, 12... 20, 21, 22... 3, 4, 5... In other words it is ordering only by the first digit and not by the number as a whole.
When I remove the union aspect it is ordered properly:
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType;
But that defeats the purpose because I am not adding a final entry of "25" and "Totals".
View 14 Replies
View Related
Oct 10, 2013
I have a table (tblContact) with an auto number key field that is numbered sequentially (1-8) there are no deletions, each new record is appended.I wrote a simple FindFirst line to locate a record that is the first record in the table. The FindFirst failed to find the record.So, I wrote a Do Until Loop that cycles through each record looking for the record that I want to find (the first record with key field 1).
Code:
rstContact.MoveFirst
Debug.Print "ContactID: " & rstContact.Fields("ContactID")
Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf
Do Until rstContact.EOF
Debug.Print "ContactID: " & rstContact.Fields("ContactID")
Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf
This works to find the first record... eventually, because it does not begin its search at the first record. The results in the immediate window are below.
Code:
ContactID: 4
CEmployerID: 2
ContactID: 4
CEmployerID: 2
ContactID: 5
CEmployerID: 4
[code]...
I believe the field CEmployerID is unrelated to the issue. I am also attaching screen shots of the table "tblContact" and code with immediate window. I have tried indexing and not indexing the CEmployerID field in the Contact table to no avail. Even though the Do Until Loop eventually finds my record,
View 10 Replies
View Related
Aug 30, 2006
I am trying to enter an experssion into the grid for age groups. The result will be a text value based on the value in a column named Age. There are three groups, '<18', '18-30', and '31+'. I tried using the following but the result that is returned is #Error.
age_grp: Switch([age]<18,'<18',[age]>=18 And [age]<31,'18 -30',[age]>30,'31+')
Do I have the syntax wrong? or maybe I'm going about it completely wrong.
I have also tried a nested iif statement Age2:= iif([age]<18,'<18', iif([age] >=18 And < 31, '18-30'), iif([age] >30, '31+')). I get the same error message.
This worked for displaying the value on a report =IIf([age]>=18 And [age]<=30,"18-30",'31+'). However, I need to sort and group by the value so I can count the number in each group, so I wanted to put it into a query.
Thanks,
Christy
View 4 Replies
View Related
Apr 11, 2005
OS/Program :xp/Access 2002
The following problem. Within my main form I have a status field. Dependent of the status of the main field I would like to lock the total record in my sub form at once. Within which event of the sub form could I achieve this and which property locks a total record?
thanks
giovi
View 2 Replies
View Related
Oct 10, 2005
Hi all
Long time searcher, first time poster!
I have a subform which has loads of check boxes for each record but i want to suppress certain boxes depending on criteria in each record. I think i need to do a For..Each statement but, as my user name suggests, I'm buggered!.
View 2 Replies
View Related
Oct 18, 2005
Hi All,
I have built a code database where right now I display my sample code in a textbox on my main form. I was hoping to replace the text box with a Rich Text Box. I could then format the code to sort of match what would be shown in the VB/VBA IDE.
I know the Rich Text Box was disabled in Access 2003 for security reasons. I also know that there is another free RTF control. I was hoping to find out how Microsoft intended for us to replace the Rich Text Box. I could use a Web Browser Control but I was not sure of the best way to add this to my form and then feed it the data that was originally bound to the text box.
Thanks for any insight you would be able to provide.
Steve
View 1 Replies
View Related
Jan 10, 2006
Can anyone help me out here.
I need to use an unbound text control to return me a value if a field is populated else "Local" if the field is not populated.
Can anyone tell me if this is possible and show me the syntax if it is.
Help appreciated.
Andy
View 8 Replies
View Related
May 1, 2006
I am trying to write an expression that calculates a new field in a query. The new field is simply recoding values from another field (survey question responses) using IIf statements. This new field, let's call it RecodedResponse, will eventually be summed in a totals query to get the total score on that survey for each participant. Each record is a question response and there are multiple records for each participant (16 items on this particular survey). The problem is that for 2 of the questions on this 16 item survey, the responses are supposed to be eventually combined and recoded based on each other. So, my question is:
Is there a way to do a conditional expression where the result from the previous record is used? Or do I need to first do a crosstab query so that all the responses for one participant are on one line?
I am pretty sure I can do a workaround to make it work, probably doing a crosstab, then recoding in another query based on the crosstab, but it would be cleaner (I think) to do this some other way. Any ideas? Or is this too vague?
Thanks,
Liz
View 3 Replies
View Related
May 10, 2007
Is there a way to select a query field based on conditions? There is a table at work that I need to use for my reporting in which the field names are the months of the year. Since my report is monthly for that specific month only, I have to change my field in the query every single month. I would like to be able to put in some type of code that will automatically select the proper month field.
Is this possible?
Thanks! :)
View 2 Replies
View Related
Apr 30, 2014
I work for a doctor, and I am creating a database to assist with surgery scheduling. There are a lot of pieces and parts to this process, and I am building a "bird's eye view" of tasks to be completed, such as "Notified OR," "Sent Surgery Packet," and "Scheduled Post-Op Appt." Each one of these fields has a lookup attached to it with three choices: "Yes," "No," and "Not Needed."
What I want is for the drop-down box with the three choices to be (a) automatically defaulted to "No," (b) immediately turn red if "No" is displayed or immediately turn green if "Yes" or "Not Needed" is displayed. In this way, the goal for our scheduler will be to get all the drop-downs to turn green (an easy visual cue that the patient is ready for surgery) by the date of the procedure.
View 1 Replies
View Related
Mar 29, 2013
I have a databse of bird ringing (banding) which I have turned into an encouter history by using the pivot table capability in MS Access. Example attached.
For each animal, identified by a ring number in column B, there is a set of "encounters" over time. A blank in a date column indicates that the animal was not seen or captures in that year. A number in the column indicates that it was seen or captured n times.
To format these data correctly for subsequent surival probability analysis, I need to replace all of the blanks, in each of the "year" columns with a 0 and anything that isn't blank (e.g. 1, 2 or 3) with 1. So, I need columns F to L to look like C to D.
It was simple enough to do this one column at a time...
UPDATE [Capture history intermediate] SET [Capture history intermediate].[1971] = "0"
WHERE ((([Capture history intermediate].[1971]) Is Null))
But I have a lot of data, spanning 40 years (i.e. 40 columns) and I will probably have to do this conversion many times as I work on sub-sets of my master database.
So, question is, is there a short-cut way to avoid having to create 40 different queries and run each one seperately?
View 3 Replies
View Related
Jul 10, 2015
I have a button on a Form which opens a report, passing in two WHERE clauses which are driven by two comboboxes on the Form (one for ProductType and one for ProductCollection).
Everything works great but I needed to add an "All" option to the comboboxes so that the user can get all Producttypes from a specific Collection or vice versa.
I've managed to add the "All" option to one of the comboboxes (using the UNION query method on the rowsource). I'm a bit stumped though, as to how I would actually tweak the function below to do a select * where the combobox value is found to be = All.
Code:
Private Sub ProduceReport_Click()
Dim strWhere As String
strWhere = "producttype = " & Me.ProductTypeForReport
strWhere = strWhere & " AND ProductCollectionName = '" & Me.CollectionForReport
strWhere = strWhere & "'"
Debug.Print strWhere
DoCmd.OpenReport "Products Report", acViewReport, , strWhere
End Sub
View 7 Replies
View Related
Sep 27, 2013
I have a simple select query , and I want to add a field that will take the value from another field and increment it based on a condtion.
i know i need a case statement but not sure how to go about this ..
column 4 is my desired result
i want to increment the text string from column 3 based on column 3 and restart the increment based on column 1
values of A would increment in the following manner 01,11,21,31,41 etc
values of O would increment 02,12,22,32,42,52,62
i'd also like the option that if a new value were introduced to increment it
values of x would increment 80,81,82,83,84
Example data .. apples and oranges ..
column1 column2(name) column3(type) column4
mary braeburn A 01A
mary blood O 02A
tom cortland A 01A
tom fuji A 11A
tom navel O 02O
tom mcintosh A 21A
[Code]...
View 2 Replies
View Related
Dec 31, 2013
I'm running Access 2003 and my question relates to delivery notes that are produced for drivers as reports. These delivery notes have a blank payment box for the driver to complete if the customer pays upon delivery.
As more and more customers are prepaying by credit cards, I'd like this payment box to be automatically filled with a "PAID" text which will let the driver know the order has already been paid for.
The delivery note is produced from an "Orders" table which has a "Paid" field whose value is automatically set to "Yes" when the order has been paid for.
I was hoping to be able to place a "PAID" label on the report which is only visible when the Paid field of the underlying query is Yes.
As things are at the moment I cannot see any way to put any conditional statement onto this Text/Label. The only options for the "Is Visible" property of the text are Yes or No.
View 4 Replies
View Related
Dec 13, 2013
Have a generic dashboard report with multiple charts in it. One in particular is a bar chart, but depending on what the dashboard is produced for, the bar chart may have too many bars in it to make it legible. Therefore I'd like to conditionally determine the number of bars (basically the number of records in the query) and then make the chart go from one page and continue onto another.
View 4 Replies
View Related
Oct 7, 2014
I've searched and can only find information setting the visible property when selections are made on an open form--I want to set the visible property when the form is opened depending on which avenue the user chooses from the navigation form.
I have a navigation form (Form A) and a second form with a subform (Form B). The user will choose from a combobox, either an existing record or a new record on Form A. On Form A there are two buttons, one that will take the user to the correct record on Form B for editing and one that will open to a new record on Form B. Once Form B is open, all controls will be blank (if a new record is chosen) or with certain controls prepopulated if an existing record is chosen.
What I want to do is to hide controls on the main form (not the subform) of Form B if the user chooses an existing record. Form A's buttons work correctly to open Form B right now. I want to be able to hide prepopulated controls on Form B if the user chooses an existing record from Form A.
Here's the code I'm using to open Form B to an existing record. I'd like to set the visible property here if at all possible.
NOTE: "Form A" and "Form B" are not the actual names of my forms--it's just easier for illustration purposes here in my question.
Private Sub cmdAddLogEntry_Click()
DoCmd.OpenForm "Form B", acNormal, , "Activity_ID = " & Me.cboINum
DoCmd.Close acForm, "Form A", acSaveYes
End Sub
View 4 Replies
View Related
Oct 30, 2013
I'm producing PDF reports in a VBA routine in Access through the DoCmd.OutputTo acOutputReport.
The routine works great, however i have now a new requirement and i need to change the report header so to if Country X is selected in a drop-down menu, then only image X is shown on the report header; if country Y is selected then only image Y is shown on report header.
I was simply thinking to make this through the "visible" option of the image, however it doesn't work because i get error "report must be open".
View 3 Replies
View Related
Feb 28, 2014
I have a table with 2 fields:
CODEDATE (text)
DATE1 (date)(dd/mm/yyyy)
RECORDS SAMPLE:
Code:
-------------------------------------
CODEDATE DATE1
-------------------------------------
TT-10/10/2013 01/01/2012
ASDFDFDF 01/02/2013
RR-18/06/2012 05/08/2012
------------------------------------
I need to extract the date from CODEDATE field if the "-" character exist in or from DATE1 if the character "-" is not present in the CODEDATE field, then filter the query on the date found.
I wrote that:
Code:
SELECT IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) AS DateFound
FROM MyTable
WHERE (IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1])
Between 1/1/2013 And 31/12/2013)
The query above extra extract some records without sense for me, I tried many other syntax without success.
View 12 Replies
View Related
Dec 18, 2014
After I have run all my access queries is it possible to send a conditionally formatted excel worksheet all in access using vba?
View 1 Replies
View Related
Mar 15, 2014
How do I conditionally change the Back Color and Alternate Back Color based on the value of notes.FollowUpDate? I have a continuous form using the following code:
Code:
Private Sub b_all_past_Click()
Me.resultsFrame.SourceObject = "FollowUp_bystaff"
Me.resultsFrame.Form.RecordSource = "SELECT * From notes WHERE (((Notes.FollowUpDate) < Date()+1) And ((Notes.followup_person_id) = GetCurrentUserId()))ORDER BY notes.priority,(notes.FollowUpDate) DESC;" '
End Sub
View 1 Replies
View Related
