Hey, i'm working on creating a database.
it's involves a customer booking a ticket for a flight.
i have three tables, customer, booking and flight.
anyways, i'm having trouble applying the price the customer needs to pay
say there's an attribute on table Flight -> flightprice.
when a customer makes a booking, discounts are applied to the price.
discounts include:
1. Special seasonal discount, (eg from Sept to November) - 4%
2. Member discount - 6%
3. frequent flyer discount - 5%
4. other discount - 4%
the thing is that these discounts are stackable. ie, applied together.
(eg a person eligible for member discount and frequent flyer discount will get 11% discount total)
i know that if only one of the discount rates apply,
then i'd be able to create a new table called Discounts
and have a one to many relationship with the Booking table.
but i'm not sure how i need to bypass this when several discount rates can be applied at once to a single booking.
-------------------
also on another note.
how can i make the seasonal booking only be applied if the date of the booking lies in between the promotion season?
When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
Any way to use multiple count functions in a query with their own individual filters without affect the others?
For example:
SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2] FROM [E&I Table] WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV")) GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;
I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
I am experiencing some confusion in setting up a many-to-many relational database, and I would greatly appreciate any assistance that anyone may have to offer.
Currently, I have two tables: one which is a last of organizations provided services, and the second table is a list of counties in the state. While many organizations only focus their services on one county, some offer services in multiple counties. The desired end product is a website in which users can search for available service organizations based on the county they have selected.
In Access, what is the best way to link each individual entry in the organizations table to the appropriate counties in which they offer services? For example, I will need to say Organization1 offers services in County1, County2, County43, and Organization 2 offers services in County2 and County62. When a user selects County2, it should return both Organization1 and Organization2… and so on.
What is a more efficient way to store this information than just having multiple columns?
I'm hoping someone can help me with this one. I have a database that tracks seminars and registrations. I need to revise it as they want to allow an individual to add in up to 10 more additional people on their registration.
The simple way would be to do the following (you'll see my 10 additional fields in here), but I don't think this is the 'RIGHT' way:
Tbl_New_Registrations Registration_Number - Autonumber - PK Seminar_Name - Lookup (goes to Tbl_Seminars) First_Name - Text Last_Name - Text Business_Name - Text Years_in_Business - Number Address - Text City - Text State - Text Zip - Text Phone - Text E_Mail - Text Questions - Memo Additional_Registrant1 - Text Additional_Registrant2 - Text Additional_Registrant3 - Text Additional_Registrant4 - Text Additional_Registrant5 - Text Additional_Registrant6 - Text Additional_Registrant7 - Text Additional_Registrant8 - Text Additional_Registrant9 - Text Additional_Registrant10 - Text
What I 'think' is required is an Additional_Registrants table, but I'm not sure how to do this so that when someone fills out a registration form, the 10 additional registrants go into this table and the database knows that these 10 belong to record X and so forth.
I wish to request if it is possible to design an inventory where multiple shops or branches can be accessed from the same place.
or
multiple shops can use the same program with a centralized database, such that every user can only access his or her report from anywhere they login from.
I am trying to design a subform to allow multiple records to be entered and then uploaded to a table. I've designed the subform but it is currently pulling all of the records from the table through and all I want is a blank subform for data entry.
I have tried putting a macro in the On Load and On Open events to get it to go to a new record but I keep getting error number 2046.
I am attempting to apply a filter to a query i have setup. I only want the user to be able to search for specific words within two fields (technical and behvaioural) i have setup. I know i have to use a WHERE expression but am confused as how to exactly setup the expression... any idea's???
Have a Access databaseform with 300 names, ID's, dates and other work information.
On this form I have an unbound comboxbox called "date" where I select various dates. Also on the form, I have a bound textbox called "completed by". The date selected in the combox goes into the "completed by" textbox. Last, a checkbox called "apply_date_to_all".
I wish to select a date form the unbound combobox when the checkbox is checked, the date is applied to ALL records in the database into the "completed by" textbox.
This is to be printed form time to time.
This is my second request, sorry I I did not make myself clear the first time. Any ideas on how to start?
Private Sub APPLY_DATE_AfterUpdate() Dim mytext As String Dim mystring As String mytext = Me.[Text1074] Dim mysql As String mysql = "UPDATE APPLICANTS SET [GRAD_DATE_IN_WORDS]=" & mytext & ";" DoCmd.RunSQL mysql
I am applying a date to ALL records on a form. Text1074 is a combobox with dates in it. It gets the dates from a table called Grad_dates.
The dates are defined as text in the design view of the Grad_dates table. In the first column is called Graduation_date of Grad_dates table are dates in this format: Feb 1, 2005. This is defined as text. The second column is called Text_graduation_date. It is defined in this format: February first two thousand five.
I wish to pick the usual date format from the combobox and it applies the TEXT version to all in the dabase. Is the code above correct? in the code above I get an error message saying "syntax error"
I want to apply a parameter query to a subdatasheet.
I have a primary query that has a list of all the machines we service, this same query will count the number of service calls within a given date range that the user inputs. (For the date criteria- Between [Enter Period Start Date] and [Enter Period End Date])
The resulting query datasheet is linked to a subdatasheet from another query that searches all the service calls and links it to the Machine ID. So when I expand the subdatasheet it shows all the service calls for that machine, beyond the date range.
How can I make it so that the date range from the primary query applies to the subquery?
(without having to re-enter the date range everytime I expand the subdatasheet)
I have about 125 .mdb's I want to go through, and in each one, select a table and run a "find duplicates" query, creating a table of duplicate record info for research into why there are duplicates.. I am aware I can manually do this within each .mdb, but would be happy to be able to automate the process.
I wasn't sure what to search for, which is the reason for my posting. If anyone has an idea (and not questions on why I want to do this, or probs. I might encounter), I would be most appreciative.
I developed a sales database that contains around twenty forms, (in addition to tables, queries, reports and 2 modules). In these forms, I'm setting recordsets, select statements and filters. We recently hired a person to set up a Virtual Private Network. After spending a couple of thousands on a server and countless hours reprogramming the database to have multiple users access the database out in the field, (via Verizon broadband network card), we were unsuccessful to use the database since it was taking too long to download the data or perform any calculations. The database is split. The users have a front end.
The person setting the VPN has suggested that I get rid of all the queries that I have in the forms (which is impossible), or use Visual Basic for the front end instead of Access with VBA.
When I set up the recordsets I use the following setting: rs.CursorLocation = adUseClient rs.CursorType = adOpenDynamic rs.LockType = adLockOptimistic
I don't know what to do. This program is very complicated and I don't want to have to reprogram the whole thing with Visual Basic, and I don't know how the front end is going to work if I eliminate all the queries, select statements and filtering.
Any ideas, comments or suggestions would be appreciated.
I need a button on a form to not only add a new record but also run a query. I can't see how to do this using a macro and my coding experience is limited. How can I get this button to do both of these actions?
I think that I should add that the add record action must happed first. However, I have noticed that this automatically increments the record number in the navigation section to the next number and blanks the firelds in the form (which is data entry mode). As the query relies on information given in the fields on the form, i don't want the field to be blanked. I'd like the the for to stay on the same record until the query has finished. Once this has happened, I'd like the form to reset itself so that it looks as if it's just been opened and the first record is the new record (just as if you'd opened a form in data entry mode).
Sorry, complex I know. :( :confused: I'm sure there's some one out there how's got the answer though. :cool:
I have a simple form where a user selects a query from a drop down list. I need to have the query apply a date range that has also been selected in the form.
Would I call to the date form fields from the query as a variable somehow?? If so, could someone show me the syntax? I am extremely new to access.
I am currently building a stock control system in access. I have a form with which the user selects a stock item from 3 combo boxes (stock name, stock weight and stock grade). The combo box links to a table of stock items. A stock item is made up of a name, weight and grade (all separate fields in the stock items table).
I wish to setup the form so as the user selects the stock name from the first combo box. Based on the value selected by the user, the appropriate stock weight(s) assoicated with that stock name will be displayed in another combo box.
Basically, I wish to take the result from one field in a form and apply its result to a query(?) in another field so as to filter(?) my result accordingly.
I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).
I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.
Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.
If Me.txtRole = "Agent" Then DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())" AgentFilter = "CASEOWNER ='" & Me.txtName & "'" DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter Exit Sub End If
In my database i need two process in the database one is SOD(Start of Day) and the 2nd one is EOD (End Of Day). The purpose of these process to run multiple hidden queries in back-end. In both processes there are several queries to run. How i can built this but this is necessary for database. another thing these process execute only once a day. after login i want to run a form that will check that SOD is performed or not if not then SOD (Run SOD) Screen will appear if SOD not perform yet then system ask for SOD Process to run otherwise disable my dashboard items. and 2ndly if EOD not performed last day then system will run normally and not ask for SOD process.
I am using two combo boxes to filter a list box with the code below. The combo boxes work, but when the form opens, the list box is empty until it is filtered using the combo boxes.
I want the list box to return all records when no filter is applied.
Here is the code:
SELECT Q_Gender_Statistics.ParticipantID, Q_Gender_Statistics.Gender, Q_Gender_Statistics.Date, Q_Gender_Statistics.Year, Q_Gender_Statistics.[First Name], Q_Gender_Statistics.[Last Name], Q_Gender_Statistics.[Other Names], Q_Gender_Statistics.[Passport No], Q_Gender_Statistics.[Duty Station], Q_Gender_Statistics.[Contact Number] FROM Q_Gender_Statistics WHERE (((Q_Gender_Statistics.Year) = Forms!F_Gender_Statistics!cboYear) AND ((Q_Gender_Statistics.Gender) = Forms!F_Gender_Statistics!cboGender)) ORDER BY Q_Gender_Statistics.Date DESC;
Dim stDocName As String Dim stCriteria As String stDocName = "Rallies this year" stCriteria = "[Year of Rally] = " & Year(Date) MsgBox (stCriteria) DoCmd.OpenReport stDocName, acViewPreview, "", "", stCriteria
the output in the msgbox is [Year of Rally] = 2013 which seems fine but the OpenReport command then gives me a type mismatch error the [Year of Rally] is a calculated field in the query - the record source.
I have tried
stCriteria = "[queryname].[Year of Rally] = " & Year(Date)
