Hi,
I am starting to design a Quoting database and would like some advice on how to set up my tables.
I would like to record all quotes received with all relating sales, purchasing and despatch/invoice details included.
I am thinking of having following tables – Am I on the right track ????
Customer Table - With all details relating to customer
Customer ID
Name
Address
Phone
Contact
Products Table – With all details for quoted products
Product ID
Part Nbr
Description
Price
UOI
Quote Table – With all details Specific to Quote
Quote ID
Customer ID
Qty
Sales Price
Product ID
Date Received
Date Required
Dated Quoted
Purchase Table – With all Purchase details
Purchase ID
Purchase Order
Supplier ID
Date Ordered
Date Received
Sales Table – With all final sales / Invoice details
Sales ID
Product ID
Customer Purchase Order
Po Date
Invoice date
Invoice No
Total Invoice Amount
Paid Date
Despatch Details
Payment Terms
Delivery Terms
Please let me know if I am on right track or any advice you could give would be appreciated.
Tks
I'm trying to develop an application to help me generate quotes. Right now I have a spreadsheet with all of our products and their prices. When I need to generate a quote I have to find each line item on the spreadsheet, manually type in the item and its price in a quoting spreadsheet, and repeat until I've entered all the items. This is tedious and error-prone.
I'm a software engineer with experience with relational databases but have hardly any experience with Access, and I'm hoping that it is a good tool for this application. I'm thinking that I can create a table with all of our products and their prices, and to generate a quote I simply enter the quantity of each item in a form and have a report generate the quote. I'm not sure how to go about designing this though. I've searched far and wide for a template or tutorial on a similar problem but have come up short. Can you guys help me get started?
I work for manufacture company, we tried to upgrade to a new CRM system, 'Netsuite' but upper management decided to quit the project because it required more customizations then previously expected...
So on a daily basis I have to create quotes using excel.I look at the prices printed out on paper and type in each option and price for each unit. I've had to do this for 8 months and the company doesn't seem very eager to change this process.
I attempted to build a quoting system in Excel, using simple lookup and if statements. But what I need in my quotes was machine option filtering. If I select a model I need all options filtered based on the model selected. Each machine has 9 different options.
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.
Is it possible to access a table in one Database (Database A) from a separate Database (Database B) and if so how. I should clarify that this is an Access Database.
I am assisting my employer by combining two databases into one. Both databases have the same field "structure" but the data differs. When creating my append query.
I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:
Although this is pretty self-explanatory, here is a key for reference:
Code: Private Sub cmdAddMet_Click() Dim DB As DAO.Database Dim RS As DAO.Recordset Dim strSQL As String Dim i As Integer Set DB = CurrentDb
[Code] .....
This works great but I would like to be able to pull in data from another database based off of Me.lstFacilities.Column(1, i) which is the FACILITY_ID field and is located in the other database's table. I thought about adding in another string variable(strSQL1) and opening up a separate recordset and database:
Code: Set DB1 = OpenDatabase("serverotherdb.accdb") strSQL1 = "SELECT [FieldName] FROM [tblOtherDatabase] IN 'serverotherdb.accdb'" Set RS1 = DB1.OpenRecordset(strSQL1)
However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:
Code: For i = 0 To lstFacilities.ListCount - 1 If lstFacilities.Selected(i) = True Then RS.AddNew RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i) RS!MEASUREMENT_PERIOD = Me.cboMeasure
I just want to get started on this project. I have to have a minimum of 5 significant tables. A significant table has more than 2 attributes/columns.
All tables will be normalized to at least third normal form to prevent update anomalies. All attributes must have captions and descriptions in the table design form. Use predefined input masks where appropriate e.g. phone numbers, social security numbers, postal codes, etc.
Include at least 2 user defined input masks that you create e.g. an input mask to insure the letters in a state code are all capatilized. There will be at least 2 lookup tables. Lookups can be done using one or more of the 5 significant tables or you can create a table just used for looking up values (eg State Code Lookup table) that will be entered in other tables in your database.
All primary key fields will be required entries. Non key fields containing the min. essential data to make each record meaningful will be required entries.
All initial entry tables will have validation criteria included to insure the quality, accuracy, and correct format of the data.
Let me know if you can help me out at all. Thanks.
I need the tables to be related to CARS, any ideas, please let me know, thank you.
Is it possible to take data with all the same fields and put it into another database with the exact same fields, etc. ??? Thanks for your help in advance!!
I wish to implement database/table that includes 2 columns: serial number and status field. The serial number should consist of a 9 digits. The status field should contain one of three words (e.g. monkey, donkey and funky :)).
The databse/table should be empty at first and grow as the users insets new rows at will.
* The application should let the user the option either to add or remove new lines at any time. * It should provide the option of seraching a row by its serial number. * and should produce cross sections of the databse/table when asked to.
I'm new to access and don't really now it capabilities. Could any one tell me if this is the right tool? In addition a direction to a source code/lead would be welcome.
I have searched through a number of threads trying to find a solution to this problem that I am having without luck, so I figured I would post a new thread.
I have an application in access that pulls data from a SQL database. Users can already delete a record out of the database.
The problem comes when I try to give them the ability to update the information in a record.
First, I went to the tables listing and double clicked on the link table that contains my information.
Once that window opens up, I have tried to change a piece of data on this table, but it give me a 'Write Conflict' dialog box stating 'this record has been changed by another user since you started editing it. If you save this record, you will overwrite the changes the other user made.'
This is the same error if a user changes any information on the form and then tries to move to the next record.
What is set up incorrectly that is causing this problem?
I am building a database for an activity society. There are 2 courses in each season, each course has 10 sessions. Students can be old (returning student)and new.
What we want to achieve is to check student information, the student's attendance situation, how many students in each session, etc.
The table I designed is:
1. Student Detail (student info) 2. Spring 2006 Sunday Course (student ID, payment, each session attendance...) 3. Spring 2006 Tuesday Course 4. Summer 2006 Sunday Course 5. Summer 2006 Tuesday Course . . . (each new course has a new table)
The problem is for each new course will need to add a new table. I just want to know if there is a better way to manage the data. Thanks for you help!
So I split my database into a front and back end, now i wanted to add a table to the database. I created the table in the backend but I am not sure how to make it appear in the front end. I am using access 2010. I tried the link table manager but the table I created doesn't appear there.
Access 2000: How can I populate a two field table (1. Table Name 2. Field Name) with the name of ever field within every table in my database using VBA code?
I would like to build a database to keep track of tardies and absences in my dept (~70 employees).
I need Date, Name, tardy or absent,
My current table:
TblEmployees EmployeeID PK Last Name First Name Team
TblEvent EventID PK Event (Tardy or absent)
TblAttendance Date EmployeeID EventID
Is this a good structure? I need to be able to run a query that will sum the total number of tardies and divide by 6. That number will then be added to the total # of tardies. The query needs to only show the values over the last 6 months. Any help is appreciated.
My name is Paul and I am new to this Forum, so I firstly wanted to say hello to all and glad to be here.
I need some assistance with a work related database. I am currently developing it as a project but have run into a wall with the design being my first time.
I have to create a customer service and invoicing database. I currently have the following tables:
Client (Customer No, User Profile, Password, Customer Name, Acc No, Policyholder, 100% Indemnity)
Contact (Acc No, Date Signed On, Company Name, Contact Name, Address 1, Address 2, Address 3, Address 4, Address 5, Post Code, Telephone No, Fax No, Email)
I have managed to link Client and contact no problems at all. I have also managed to link Client to Product using the table Client / Price. I have tested this with queries and can get all of the data I need extracted out.
However the problem occurrs when I try and link the Usage table to something or other. The Usage table is data that I need to import on a monthly basis from Excel. Once I get it, I need to load it into Access and then generate usage statements for each of the clients to show what products they have ordered and how much each report has cost them.
To make matters worse, each client has 14 different products to choose from, and all clients have different prices for each of the different products.
Getting this to connect properly is an absolute nightmare.
Can anyone assist me in how I might link the Usage (imported table) to the rest of the database? I have attached a copy of the relationships on a word document and wonder if anyone can put a finger on what I might be doing wrong.
Beginner at ASP VBscript. Hi I'm trying to update a Access database through a form using ASP VBScript. Kind of lost. I've looked at some sites and they all have complex ways of doing it. CAn anyone help with a simple little example. say a table with firstname and second name.
I'm wrestling with a problem with a hardware and software database, on the software side. I'm trying to figure out where to store the license and media cost for a piece of software. Right now I have the following tables:
1. Software name (MS Word, Office, for example) 2. Software version, which also stores whether a license is required 3. Software license, which includes the license code and whether that license expires 4. Software inventory table, which stores the # of licenses we have available for installation. The purchase order (PO) # and date are currently here. 5. Software PO generating table. This is used when we need to reorder more licenses, or a new piece of software.
I need to figure out where to store the cost of the software, the cost of the license, how many people are covered by the license. I've been trying to decide which of the last three tables should store this information.
There are two issues: we need to keep a history of how much older versions of the software and its licenses cost, as well as be able to enter information to order new versions of the software and/or licenses.
Hi all, I have joined this forum recently, and would require the guidance of experienced members regarding a doubt:
I have tried to develop an access database with only one table in our office intranet. The front end has been developed on asp and is running on IIS 5 deployed on Windows XP machine with 512 MB RAM and P-4 processor.The system is working fine.
My doubt is ,will it matter, if more than one user is working on the database(with only one table).that is to say, suppose, one user is posting data to the table and another user is querying the table, will it lead to any complications? The situation as such is low concurrency, maybe about four users will be working simultaneously at the same time. Thanks in Advance, Sincerely, Manoj
i have a database that has a hyperlink on the table, my concern with this is that if the folder with the linked items(pdfs) is moved to a different location then does that mean that all the links will not work? if this is so is there a way to get the links to point to correct items other than manualy doing it ( there are thousands of links ) the reason i have used links is to keep the database small in size.
So i split my database into a front and back end, now i wanted to add a table to the database. I created the table in the backend but im not sure how to make it appear in the front end. Im using access 2010. I tried the link table manager but the table i created dosent appear there.
All, using access 2010 but working on a 2003 database. Trying to import a table from another database. I made a copy of a database im trying to import from a database on a production server and placed it in my development folder. When I go to import the table; it throw an error:Reserved error (-1524); there is no message for this error.why I can't import a table especially if no one is using it?
My 886 Mb access database crashes at any time I use the form I use to navigate it as the main menu. This just happened after I updated one of the tables.However, I've had this issue of not being able to compact and repair this database for a long time. This database is on one of our network drives. I tried to copy to my hard drive and it also hang up!
