ok basically im creating a quote system that will show users prices from one destination to the other destination based on the size of the vehicle..
you can see a similar quote system used on the following website..
http://www.londonairporttaxi.co.uk
there will be three main tables..
Airport Table
airportID - primary key, autonumber
airportNAME - text
the airport table will consist of 5 records..Heathrow,Gatwick,Stansted,London City, Luton
Town Table
TownID - primary key, autonumber
TownNAME - text
the town table will contain a list of postcodes, maybe 50 or so (n1,n2,n3,n4,e1,e2...)
Car Table
CarID - primary key, autonumber
CarNAME - text
the car table will consist of 4 records..Saloon,Estate,MPV,Executive
now what i need to do is create a similar quote system like there is on the url above..
my original idea would be to do something like this..
Have three tables that contain the quotes for each type of car..the first two listed here will work i think..
Airport2TownQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
townID - the town Destination, foreign key
price - currency
Town2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
townID - the town Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency
however.. for airport to airport quotes.. the resembling table would look something like this..
Airport2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency
but you cannot place the airport id twice in the same table..(this maybe a case of simply renaming the airport fields in this table such as airport1, and airport2.. but can you still link them and enforce integrity if the fields have different names?)
also one example may be Heathrow to n1 is £30... this does not mean however that n1 to heathrow will be £30.. this is why i need to separate the quotes in this way.. for the 3 possible journeys (airport to airport, town to airport, airport to town).. also the quotes will be different for each car type..
anyway.. i shall be using asp to create a similar quote system on my website.. but i just need to get the final layout of the database sorted.
I am using DoCmd.TransferText to import a CSV file to a new table. The problem is some of the fields contain quotation marks which really messes things up for me later on. Is there some way I can go through the whole table and remove them?
I was wondering if someone can please assist/advice. I am currently a bit stuck and feeling like I am walking into a dead end the whole time. I am currently creating a DB with the purpose of Capturing Potential Candidates provide them with an ID. Fill in their basic details Name Surname, submit to a report, which I will then export, email to them, they will fill in the outstanding information requested, send it back and will import it back into the DB with my various queries to run searches on candidates for placements.
My only problem is that I have quite a lot of fields...
So I Decided to Create 4 Tables In each table I am using the same ID for a person so should be a 1 to 1 Relationship.
1 General Information 2 Info Relating to Skill 3 Work History 4 Qualifications.
I have all of these separate tables due to the fact that with all the records in one i exceed my 255 table fields allowed
But then if I decide to create a query so I can run some of the details i get errors because there are to many fields. Even if i run one on the other. My last one will still have to many fields. Basically I am hoping someone can just assist in what would be the best way for me to lay out my tables. And what would be the best to link these tables. Is it better to have all the records horizontally, or as a one to many with duplicates.
Please find attached a copy of the tables. I would really appreciate any advice Regards
I am working on a complex database to store Business information as well as specific into about that Business... here is the tricky part...
The individual companies are sorted by Business type (i.e. Computer Software Companies, Child Care Providers, Commercial Building Contractors, etc.) and each Business type needs to hold a simi-customized set of information (i.e. Commercial Building Contractors needs Total AZ Billings for Commercial Bldg., $ Awarded for Completed Contracts, % of Work Subcontracted, No. of Local Employees, and Areas of Specialty). I will refer to each type of information as a Category. Each Business Type has between 4 and 7 Categories. Within a Business Type there are between 10 and 30 companies listed. There will be just over 200 Business Types listed within my database structure, as well as around 100 unique types of Categories.
Primarily, I need to be able to query and format each Business Type, to include the Companies information as well as each Category that is associated with the Business in a flat spreadsheet style layout.
Now, I intend to create a unique report (if needed) for each Business Type as needed to customize the look (i.e. column widths and any other special formatting needed) of each Business Type,
Here is where I stand currently with this project…
I have 4 tables, 1. Business (it includes basic contact info that is common to all business) 2. Category (is basically a list of all possible categories) 3. BusinessCategory (a Many-to-Many relationship container that links the BusinessID to the CategoryID and includes the corresponding Value) 4. BusinessType (a list of business types that a business can belong to)
This structure seems to work in that it can handle the customization of categories for each Business within a BusinessType. The problem I am running into with this structure is creating a query to handle the data and create a “flat” table for a BusinessType with each Business and it’s categories.
It may be that I can use this structure to do what I need done, however I think that there must be a better way to structure my data to work the way I need it to work. I really appreciate your input on this!
Hey all.. Been out of the Access loop for quite some time now.. Last project I did was in Acc97, about 5 years ago! Been working on SQL Server and web programming lately...
Anyway, I have a project that needs to be done in Access XP. It is a basic resource allocation DB. Listing of employees, projects, and time associated with those projects per Employee. I want to keep this normalized, and so I have this basic table layout..
tblEmployee -- Has EmployeeID, name, etc tblProject -- Has ProjectID, name, etc tblEmployeeProject -- Has EmployeeID, ProjectID, year, month, hours tblWorkingHours -- Has an autoNumber ID, Year, Month, Working Hours, Holidays, business days
Basically, the tblWorkingHours has 12 entries per year, Jan-Dec. The tblEmployeeProject is where I have the meat of my data.. If an employee(1) is assigned to a project(123) for 20 hours a month for 3 months(Jan-Mar 2008), then there will be 3 rows in there. EmployeeID - ProjectID - Year - Month - Hours 1 - 123 - 2008 - 1 - 20 1 - 123 - 2008 - 2 - 20 1 - 123 - 2008 - 3 - 20
This seems to make sense to me, and seems to be normalized. However, I cannot seem to create an updatable query on this layout... I would like to pick a project, and then a few employees assigned to that project, and then enter in the hours for each employee, for any of the 12 months I need. The problem I'm having is that I cannot seem to get an updatable query out of this layout. The only way for me to get a listing of all employees attached to a project, and then all months for the year, regardless of an entry in tblEmployeeProject, is to use a Cartesian Product type join on tblEmployee and tblWorkingHours. And then do an outside join on tblEmployeeProject. However this is obviously not updatable.
The only other option I can think of, is to prefill the tblWorkingHours table with 12 entries per year, however this seems to add unnecessary data in the database. At that point, I might as well create a table that has 12 columns, one for each month.
Sorry for the long-winded post, but wanted to try to get all my points across. Any help would be greatly appreciated! Thanks all.
We have an application which is used in a couple of offices and on a number of terminals. The data is stored in a back-end database on the server on each site.The databases are getting to the stage that we cannot roll out changes to the back end table layouts relatively efficiently. What I want to do through vba if possible is basically have a blank local version of each table and say:
check tab_1 on linked_backend, if tab_1 layout <> tab_1_local layout then update the back end layout to mirror that of the local layout.
We cannot overwrite the data, but on occasion, we need to add an extra field to a central table as new bits of the application are developed.I've tried a few searches here, and am probably using the wrong phrases, but not getting any results.
I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.
Ok, firstly im sorry if this is in the wrong forum. I say this because my problem first begins with the tables but then i need to sort a problem out with the form aswell, but i will put it in here.
Here is the problem. Im sorry if im not too clear with this.
I need to create a register system E.G. A child comes into a club, he is registered. At the end of the session the child leaves the club. His depature is registered and his departure time is also recorded down.
Like a paper based school register but electronically.
How would I go about implementing this into a system?
Would I need a child table (with fields such as Child ID, Forename, Surname etc.) and a register table (not sure on the fields)?
k, this bit below probably goes in the forms section...
How would I get this to work on a form so there were boxes to check for arrival and departure, and when a button is clicked the depature time is filled in for each day of the term?
Hi guys i wrote a vba code that supposed to go find all non system tables in access 2000 db and generate corresponded create table statements for them. Unfortunately when i run the program it misses one of the tables called committee members and also uses one of table names 2 time.Furthermore, instead of using table names it uses first columns of each table in generating table name for createing table statement.I be happy if some expert help me fix this bug in my vba program.Thanks
http://i5.photobucket.com/albums/y180/method007/tablereletionstenissvba1.jpg ( teniss club db reletions )
Code:Private Sub CreateTableButton_Click() Dim T As TableDef Dim db As Database, cont As String Set db = CurrentDb() For Each T In db.TableDefs If Left(T.Name, 4) <> "MSys" Then ' 'If T.Name = Me.ComboBox Then pk = Left(GetPK(T, db), InStr(1, GetPK(T, db), "<-") - 1) cont = cont & ShowFields(T.Name) & vbCrLf & " primary key " & pk & vbCrLf & vbCrLf ''MsgBox ShowFields(T.Name) & vbCrLf & " primary key " & pk & vbCrLf & vbCrLf ''End If End If Next T MsgBox cont Call createTextFile("C:createTableStatements.txt", cont) End Sub
i'm try to create a database to record staff sign in and out times and to get reports but i cannot for the life of me get it working after many many efforts. can any one help me?
I've been asked if I can create a database which will act as a quote generator. There will be 5 components to make up the whole product. However, the options available for components 3, 4 and 5 will be dependant on the components chosen for 1 and 2. i.e. there may be 5 options for each component. If part 1a is chosen, then only parts 2c, 2d and 2e will be available. If part 1a and 2c are chosen, then only parts 3a and 3d are available and 4b, 4d and 4e are available. ...does any of this make sense?????
My question is... can anyone tell me how I should create the tables and the relationships to enable me to create a database like this. I am completely stumped. It may be that this is completley beyond me, but i'd like to at least be able to give it a try. Any help would be appreciated. Thanks
I have been trying to create a Quote form in my Database. I have a list of products, each product then has a table that details prices based on quantity breaks (i.e 500 to 999 and 1000 to 1999 etc.).
In my Form I am unable to create a subform in datasheet view that will enable me to select a product from a combo, type in a quantity and for the price to be displayed based on the quantity?
Has anyone managed to achieve this? I would love to see the code and put it into practice.
When trying to update an access membership db where expired members are being set to 'inactive' the script keeps hanging up with a runtime error when the first or last name of the member has a single quote in their name (eg O'Neil)
Any ideas where to look to fix this would be appreciated.
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Products.Catalogs LIKE '%[a-z]%' AND (Products.ItemName LIKE '%t'ai%') AND ((Products.NewItem = True) OR (Products.HotBuy = True)) ORDER BY Products.ItemName'. /webdev/flaghouse/NEWITEM_List_Main.asp, line 149
this results when search terms contain a ' (SINGLE QUOTE). i've tried to double up all of the SINGLE QUOTES in the code and still get an error.
heres the CODE:
' check if keywords entered, split comma-delimited list into array of keywords
If Request("Keyword") > " " Then theKeyword = Request("Keyword") listKeywords = split(theKeyword,",") maxCounter = ubound(listKeywords) whereClause = "Products.ItemName LIKE '%" & listKeywords(0) & "%'" FOR counter=1 TO maxCounter theKeyword = listKeywords(counter) whereClause = whereClause & " OR Products.ItemName LIKE '%" & theKeyword & "%'" NEXT
SQLString = SQLString & " AND (" SQLString = SQLString & whereClause & ")" Else theKeyword = "None" End If
if anyone sees anything amiss, please let me know. i'm at wit's end!!
thanks. please email me at: URL. again, thank you in advance for your help.
I am after a MS Access Database that will allow me to do up quotes and invoices for my small computer business does anyone know of any free access databases that will allow me to do this
If you know of any can you please let me know via email matthew01@gmail.com
I have a work order system that people use but it somewhat randomly puts blank records into my table. I've added a lot of validation checks when submitting, closing and resetting the form and limited the way people can exit out of the form to fight this issue but it still happens.
As a basic user of Access I believe I have identified a use for the program for the purposes of a reporting task which I am charged with at the moment. We undertake the weekly tracking of proposals, which involves in brief, the action required for the bigger proposals, the teams involved in writing the proposals and follow up information as well as registration of wins and losses etc. At the moment we use Excel but I am finding it extremely labour intensive with entries having to be removed and captured by only one person on a weekly basis and when you are talking about 40 to 50 new proposals registered nationally in a week and about 100 updates on existing jobs it becomes a perpetual case of chasing your tail.
Am I right in thinking that if we were to design a simple, effective database with a form design that all our research directors could use with ease, and we could merely run reports off each week that this would not produce a far quicker reaction time when it comes to pooling international resources but also far more effective reporting system with which to produce our management summaries?
I am aware that this will probably require quite a bit of consultation on design but before I go shouting the merits of Access to any of my superiors I wanted to make sure I could be confident of Access's capabilities first?
Can anyone advise? point me in the right direction?
Hi Folks, I'm sorry to be stupid, here, but I can't get this to work. I have a parameter query that forms the basis for a report. To get the value for the query, I have a form on which the user selects the value they want from a combo box (cboProgram). The values in the combo box include all of the programs used in the file plus a value called "All" which means - just include all the records. My problem is that when "All" is selected in the combo box, no records are returned.
Things to mention: "All" has a value of 1 in the lookup table. I tried the query with the actual values and it works fine. The problem is when I try to do it from the combo box.
My first attempt was to put: iif([forms]![frmFormName]![cboPrograms]=1, "*", [forms]![frmFormName]![cboPrograms]) in the criteria for that field in the criteria box for lngPrograms.
Access' response was that the criteria value was too complicated to resolve.
So, I noticed that I wasn't saying 'Like "*"' in the first part, but if that's the problem then I can't get the numbers of quotes right. I also tried switching the iif statement around and saying <>1,[forms]..., else some version of 'Like "*", but that didn't work either.
Then I queried around the forum and found a recommendation to make a function to return the value, so I did the following:
In the criteria for the field in the query: IfAll([forms]![frmFormName]![cboPrograms])
And in a module:
Public Function IfAll(ProgSel) Select Case ProgSel Case 1 IfAll = "Like ""*""" Case Else IfAll = ProgSel End Select End Function
I'm still not getting it. If "All" is selected, value = 1, then no recorsd are returned. Can anyone help me out please?
I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.
On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?
If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.
I am creating an access database to store customer details as well as quote information. I have created a form to input quote details that our office can complete when a customer calls to complete a quote. I need to create a form that opens as a new form every time we click on the form to input a new quotation. How do I do this? At the moment when I click on the form, the form opens but the details of the last quotation are stored on the open form.
I have no training in access at all and am self taught so far so by no means an expert.I also want to create a form that allows you to search for particular quote numbers, so if someone calls and asks about a particular quote number we click on a page that says 'search quotes', input the quote number and the form opens with all the details of that quote.
I know that you can insert comments and notes in VBA code using a singular quote, like the following:
Code: 'Comment about what the code does
Is there a similar way to insert comments in the SQL of queries? I found other forum posts that say this isn't possible but those were all for pre-2010 Access. Has this changed at all?
I have subforms that are datasheet view, if the user rearranges the coloumns, or resizes etc , I would like to save what they have done so it is as they left it next time they open up the screen.
This works fine with an mdb (all my forms are set to close acsaveNo).
However in an mde this doesn't work.
Can I get this to happen in and mde.
(A button that saves a layout change would be fine for my purpose)
