I must find a way to generate multiple records that consist of a ticket number as one field, and whether they have been used or not as another. The ticket numbers need to be sequential, with an increase in value of 1 over each previous record.
The ticket numbers will be seven digits long. Users, or their supervisors, must be able to generate a block of ticket numbers, by designating the first and last number, and thus they decide both the ticket number value range, and the number of ticket numbers to be generated at the time.
The difference between the first and last number might be a key to record generation, but then, can you use that to tell the system to add a specific number of records? And, other than appending pre-existing records,can you command the generation of records at all?
I am trying to copy a field multiple times based on a number in an unbound field on a form. As well attach a consecutive number base on the number enter in the unbound text box. Like this [Table1] . [Field 1]
A70-3886-01 A70-7003-01
copy this many times unbound text box on form in to 3
HiI have a form where I am entering lot of records where only the date is different . In order to shorten this I need a querry that will (activated with a button on the same form) generate records in some other table (or in the same) with those records entered in the form and will have a field where will be put single dates in timeframe of starting and end date that I can enter in the form instead of single date.Thanks in advance for your time!
This probably is a very stupid question but I am a new user and have been pulling my hair out for a week over this. I have created a report and linked it to my table but it generates a list of all of the values in my table on the report. What I want is a way to generate a report page for every row in my database table grouped on a key record that is unique to each data row. If anyone has any advice it will be much appreciated, thanks.
I have a Sales Order form that is used to input sales orders. the main form is comprised of customer and shipping information that is created from a query based on a customerOrder table while the subform is comprised of product information that is created from a query based mainly on the CustomerOrderDetail table.
I need to be able to put multiple products in the subform as a customer can order many items, when I save and close the form I look in the customer order table and see only one record for that sales order while seeing all the records in the detail table for all the products ordered for that sales order as I should but the problem I run into is the query that I have for the sales order shows multiple records for every product that was ordered on that sales order and shows up multiple times in my sales order maintenance form when I only want to see the one. if I change the query to a total query that fixes that problem and only see one copy of the sales order but makes the forms un updateable which is no good.
I cant figure out what I did to make this happen as I have a purchase order form that is setup the same but doesn't have this problem and works great, no matter how many items I have in the subform I only see one purchase order in the query and in the maintenance form.
i want to have a position field that will say which position the runner finished in. This is complecated as there are many different races.
At the moment the best thing ive been able to do is set up a query with a parameter on the race no. then the time taken is put in order and i have to manually put in the positions.
Manual is bad!
does any one have any idea about how i could make this automatic.
So I've read a good 100 postings or so so far on the subject of how generating a unique identifier through any other means than an autonumber can be dangerous. But I can't resist the opportunity to simply ask if there is a way to make this possible, if only through a calculated field...
In my single user database, I have an employment table with an autonumber [EmploymentID] field as its primary key. The employment table exists in a one-to-many relationship with it's child, the income table where each form of income is recorded as it is recieved so that one form of employment (or employer) can produce many unique forms of income (like weekly paycheques).
I would like to be able to generate an identifier for the income table that is a combination of the parent [EmploymentID] field and an increasing number to produce something that looks like this:
1006789-001 -| 1006789-002 -|--> Same EmploymentID 1006789-003 -| 1006790-001 ----> New EmploymentID with a reset numeric
I have three fields, One called Key, one called IS and one called BC. I want the table to automatically copy the number from the IS field to the Key field to be used as the primary key. However, this field does not always have a value, when this occurs I want it to take the value from the BC field. Is this possible?
I dont know what to call my problem but it's not much for experts, but too much for noobs like me. I've been trying to generate a random output in one of my forms which will get it's data from one of the tables that i made (Ex. frmForm1 will generate random data from Table1, and the fields are "Name", "Age", "Sex", etc.).. i've tried doing Randomize but it doesn't work. Pls advice, and thanks to whoever will give solution to this..
I need to generate a pre-formatted letter from within MS Access, where the name, address and other information will be pulled from the current record on an Access Form. Basically it is like a welcome letter I could send to any new client I enter into the data base. Since I am doing one letter at the time, mail merge in MS Word is not an option What would be the easiest way to do this? Thanks
I have a small .mdb for invoicing. Till now I had to put invoice number, which had two parts, manually for the first part, for example 001 or 002 and automatically for the second part as Date part. So I had, for example 2006 or 2007. The complete number was, for example 001/2006 etc.
The problem was New Year. Now, all previous invoices from 2006 have 2007 extension and the first parts are not starting form 001 but they continue.
I've searched through the forum looking for what i want to do with no luck so hopefully one of you kind people could help.
I'm setting up a DB that logs in a batch of parts to which i generate a batch no. As the batch progressess through the works when a problem arises we generate fault records (I've set up another table for this and linked the batch no fields together). I want to put a serial no. on this form consisting of 'year' 'batch No.' and ?'Auto number'? From reading posts is it best to put the 3 items in seperate fields (less hassle)
My 2 questions are
1, how do i extract just the year from a date field?
2, This is the main problem. The auto number. If i use auto number to generate a unique number to log the record the number will be massive in no time at all. This is fine as the unique identifier for the record in a table but not for the serial number of the printed report. Idealy I want to have the batch number then a serial number that resets to 1 on a different batch No. For example
21098/1 21098/2 Batch No./serial No. 21098/3
22309/1 22309/2 22309/3
Just some more info this database is a multi user database split front and rear.
I hope i've explained this well enough, many thanks for your help in advance
In my table design view I've set my primary key to auto number and the 'New Values' to random. The problem is, sometimes the numbers generated are too long or negitive. Is there any way I can specify the limit of the numbers to be generated and keep them positive numbers?
I would like to create a unique ID number (currently the Primary Key) for a table in Access 2000.
I am using 4 elements for the current manual entry, and want to automate it after I make one selection on the Combo box.
The elements are: 1: The species of the wood I am entering into the inventory. 2: The last two digits of the current year. 3: A ":" as a separator. 4: A sequential number unique for each species of wood as purchased in the current year. This would reset when the year changes.
For example, I can use the Combo Box to select either Ash, Maple, Myrtle or Elm. When I make the selection, I want the system to automatically generate the rest of the ID code.
If I had the sixth piece of Maple for 2007 I would select Maple in the box, then the generated code would be Maple07:006. The next piece of wood might be the fiftieth piece of Myrtle, so it would be Myrtle07:050. On Jan 1, 2008 the sequence numbers would all reset to 001 - so the first piece of Maple that year would be Maple08:001.
Can this be done? It would help me to avoid the problem of skipping numbers when I loose track of the last piece of the same species I entered. Can I do this with a simply formula macro, or would it need VBA?
And can this code ten be used in the Primary Key for that table - and will I need to re-enter all the 300+ pieces I already have in the table?
I had thought this could be done with a 3 field key - but I'd like to keep it as simple as possible.
im trying to fill a temporary table, with the following SQL. While it successfully builds the table, its not pulling in any values. which are the important part. Can anyone see any problems?
'Dim stDocName As String 'Dim stLinkCriteria As String Dim strSQL As String
strSQL = "SELECT TLOG.keyID, TLOG.Analyst, TLOG.EntryStatus, TLOG.Value, TLOG.FundingStrings, TLOG.Description, TLOG.PurchaseOrder, TLOG.Vendor, TLOG.FiscalYear, LPO.[PO Title], TAMD.Amendment, TAMD.DOR, TAMD.BCD, TAMD.FCD, TAMD.SSD, TAMD.AmdComments, TAMD.CertifiedDocument, TMOD.Modification, TMOD.MSD, TMOD.MRD, TMOD.SLD, TMOD.SCD, TMOD.OSD, TMOD.ModComments, TMOD.ObligationDocument INTO [tblSummery]" strSQL = strSQL & Chr(10) & "FROM ((([lkup PO] AS LPO INNER JOIN [tbl Log] AS TLOG ON LPO.[Purchase Order] = TLOG.PurchaseOrder) INNER JOIN [tbl SupDoc] AS TSUP ON TLOG.keyID = TSUP.keyID) INNER JOIN [tbl AMD] AS TAMD ON TLOG.keyID = TAMD.keyID) INNER JOIN [tbl Mod] AS TMOD ON TLOG.keyID = TMOD.keyID" strSQL = strSQL & Chr(10) & "WHERE TLOG.keyID = " & Me.lstResults.Column(0)
DoCmd.SetWarnings False If tableExists("tblSummery") Then CurrentDb.TableDefs.Delete "tblSummery" DoCmd.RunSQL (strSQL) DoCmd.SetWarnings True
ps i learned my lesson about naming tables with spaces this morning.
Hi, I'm trying to make a database where a Date and a Service Number is entered into a form.
I have 2 buttons, one to automatically enter the Current Date and the second to automatically enter the Service Number.
The service number is incrimented by 1 each time and reset for every day.
eg.
Date Service Number January 1, 2005 1 January 1, 2005 2 January 1, 2005 3 January 2, 2005 1 January 2, 2005 2 January 5, 2005 1 January 5, 2005 2
I already have the button that enters the Current Date. However, I am having trouble with the second button.
How can I code the button so that it searches the table for the Current Date and finds the record with the greatest service number for that date? After this is done, I want to create code that will automatically enter the next service number into the Service Number Textbox on the form.
eg. Using the Example Table Above The date January 5, 2005 is entered into the Date Text Box and the Search Next Service Number button is pressed. The number '3' should show up in the Service Number Textbox
Sorry if its hard to understand, but thanks anyways.
I just realized that I placed this in the wrong forum, Sorry for the inconvenience.
I need to have unique ID for each of my record in my form, and it should be created automatically.It should look like this:
AB060106-1 AB060106-2 AB060107-1 AB060107-2 AB060107-3 AB060108-1 and so on.
Idea is to have two or three letters at the front that never change, then 6 numbers that represent current date (year,month,day), and then incrementing numbers for that day.Whenever day changes, this last number starts from 1 up to 999 and next day it resets to 1 again.
I want to know how to generate a result from a query in a control of a form? For exampel I have made a SQL count function in a query and I want to use this result i a control of a form.
Hi every body. I wonder how i can generate and load a report when a button is clicked on access 2000 form. I be happy if some one show me an example.Thanks
Is there any way to leave a trail or history everytime a record is changed? We have kids in our database that sometimes go to follow-along when our service will not work for them at the present time. These kids go in and out sometimes several times. Since there is no absolute way of knowing, we have to create a new record everytime they come back to our service. I would like to be able to see a history of status changes along with some dates. Is there anyway to do this? If anyone knows, let me know. If not, oh well. Thanks guys.
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause FROM tblMainTWTTPSheet GROUP BY tblMainTWTTPSheet.txtRootCause HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null)) ORDER BY Count(tblMainTWTTPSheet.txtRootCause) DESC;
But What I am trying to do is on a form select dates and generate data in this query. which doesnot happen even if I use the following query.
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause FROM tblMainTWTTPSheet WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate])) GROUP BY tblMainTWTTPSheet.txtRootCause HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));
I'm currently working on a simple inventory system that i keep trying to improve and making it much less simple :(
In my product table I have a min and max level. What I'm trying to achieve is to set up a form where i can select a Supplier, then hit a button (or another form of trigger) and it comes back with all the products for that supplier where we are low on stock ie
Product - Supplier - On Hand - Min - Max Prod1 - Supp1 - 2 - 2 - 4 Prod2 - Supp1 - 4 - 2 - 4 Prod3 - Supp1 - 1 - 2 - 4
So if on my form I selected supplier Supp1 it would automatically fill in order detail for Prod1 & Prod 3 for 2 & 3 units respectively.
Now I'm pretty sure i could do this as a report as the formulas are relatively simple, however i want to pull the data on a form so it creates an actual order with order id etc.
am working on report generation on performance of my department. I need help to develop in Access. If anyone can help me in developing it? i had started with tables. If u have some spare time, please mail me at nrmarthi@hotmail.ca.
I'm building a database for my folks's volkswagen car show (www.texasvwclassic.com) if anyones interested ;)
So I've got a Registrations table and a Classes table. Each person that registers for the show is an entry in the Registrations table, and a field in that table is Class, with a relationship to the Classes table. That is all set up and working fine, but I want to add an EntryNumber field generated at time of checkin, so some VB function I can call when the checkbox is clicked in the form, or a query based off the value of the checkedIn field. I want the EntryNumber field to contain a number like 0503 (CCNN) where CC is the class number (class #5) and NN is a counter of how many people have checked in so far in that class (so 0503 would be the 3rd person to check in for class #5). I can get the class number fine, and I can prolly figure out how to stick em together into another field, but im not sure how to generate the NN part.
In the classes table, each class has a sub-table listing all the registrations that have entered that class. I need like an autonumber field in each of those subtables, but I dont understand how to add something like that.
I hope this makes sense, as always any help is appreciated. =)
