Best Practice: Multiple Values

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 Replies


ADVERTISEMENT

Multiple Practice Groups For One Person

May 27, 2005

Hello,

I'm creating a simple relational database of all the attorneys our company work with. I'd like to have three tables: "Law Firms" (firm name & other info), "Attorneys" (what firm, personal info, and what practice groups s/he is involved in), and lastly "Practice Areas" (all different practice areas with codes).

Now, my issue is that people are usually involved in multiple practice areas. Thus, I can't just put a code for one practice area in "Attorneys" table, but I can't put multiple codes in either in one field (right?) because it'd mess up the relations.

What I'd like to accomplish is for people to be able to go to our intranet and, using a form (?), select a law firm and a practice group and get a list of attorneys.

Does anyone know of a good way to solve this? It doesn't have to be pretty, since the DB isn't going to be huge (2000-2500 records).

Thank you!

View 3 Replies View Related

Good Practice Or Bad?

Mar 9, 2006

A developer where I am contracted to constructs his If statements like this:
If Day(Now()) <> 1 Then DoCmd.OpenQuery "qry_06c_Get_CurMon_Rebates"

Instead of like this:
If Day(Now()) <> 1 Then
DoCmd.OpenQuery "qry_06c_Get_CurMon_Rebates"
End If

All on one line and no end if needed. I didn't even know that would work.
Is this good or bad practice from a coding standpoint? Any Pitfalls?
Thanks

View 5 Replies View Related

Best Practice For Charts?

Jan 3, 2007

Deal All

I've got some simple charts to do in Access. They are working fine - except for the Legends.

The problem is that my table (on which the charts are based) is fed by Combo boxes which populate the table with codes, rather than a descriptor.

For example, the combo box on the input form displays two columns

5 | Excellent
4 | Very Good
3 | Average
2 | Not good
1 | Poor
0 | Don't know

..but populates the table only with the numbers 5, 4, 3, 2, 1, or 0.

Consequently, the legend in my graph only displays the numbers - when I would really like the Words.

What's the best way round this?

NoVoice

View 1 Replies View Related

How To Create A Template For A Medical Practice

Oct 26, 2012

I am just wondering if theres a way I can create a customise database for my workplace. We are looking at replacing the old record cards that sit on the desk that records each clients visit and the price they paid. We do use MYOB for all our accounting etc but we are wondering if there is a way that we can create a database that enables us to record the date and price paid for each visit for every individual client?

View 4 Replies View Related

Is Trimming Field Size Where Suitable Good Practice?

Jun 6, 2007

If I have a DB with several tables containing thousands of records, and most of those records only ever need say 8 characters, does anyone know if changing the field size from default 255 chars to 8 chars will actually benefits the the DB?

Potential benefits I'm thinking may occur are reduced filesize and maybe some speed?

View 3 Replies View Related

Best Practice To Handle Poor Data Quality In Relationships?

Jul 21, 2007

I'm attempting to normalize an enormous table with order data, but I'm running into some problems. The table currently contains many duplicates, of which also included the actual order information (yikes!), but I managed to normalize it almost all the way down. It appears that different accounts can be used on orders, and these order numbers are being recycled for some reason months down the line (don't ask my why they're reusing them for future orders because I have no idea either, they should be creating new order numbers). Of course, the Order number is the primary key in my table as it should be. I guess the same thing can occur with the sales rep. Anyway, I'm struggling to find the "best practice way" to deal with this situation. I'm almost tempted to create an intermediary "transaction table" or something like that between the main general order information (which at this point will basically be the Order Number and Customer ID only), then include a table with the account information and sales rep info, then have that link to the Order Detail with the products, quantity, order number and various dates for those order numbers. Order maybe it should be a separate, related table, but not between the general order information and the order details? Can anyone tell me if I'm on the right track for this situation? It was a total curveball that the rep and account information could be different on these orders.

Option 1:
Order (Order #, Customer) -> Transaction Information (Order #, Account Type, Sales Person) -> Order Details (dates, products, quantities, etc)

Option 2:
Order (Order #, Customer)---> Transaction Information (Order #, Account
| Type, Sales Person)
|
|-> Order Details (dates, products, quantities, etc)

View 2 Replies View Related

Computer Disposal Best Practice: Table To Store Retired Hardware?

Jun 27, 2007

Hi guys. I have been working on a database to store network information, computers, software etc......

I have a table (tblComputers). I allocate computers to users on the network.

My question. When they come to the end of their shelf life I have the option to delete them from the database. However once deleted I will have no record of them. So I thought......would it be better to create a seperate table and move them there, or add an additional check box within tblComputers 'Retired' then add some code to prevent allocation?

Advice warmly received as always.
Phil.

View 2 Replies View Related

Sum Of Multiple Values

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

Looking Up A Value From Multiple Values

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

Multiple Values In One Field??

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

Multiple Values In 1 Form

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

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 3 Replies View Related

Prompt With Multiple Values

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

Multiple 'order By' Values In Sql

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

How To Add Values From Multiple Subforms

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

Multiple Values Field

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

Update Values In Multiple Tables

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

Concatenating Various Values From Multiple Columns

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

Copy Values To Multiple Fields

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

Averages From Multiple Colums With Ignored Values

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

How To Return Values From Multiple IDs On The Same Line

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

Adding Values For Multiple Departments

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

Passing Multiple Values To A Query

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

Store Multiple Values From List Box

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

Form I Can Enter Multiple Values Into.

Feb 17, 2006

Hello,

I am making a database and I am currently doing my forms. I have been using wizards and so far everything I have wanted to be able to do has been achievable, except for where I am now! I am making a 'purchases' form. What I want to be able to do is make multiple purchases in the one form. Let me try and give you an example to further explain myself.

Lets say Mr John Smith wants to purchase 1 black pen, 1 30cm ruler, 2 rubbers and a compass.

I want it so that rather than have to add each one of the purchases one by one, I want to be able to add them all in the same time and be able to go back to my purchases table and see:

Mr John Smith - 1 black pen
Mr John Smith - 1 30cm Ruler
Mr John Smith - 3 rubbers
etc

Hopefully you understand what I mean?

Can anyone assist me?

Most appreciated!
Adam

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved