I am assigned to create a customer quote database for our company. Now it is in excel and already have 40,000 records and increasing average 25 records everyday. The way they want to send quotes are:
- The number of items in the quote are varied from 1 o 15.
- Each item has six different prices, depends on the order qty and want to mention in different adjusent lines.
- Each item has different delivery time depend on order qty.
- Company's sales Terms and conditions need to be printed at the end of each quote in separate page. (two full pages)
How do I create the table to accommodate all these fields? Is it possible to accommodate all items/sub items in one table under one qutoe id. I have customer table, product table etc ready and little confused to create the Quote table.
A friend pointed me in the direction of this site, and I sure hope someone can help. I need to build databases that will primarily be used for creating sales quotes. I know my way around, a little bit, but I am having trouble building this database. Would anyone be interested in helping me?
i need to create a customer database, which i would like to be able to generate invoices from, nothing fancy or complicated, just name, address phone number etc. with a button to put this detail into a template word .doc file.
i would also like to be able to import all names and addresses at the end of the year to send a christmas card maybe or promotional e-mail.
how difficult is this to do? i kinda remember doing something similar at school years ago but can't remember how i did it.
any help/information would be greatly appreciated.
create a check on a feild(customer id) of customer table in MS Access , as "Customer ID is of 8 characters, the first 4 are alphabets and the last 4 are digits"
I have a Table Sales - that lists customer and order information
Most of the customers are new - is there a way to populate Customer Name Table with a new entry each time a new customer is input into the Sales Tale
(Without having to add the custome to Customer Name Table - then going to Sales Order) OR Failing that - is there a way to use a list box that points to Customer Name Table (and if it doesn't find the one you want, will allow you toadd a new record) In the SAME form as you use to add to Sales Table.
So: Date (textbox linking to form.Sales) Customer Name (List Box linking to form.CustomerName + adding the info to form.Sale)
I am having problems with a customer care type database. I used VBAUNBOUND to update information from one table onto a form.
If you select the combo box on the form, it reads the info from the one table, and updates 3 other text boxes in the form, but it doesn't update the main table in the database.
Tables: Contacts - Customer problem details tblcustomer - Branch details
Query: qurycustlisting - Reads info from tblcustomer (used in form Contacts)
Form: Contacts - Manually input details. Select branch name in combo box (reads from query - qurycustlisting) and then updates the rest of the branch details on the form. But the info insn't updated into Table Contacts
Hi all, just wondered if anyone would have an idea of the best way to implement this? I have a customer database, the relationships are below, and currently have done searching by town/city, street, purchase date, and also searching by area. But now the person i created it for wants to be able to search by 'groups of areas'. He has written how he would like to be able to search and they often include 3, 4, or 5 areas grouped together as one, and in only one or two instances he has included a small town aswell as a few areas.
Basically, it's been a while since i actually made the database (a year or so) and have not been using access much at all, so i'm having a bit of trouble working out how i'm gonna go about it and would like some idea's :D . I'm sure once i get back into the swing of it i'll be ok, but i feel like a newbie again at the moment :o :rolleyes:
I have a table with all patient information ~1000 patients. However, not all patients will be case managed. I have another table where I will add all the patients that need to be case managed ~ 100-300 patients. I'd like to be able to autopopulate the 2nd form (the case managed patients) by selecting the Patient ID.
I'd like the patient ID to pull the patient info from the 1st table and populate it into the 2nd table.
Same for physicians. I have a physician file with around 2000 physicians. I'd like to type in the physician ID in my form and have it pull all the demographic information for the physician and populate those fields...
Any thoughts? I am sick of cut/paste and retyping all this info that could easily be pulled for seperate existing tables.
I have a DB set up with a debtors table (Customers) and a Customer Contacts table, I was thinking of combining these two tables into one.Most of the Debtors are companies, but a few are actual people. With the current setup, i need to have both a debtor and a customer in two different tables, but with exactly the same data.
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
I have a nice little app that works very well except that the code is not tolerant of single quotes in the user's data. My problem is that VB uses double quotes for its literals and SQL uses single quotes so where does that leave me?
The user has entered a comment into a data field and I am now adding this to the record on the database. I don't know if the user's data contains a quote. If it doesn't then all is well. If it does then, crash.
Here is a code sample...
Dim SQLStr As String, Ret As Integer SQLStr = " UPDATE MeetingRole " _ & " SET MeetingRole.Comment = '" & [Forms]![Comment]!Text2.Value _ & "' WHERE MeetingRole.DateOfMeeting = '" & [Forms]![MeetingStatus]![List0] _ & "' ;"
However, i just changed the formulaID to TEXT in my table.
So I think i need different quotes somewhere at the criteria end of this qry calc because FormulaID is now TEXT. how do i fix this at the end of the field? thanks
I have a combo box on a form that simply selects a record from a 2-table query. My problem is that the data is Book Titles, which can contain both quotes and apostrophes, sometimes in the same title. other than eliminating these characters from the records, how do I make the simple vba code for the box work for either quotes or apostrophes in a record? The box works fine as long as the record does not contain these characters. (I'm a big vba rookie by the way, and this code was created by the box wizard.) Using Access 2003, Here is the code for the box:
Private Sub cboSelectBook_AfterUpdate() ' Find the record that matches the control. Dim rs As Object
Set rs = Me.Recordset.Clone rs.FindFirst "[BookTitle] = '" & Me![cboSelectBook] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub
I have a button that generates text according to information in different fields. The text has multiple lines and when I select the button to generate the text it puts all the lines together when I need it to do a new paragraph after each line.
Sample: Me.GeneratedCode = "This is a test for " & Me.ServerName & "." & _ "The server " & Me.TestResults & " the test."
The above shows up in one continues line. This is a test for shankserver. The server Passed the test.
I what it to look like this
This is a test for shankserver. The server Passed the test.
Could someone tell me if I need to break this select statement down? I'm running into problems with the DMax statement since it requires double quotes around it's arguments. When the parser hits the first argument, it closes the string like it's supposed to, but not the way I want it to.
In essence, the query returns 4 fields: unique ID, week number, computed score (TotSum), and the computed handicap (which is half the difference between your score and the high scorer):
Quote: strSQL = "SELECT tblRoster.HEDR, tblScores.WeekNo, " & _ "[A1T1]+[A1T2]+[A1T3]+[A2T1]+[A2T2]+[A2T3]+[A3T1]+[A3T2]+[A3T3] AS TotSum, " & _ "Round(((DMax("[TotSum]", "[qryHandicap]") - [TotSum]) / 2) + 0.1) AS Handicap " & _ "FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _ "WHERE (((tblScores.WeekNo) = " & inpWeekNum & ") And (TotSum > 0)) " & _ "ORDER BY TotSum DESC;"
What would the proper syntax be and/or is there a better way to obtain this information. After acquired, it is being salted away in a table which is recomputed after each competition.
Thanks for the input.
-Brian.
p.s. I know storing computed fields is a big no-no, but if you do not compete in a given week, the handicap from the last time you participated is used. This table will store the participants handicap as well as the last competition they were in. It seemed easier this way.
On an existing DB i have set a default value for a field. The customer has now asked that they be able to change this default. I thought, hey, no big problem. They have SuperUsers, so i included a little pop up form that lists all the current valid values for that field (from a look up table). If the default value needs to change a superuser can access this and select the new default. That works fine. The trouble comes when setting the default value on a form.
I figured a simple bit of VBA in the OnOpen event - Me.cmbTypes.Value = Me.cmbTypes.DefaultValue - should work. However the value it returns from the default is ""xxxx"" and as such it is read as "". I can work around it by removing the extra " " but WHY does it do this? Would it be better to set this up as a custom property of the DB?
My db contains an unlinked form that is used to append records to 1-3 separate tables (tests to see if master records exists, appends if needed, moves on to child records). One of the fields is a text field meant for notes. The VBA errors out whenever this text contains apostrophes or quotes.
Is there a simple way to recall the string field with quotes and apostrophes intact and append it, or will I have to validate and remove them from the string?
I'm having problems with quotation marks in a sql statement. The string is an array separated by a semicolon.
120/80;70;5'6";125
this string represents patient vitals. I'm using the string to update a record. But I get hung up with the quotation mark.
I've tried: 120/80;70;5''6'"';125 which is a enclosing the quotation mark with apostrophies, but this does not seem to work. The sql still gets hung up. My sql statment looks something like:
original string: 120/80;70;5'6";125
strPreOpVits = "120/80;70;5''6'"';125"
mysql = "UPDATE mytable SET PreOpVits = '" & strPreOpVits & "' " & _ "WHERE nID = " & myRecID
docmd.runsql mysql
I've narrowed it down to the quotation marks and I'm unsure how to handle these. I get a runtime 3075 - Syntax Error.
Here is the code that I use to convert the original string
Public Function FixQuotesInSql(strToFix As String) Dim lgth, y As Long Dim strTemp, char2Add As Variant 'This routine fixes the use of apostrophe and quotation marks in an SQL sequence 'If the apostrophe is at the beginning or end of the string it replaces with 3 x "'" or "'''" 'If in the middle of the string then replaces with 2 x "'" or "''"
I heard from a number of people that this is the place to come for Microsoft Access related help.
So here is my question and I hope someone can help me understand access a little bit better.
I am trying to create a database for a job I am doing. THere are 700+ convenience stores that we (4 inspectors) inspect every three months.
We have been told to do an ADA compliance survey when we visit these stores.
The ADA checklist that we have is about 78 questions and my manager wants me to create an ADA database for this information.
So I am in need of help on how to create a database that allows me to input the answers of this ADA checklist for each store inspected.
I then would like to be able to pull all that information into a report that I could say of the 700+ stores, 50% passed all questions, 25% passed only questions 1-??, etc. etc.
I can upload the two files: The ADA checklist and our Store list if anyone would like to help me out!
The people table contains: surname given name email - primary key phone number course end year
Employment is the linking table it contains: Role start year end year employer email - foreign key same as email(above) org_url - foreign key same as url (below
Organisation
orgname add1 add2 add3 url - primary key
All I want to do is have three tables - enter a persons details in one form