Best Way To Design For Multiple Values?
Sep 24, 2007
Hi,
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?
Thank you in advance for your help.
View Replies
ADVERTISEMENT
Mar 3, 2008
I am adding fields with numeric values in design view of my query. The only issue I am having is that the query is not showing totals when one of the fields does not have a numeric value in it. How can I show the total numeric value regardless of the null value? Thank you:cool:
View 1 Replies
View Related
Oct 1, 2015
I have a combo box in my report design and I would like it to display "true" values as a list with check boxes.
View 5 Replies
View Related
Dec 29, 2005
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.
Does this make sense?
Any ideas or thoughts on this on?
View 1 Replies
View Related
Mar 6, 2006
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?
View 1 Replies
View Related
Aug 14, 2015
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.
View 1 Replies
View Related
Mar 27, 2015
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.
View 2 Replies
View Related
Apr 17, 2015
how to design a form that can pull multiple invoices data related to the same purchase order number?
View 14 Replies
View Related
Oct 6, 2014
I have had to use my first crosstab queries.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes.
I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
View 6 Replies
View Related
Mar 29, 2006
I am using the formula:
Normal Pay: Sum(IIf([PAYCODE]="STD" Or [PAYCODE]="PAY_R" Or [PAYCODE]="RATE*",[AMT],0)) .
as an expression and need my query just to pick up one row with a total sum of these values, however I am still receiving 2 or 3 lines of seperate values.
Can anyone help?
Thanks
Paul
View 1 Replies
View Related
Jan 27, 2014
I have a Customer table with the normal customer info, name phone.Each customer has one or several parts. so I have a parts table.I have a service table and form.The service form contains the service number along with customer number.Each service has a service detail subform for service to the customers part.The problem I'm having and It seems to me to be an easy answer but it has escaped me totally. When I go to the Service detail form I want to pick the specific customers part.I cannot just select from the particular customer, but have to pick from a list of parts from every customer. I just want to see the parts from a certain customer that I have selected from the service order form.
View 3 Replies
View Related
Feb 14, 2006
I have a list of Vehicles in which each have their own maintanance schedules. Most of the maintanance for the Vehicles overlap. Instead of entering the same information over and over I'm assigning each type of vehicle a code number (example '05 Cargo is a 1, '04 Cargo is a 2). Now what I want to do is assign each maintanance the vehicle code, for instance I have 13 different types of vehicles that all need oil changes, so I want to assign the Oil change maintanance 13 codes. Is there a way to assign that maintanance 13 codes without having to enter it 13 times??
Thanks, Joe
View 1 Replies
View Related
Feb 21, 2008
Hi,
how could i get multiple values shown in 1 form only? i currently have a code to look through 6 fields and if the fields iar blank, you get a a message box. you press ok and the next message is shown for the next field if it is blank but it would be so much better to have 1 message box showing all blank fields required to be filled in.
all it does is check the fields as these are required before continuing. if anyof the fields are blank, then it tells you and you cannot continue untl they are all filled. it seems easy in my head!!
im sure this can be done but im not quite sure how.
any ideas would be greatly received.
NS
View 6 Replies
View Related
Feb 25, 2008
Hi All,
I'm a relative newbie to Access - I am wondering the best way to solve a problem. I am the admin for a database used to track projects and the quality control process. With this process we must keep up with the review of another database, the pieces of information entered which are erroneous and when the individual responsible corrects the data. To date, the errors have been manually typed into a memo field which is then pushed out to the end user in a report. The errors are very consistent and I want to create functionality that will allow me to select the errors (there are approximately 50 standard errors) from a list or using a button or whatever is wisest. Basically, I would love some best practice opinion here as I am totally stymied at this point.
Any advice for a weary admin?
View 14 Replies
View Related
Sep 28, 2007
I have a query, and one of the fields has a prompt for user input. Is there a way for the prompt to take multiple values?
View 1 Replies
View Related
Jul 22, 2005
Code:SELECT * FROM mytable ORDER BY type, value
hi i am trying to order a select statement by two columns, so it first orders by type, then by value. the above code would work in php/mysql (i think!) but not with asp/access. any ideas?
cheers
View 5 Replies
View Related
Jun 20, 2012
how to add times together from multiple subforms and display the data on the main form. Just for backround, I am creating a timesheet to be used by our workshop employees. The form is set up in such a way that there is a combo box where they can select their name which rotates the record so all the data is specific to their id, and then there is a tab box with a tab for each day of the week each containing its own subform with combo boxes to select the project they worked on, text boxes to enter the start time and stop time for that project, and then 2 other text boxes: one that calculates time worked on that particular project and then one that calculates the total time worked for the day since they typically work on more than one project in a day. So what I need to do is to take the values from the total daily hours boxes and add them all together and show the sum in a box showing the total hours worked for the week.
My most recent attempt was to simply make an expression in the control source as follows:
=[Sunday subform].[Form]![Daily Hours]+[Monday subform].[Form]![Daily Hours]+[Tuesday subform].[Form]![Daily Hours]+[Wednesday subform].[Form]![Daily Hours]+[Thursday subform].[Form]![Daily Hours]+[Friday subform].[Form]![Daily Hours]+[Saturday subform].[Form]![Daily Hours]
View 2 Replies
View Related
May 7, 2012
Is it possible to have a text field in a table to store multiple values by using a comma or some other type of seperator? I do not want to set the field to a lookup field because the number of values that would be included would be to many to continuously look through to select. I would prefer to just manually type in what I need and use a seperator to create a multiple value field.
View 7 Replies
View Related
Oct 26, 2005
I am developing an access db where employees are allowed to load tools from a tool store.
I have three tables: tblEmployee, tblTool, tblOnLoan.
tblTool includes a field "QtyOnHand" which is the quantity of a particular tool in store available to be loaned.
tblOnLoan is used to record which employee has what tools on loan.
When an employee loans a tool i need to be able to reduce the QtyOnHand of the tool and record the loan details in tblOnLoan.
When the employee returns the tool i need to increas the QtyOnHand of the tool and record the return against the original loan in tblOnLoan.
i have not yet been able to work out how to reduce or increase the QtyOnHand as tools are loaned or returned.
Can anyone please help?
View 1 Replies
View Related
Nov 25, 2005
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.
So for example
Column1 Column 2 Column 3
B11 Europe
would concatenate as:
B11.Europe.
View 1 Replies
View Related
Jun 20, 2006
Hello,
I am running a database in which end users input values into a table via a data access page. The problem is that there are usually lots of records in this table which requires the same value for a particular field.
I was wondering if there is a way to implement something so that the user can input the value once and the value is then copied to all the records?
Many thanks in advance
View 1 Replies
View Related
Feb 15, 2007
I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.
Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.
Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?
View 10 Replies
View Related
Sep 12, 2007
I'm working on a freebie for a day care center.
I can not seem to work this out in a single query.
I have:
tblChildren
tblParents
tblGuardians
Because so many familes that use day care are fractured, it is possible for 1 parent to have multiple kids but some of them to have a different second parent.
Therefore each parent and each guardian has thier own line and own unique ID in their respective tables.
each child in tblChildren can have up to two Parent IDs (Parent_ID_1 & Parent_ID_2) and up to two Guardian IDs (Guardian_ID_1 & Guardian_ID_2)
I've set the data entry form to require at least one parent for each child, but there may not be any 2nd parent or any guardians.
So what I now need to do is create a query, or series of queries, that results with each child on it's own line, with the name of each parent, and each guardian in thier respective fields.
Has anybody got any guidance for me please?
View 1 Replies
View Related
Sep 21, 2007
Hi,
This may be a dumb question, but I've got kind of a question about adding values.
I have a subform that contains a department name, and it's waste totals (in Kilograms) for the day, for a particular company. The company information is the main form.
Whenever I start a new department, the totals, and category names start blank (different departments could produce different waste).
I'm trying create a query that gives me the total value for each Category of waste. However, I keep getting all the seperate values returned?? As apposed to a total. For example.
Company A has two departments:
Office and Production.
For Office they had 20 Kg of plastic bottles and Production had 20 Kg of plastic bottles.
I want to be able to total the weight of plastic bottles...it's just not working../
?
Any advice?
View 1 Replies
View Related
Sep 16, 2005
Ok, i have a form in wich i want to set the parameters to a query.
On the search button it will open another form to display the data.
i used this code stLinkCriteria = "[ID]=" & Me![id] And "[status]=" & "'" & Me![sta] & "'
the 1st value for status is "active"
it returns a msgbox asking the value of the parameter and a field to input the data.
I can't understant whats wrong....
View 4 Replies
View Related
Jan 11, 2006
Hi all,
I was thinking/hoping of using a list box on my form to store multiple values, I haven't been able to find a way of storing any value so far so not sure how easy it will be?
How can this be achieved or is it just easier to use several check boxes (approx 8)
Thanks
View 3 Replies
View Related
