I want to track how many orders a user makes. So every time they place an order it increments 1 after each order.
I currently have the following tables,
Order
OrderDetail
User
Should i create an UserOrderHistory table?
Eventually i want this information to form part of an order number. eg
196(order primary key) AR (UsersInitials) /23 (number of orders User has placed)
I am developing a database for a friend and have all the relationships,
tables, forms, queries and reports working well. I used the orders template to
build this and just made a few changes. I have an orders form with an orders details
subform that works beautifully. There is also customers table, employee table,
products and inventory table. The inventory table has product ID, units in stock and reorder level
as the only fields. I need an easy way to have the orders detail form decrease inventory as
items are ordered and also some sort of way to enter restock. Can anyone offer advice that a
beginner can understand in regard to this?
I wrote an update query that he can run that will subtract the qty ordered from the units on stock
for orders with order date >= whatever date he enters, but if he enters a date he's previously updated it will update those records again. I think this is not a good way to go but the only way I could accomplish. I have him using the same order entry form with customer name Restock to add items to inventory by putting an if then else statement in the before mentioned update query. This just adds to inventory if customer name="Restock" else the qty ordered is inventory units in stock -qty ordered.
I'm sure someone can suggest a better way. A friend mentioned dsum function but can't explain how to apply it in this instance. Thanks so much!!!!
I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.
The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)
We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.
I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.
The check number.
I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.
By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.
I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.
I have a training database that I have set up and utilize to track attendee data and who is registered to which course.I have a course registration table that has a course title field which is a drop down list populated by course titles from the courses table, and a trainee id field.
For whatever reason, when I view reports or queries that draw from this table, access will make changes to the first record in the course registration table course title field i.e. it will take whoever is in the first record, and change the course they were registered to.
Started a new thread: Rebuilt database and still working with auto number for multi user form. The code I have works for single user but not multi user. Also if someone opens the form and closes without adding anything to the form the number is used with blank record.
This is what I have thus far:
Private Sub Form_Current() Me.[PO] = Nz(DMax("[PO]", "tblPurchaseOrder"), 0) + 1
End Sub
where I can find VBA for (Tracking User LogIn/LogOut) I have tried researching to no avail.I just need a simple code to track users logging date & time in and out of my DB.When they click on the icon on the desktop id opens the DB to login screen with username & password then into the DB.
View 9 Replies View RelatedI have a database where I have 3 tables. first is client data, second orders data and third the products data.
What I would like, is to have a button on my form that after selecting from a combobox a product, it wil enter it in the tabel orders in the next available free record (row). I created a button and the combobox in the client form. The Products table has also the price per unit in it. It should take the whole row of data and put it in the Orders table.
Is there a way to do this? I guess it needs to be done with vba, but not sure.
I currently have a database where new records can be entered in a "New Part" Form and Records can be viewed(but not altered) in a "Part Search" Form. The forms are the same except one is allowed to edit. I have also created another form where I am trying to search the "Part Search" Form via a tracking number. I want to have a text box where users enter a tracking number then click the button to take them to the record on the "Part Search" Form.
View 2 Replies View Related Single products night be thinks like 2-inch wheel or 1 inch wheel in a choice of colours, front axle, back axle etc that can be ordered as distinct products
A kit is distinct product in its own right consisting of a bundled set of other single product components.
Question 1: I would quite like to have single products and kits use the same ProductID index but I think Single products and kits need to be on separate tables in order that the product components included in a kit can be defined. So what I would like to happen is that if I open a kit form on my database in order to define a new kit that it would somehow look at the index for the single product table and take the next assigned ProductID. I am not sure of the best design approach with regards to tables and relationships needed to enable this.
Question 2: When associating the single product components of a kit with the kit record it will be necessary to select from the entire list or single product components as all single products are potentially valid kit components. If I were to do this using the tbl_single_products as a lookup table it would be a very long lookup list the user would have to scroll through. Are there any functions or techniques that would allow me to dynamically search a lookup list as the user enters text e.g. The user enters "T" the list pointer goes to entries beginning with "T" the user enters "e" the list pointer goes to lookup list entries beginning in "Te" etc.
Question 3: Is there anyway apart from autonumber to create an automatically incrementing index of your choice for use in tables e.g. PROD001, PROD002 etc...
how do i get form to generate a new order ID for the same customer in my orders table
View 2 Replies View RelatedI have a Client database table in Access. I now need to add a simple order table (related) to the client table. I have a client ID field set to autonumber in the client table. As I start to create the orders table I'm not sure how to link the two so that I'm not entering data twice and have assurance that they are tied together.
View 4 Replies View RelatedI have a DB thats working fine, but (there always is a but) I now need to do something a little bit different. I need to be able to track my originators and who they have brought into the company. So I will have an originator and many down-line originators, and these people that come in down-line will have people coming in down-line from them and so forth and so on. I have a table that has all originator info and originatorID, but will I need another table or can I just add to the exisisting one. Here is the table structure:
Originators:
-------------
OriginatorID -autoNumber
FirstName - Text
LastName- Text
OrigSSN- Text
Address- Text
City- Text
State- Text
PostalCode- Text
HomePhone- Text
WorkPhone- Text
CellPhone- Text
FaxPhone- Text
EmailName- Text
BirthDate- Date/time
Level- Text
If more info is needed please let me Know.. Thanks in advance.
Scott
I'm trying to create a database that is going to be used to deliver some work to several of our users. Each time they open a specific form they'll be delivered a job.
The tables are organized somewhat like this
tblQUEUE
PK_ID_Job(AUTO_NUMBER)| JOB_NUMBER(INT)|LOCKED(BOOL)
tblWorkOrders (FK_ID_JOB (INDEXED DUPLICATES ALLOWED))
FK_ID_Job(LONG)|Name(STR)|Date(DATE)|
Now it works like this, the user gets an ID_JOB from queue
In the form they get all the all the work orders with that ID_JOB, the thing is i'm getting users with same duplicate orders cause i can't update the locked efficiently.
Regarding the users, the database is split, multi-user, with >30 simultaneously
I'm been trying to use dao.recordset, with transactions to try and reduce the duplicate orders.
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
I am trying to deconstruct a database that has several linked tables. Is there a way to find the source database for the linked table from within the original database?
View 3 Replies View RelatedI'm looking for some input on table design.
I work in a lab and am looking to track data associated with samples on PCR plates usually in 2 formats
96 well (8x12) rows A-H and Columns 1-12
384 well (16-24) row A-P and Columns 1-24
each well will have at least 3 pieces of information associated with it:Sample
Assay
Qulaity Score
but more may be added as I progress with the design.
field such as run name run date and operator for each plate would also be needed
What would be the best way to structure the table?
Obviously listing well A1, sample in A1, QA for A1 etc etc as fields wouldn't work as theres a 255 field limit and 3*96 = 288 and thats before we even get to 384
I could have a table for each run but that feels like bad design to me, as if you wanted to look at quality trends across time you would need to query multiple tables and query the date associated with each to even knwo which table to look in
A table for each well along with its QA score, assay and a look up field linking it to a list of plates, which would contain the run date etc. (this table listing runs is already present in my database)
Some background on current structure
Tables
Booking - contains a list of worksheet numbers along with info such as submitter, and conditions for the test such as assay
Experiment - expirment number conditions and associated DNAs
DNA - a list of dna samples
Plate - name of plate and info such as run date and associated worksheets
I am looking to track the salary information for my small firm over a number of years. Does it make sense to create a table with multiple fields that would be structured like this (2011401(k), 2011Medical, 2011 LifeInsurance, 2012401(k), 2012Medical, 2012LifeInsurance, etc) or is there a better way to tackle this?
View 14 Replies View RelatedIm trying to create a database to track who worked on what item and on what date, with four tasks required to complete the item.To give an example:I have a factory that builds Lego models, for each model there are four steps:
1)Unbox Parts
2)Sort Parts
3)Build
4)Check build
Any employee can work on any task, and multiple tasks can be done by the same employee.Having a completed item table with a field for each task, and a date for each task, with one to many relationships from employee id to each task type. This failed as I needed many to many, so I made a junction table to link them, and this is where I get a little muddled.
Do I need a table of task types, then a table tracking each task to feed into the item table, or is there a simple solution I'm overlooking?
Hi All,
I have been trying to improve a form used to add/edit names in a mailing list. What I want to do is check the database for possible duplicate names and alert the user to the fact that their new person may already be in the db.
I used the following VBA code in the After Update event as follows:
Private Sub txtLastName_AfterUpdate()
On Error Resume Next
Dim strLastName As String
Dim NbrNames As Variant
Dim strNameSQL As String
txtLastName = Trim(txtLastName)
If Not Mid(txtLastName, 1, 1) = "=" Then
txtLastName = StrConv(txtLastName, vbProperCase)
Else
txtLastName = Mid(txtLastName, 2, 999)
End If
' Test for duplicate last name in data base
NbrNames = DCount("[tblMemberListings].[mlLastName]", "tblMemberListings", "[tblMemberListings].[mlLastName]= '" & txtLastName & " '")
If NbrNames > 0 Then
strNameSQL = "SELECT ALL tblMemberListings.mlID, tblMemberListings.mlLastName, tblMemberListings.mlFirstName, tblMemberListings.mlSpouseSO, tblMemberListings.mlAddress " & _
"FROM tblMemberListings " & _
"WHERE ((tblMemberListings.mlLastName) LIKE '" & txtLastName & "*') " & _
"ORDER BY tblMemberListings.mlLastName, tblMemberListings.mlFirstName, tblMemberListings.mlSpouseSO;"
lstDuplicateNames.RowSource = strNameSQL
Beep
lstDuplicateNames.Visible = True
End If
End Sub
This generates a useful list of potential persons already in the db. The user then has the choice of continuing to add the new name and address or they should be able to select one of the names in the listbox and refresh the form with that person's data for possible modification.
I currently have these two Event Procedures in place:
Private Sub lstDuplicateNames_AfterUpdate()
On Error Resume Next
'MsgBox "Sub lstDuplicateNames_Click value = " & lstDuplicateNames.Column(0)
If Not IsNull(lstDuplicateNames.Column(0)) Then
' User chooses and existing record
Me.RecordsetClone.FindFirst "[mlID] = " & lstDuplicateNames.Column(0)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
Me.lstDuplicateNames.Visible = False
Me.Refresh
End If
End Sub
and
Private Sub lstDuplicateNames_Click()
Me.txtHidden.SetFocus
Me.lstDuplicateNames.Visible = False
End Sub
The problem is that when one clicks on one of the listed names just two things happen:
1 - a fine dotted box outline appears around the *first* record in the listbox
2 - the MS Exclamation wav sound is generated each time the mouse button is clicked. :confused:
Short of enrolling in a local junior college that offers Access 101 what can I do to get this addition working? I'm also not sure that my code to refresh the form is going to work, but right now I don't ever get there. ;)
Oh, this problem is occurring in MS Access 2000.
Any help would be greatly appreciated by this NOOB.
Divit11
I am developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.
I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
tblEvents
EventID
EventName
tblClaimEvents
ClaimEventID
fkClaimID
fkEventID
EventDate
With this table structure there is a many-to-many relationship between Claims and Events, but, there will only be one of each event per claim. Is there a better way to set up the tables to enforce a 1-to-1 relationship?
I was planning on creating a form for the Claim table with a subform for the Events joined on ClaimID and fkClaimID, but that would allow users to create more than one set of Events per claim, if that makes sense.
I've created a report which i'm exporting to PDF,but when I open the PDF I can see that some characters are replaced by a question-mark in a box.
for example : RMA Number: 5002408...here the empty characters after number and the : are replaced by a questionmark in a box...if I export to other programs i don't have this problem..
I'm creating an attendance tracking system, the attendance system needs to : Allow an instructor to enroll students into one or more classes, and then add attendance by date to each class(es). The problem that I'm having is, attendance is tracked by the number of hours that a student attends class. For instance, if class1 is in session from 8a.m. - 12p.m. student1 may only stay 2.50 hours of that class and student2 may stay 3 hours. The teacher needs to be able to add attendance to class1 for July 12, 2013 and add hours for each student enrolled in this class session who attended on that day. I have the following so far, minus the attendance:
Students -This is just the basic demographic information that instructors will need to enter on students.
StudentID (PK)
StudentLastName
StudentFirstName
DateOfEnrollment
LevelOfStudy
DateOfBirth
Location - These are the locations in which our students can attend classes
LocationID (PK)
LocationName
County
City
LocationPhoneNumber
LocationFaxNumber
Classes - These are the classes that our students can attend.
CRNNo (PK)
ClassName
LocationID
InstructorID
Instructor - These are the instructors that teach our classes.
InstructorID (PK)
InstructorLastName
InstructorFirstName
InstructorEmailAddress
Enrollment - This table enrolls the student into a class
StudentID(PK)
CRNNo (PK)
LocationID (PK)
I use Access 2013. Is there an easy way to do the following: I have a contract that starts on eg 01/07/2015. Tenant has to pay 100 each month. Is there a way to create a query/table/... where access automatically makes a due date? EG: Joe needs to pay me 100 each month, starting 01/01/2015 until 31/12/2018.
This means:
01/01/2015 - due 100 from Joe
01/02/2015 - due 100 from Joe
...
01/12/2018 - due 100 from Joe
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
View 8 Replies View RelatedIs it possible to have the user enter the number of top records to choose in a query?
I know that you can enter a number or percentage in the Top Values property of the query but I would like the user to be able to enter this number each time they run the query since it may be different each time and not always, say, the top 10 records.
What is the best way to allocate a record number to the end user?
I have had problems where users have come back to me saying that both of them created the same record number at the same time and I am not sure why that is happening.
To be certain, I am using INSERT rather than UPDATE which was my initial concern (an update would just over write the previous record)
How can we check the last value, uniquely assign that value to a person at the point of creating the record and ensure that no one else can take it...
Is it possible that a laggy server would cause these issues? (as the code executes roughly the same time as another person also executes code, they overlap?)