How to extract or calculate the number of records in a sequence - e.g. in a table/query ordered by ID and date, the number of consecutive records by date for a given ID that have a value >= 50 in a given field. Is there a way of doing this purely within a query, or would I have to resort to a VBA loop through a recordset and keep a count of the consecutive records that match that criteria?
I have two tables - one with parent Records, the other with child records. The ID links the two tables .I want to add a sequence number on the child table which resets at each ID change. What would be the best way to accomplish this?
Code: PC_ID ID Name Address
P 1 Parent1 Address1 P 2 Parent2 Address1
PC_ID ID Name Address SEQ
C 1 Child1 Address1 1 C 1 Child2 Address1 2 C 1 Child3 Address1 3 C 1 Child3 Address1 4 C 2 Child1 Address1 1 C 2 Child2 Address1 2 C 2 Child3 Address1 3
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.
I have a set of transactions with a Unique ID field and a date. I want to add a field based that gives me the sequence of events for each Unique ID in order of when it happened.
For example, if customer X has 6 transactions, the sequence field would have a number (1-6) in each record that corresponds to the order in which those transactions occurred. The first transaction would have the number 1, the second would have the number 2 and so on.
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
I am trying to make a crosstab query to filter my records from my table.
Here is the scenario.
I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.
Now I make a crosstab query and here is the syntax.
Code: TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank] SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] FROM [TBL Qualification Data India] GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] PIVOT [TBL Qualification Data India].Period;
This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.
Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.
Code:
TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q] SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] FROM [TBL Qualification Data India] GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] PIVOT [TBL Qualification Data India].Period;
Now after this how to make a select query to show the data from these two queries.
I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.
Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.
To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.
I have a form which allows the user to add new records to a table. After the user had entered all the information into the form, they click a command button to add the record. In addition to adding the new record, my command button runs an query which is supposed to generate a random number between 1 & 1,000,000,000 and update the record ID field with that number.
Here is the formula I have been using in the "update To" now of my query: Int((1000000000-1+1)*Rnd()+1)
My problem is that I keep getting duplicates. You would think that the chances of getting a duplicate number would be pretty small with this large of a range, but I get a duplicate almost every time.
I have tried indexing (No duplicates) the field in the table, but that did not work. When my query generated a duplicate number, the record was just not added to the table.
I also tried a two step approach: 1-Make a table of all in use record ID numbers from my table (tblIdNo) 2-Update new record with a random number that is not in tblIdNo
This was a no-go too
How to build an update query that will update each new record added to the table with a random number between 1 & 1,000,000,000 without any duplicates? This seems like it should be so simple, and I am starting to get really frustrated.
I would prefer to accomplish this through a query/queries (if possible) rather than with 100 lines of code. This database is not for me, it's for another group, and the individuals in this group are totally freaked out by code.
The title probably doesn't give an idea of what I need, so here goes.
We currently have reference #'s for our bills that include the date requested in mmddyy format, the first 3 letters of the customer, the initials of the order taker and finally a sequential number to show the sumber of bills that day.
example: 120705SEAGMM02 2nd order taken by GMM for Seagate on 12/7 120705SEARLH01 1st order taken by RLH for Seagate on 12/7
I want to generate this number automatically based on the date entered and the initials given of the user. We only deal with one customer at our desk so that will always be "SEA".
I have a query that generates the first portion (date, customer, and initials):
SELECT Format([REQDATE],"mm") AS [Month], Format([REQDATE],"dd") AS [Day], Format([REQDATE],"yy") AS [Year], Format([REQDATE],"mmddyy") AS [Both], [Both] & "SEA" & [PickUpReqData]![INITIALS] AS REFNO FROM PickUpReqData;
But I can't seem to get my head around the part of generating the number. I know I had done this in a database I created 2 jobs ago but all my files were flooded out in Katrina. (I have since relocated to Atlanta, although I'm not looking for sympathy. But I will take what I can get!)
Anything to point me in the right direction will be appreciated. I am looking to have this on a form and feed the REFNO field in the PickUpReqData table.
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?
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?
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.
I have a table in a database with 7 columns. The data is sorted by Date1 in descending order.
For each pid I want to put the sequence numbers First record has two conditions If string 2 is null then start numbering from sequence1 If string 2 is not null then start numbering from sequence2 If string 2 = string1 then Sequence1 = 0 Second record has two conditions Number sequence2 with the value 2 or 3 depending on the line one If string 2 = string1 then Sequence1 = 0 Else Sequence1 = next number Same condition for the rest of records
I have a field that contains 12 numbers. I need to replace the middle four numbers with a character so that the entire number is not readable. How do I do that?
I have a Primary Key field named Member ID(Number format) in a Table named Mail List. The field is populated with existing ID numbers and my need is to use my Member Entry Form to increment by 1 from the last entry.
I have gleaned from other references that this can easily be achieved by the following entry in the Default Value of the property sheet for the relevant field;
=DMax("Member ID","Mail List")+1
The Form saves without error but when I attempt to add a new record in the entry form, the ID Number reads "#Error".
I do not want to use AutoNumber to generate my Stock Control Number. I need to somehow automatically generate a number so that when I enter data and then make an error it does not mess up the numbering system like AutoNumber does.
I am a novice who is will to try to do what it takes to get this going properly. I cant find anything online relating to Access 2010.
I am trying to get the max of an integer field if the ProjectNumber field is equal to the ProjectNumber field in the current record. I have used code similar to the one below before but it was on a date field, then add one.
I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.
Staff name etc is in tblStaff Staff shift details are in tblShifts linked via staffID
tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.
The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.
I have for some time now been fooling around with a database to keep track of several clinical trials Basicallly I have made 4 tables..2 which defines the project ( Project and visist), 1 that populates the projects ( patients) and 1 that bind the patients visits to specific dates ( appointment)
The trouble is that im Lazy :D and therfore dont want to enter the appointment data for all patients one at a time each time we get a new guniea pig since all thats diffferent is the dates he/she has to show up. Im hoping for some way to fill up my appointment table based on patientID and projectID alone.
SO this is my 4 tables... ( some unimportant fields left out)
The idea is to create a project and then add a set amount of visits to the project ( days when they have to come and get a blood sample taken) All patients involved in the project is likewise added in the Patients table and joined to a project.
Since the patients all have to come to a fixed amount of visits ( determined by the project) I was thinking I could make a form where I pick a patient from a combobox (select projectID,PatientID,PatientName) and then automatically generate a record for each visit in my Visit table where Visits.ProjectID = cboPickPatient.ProjectID and show them in a subform so I can enter the date they have to come. I hope that makes sense. ( preferably I would like to add the date also by just entering the first Visit date and the using the DaysToNextVisit to make the other visit dates or each patient, but thats a whole other problem :o )
My instinct tells me I have to do some sort of loop code but I havent figured out the details.
Can any of you guys lead me in the right direction before I loose all my hair in frustation??
This seems to me to be a pretty simple problem-making a recordset based on a projectID and the visits involved in that project but im just out of ideas:confused:
