I'm sure there's an easy solution to this somewhere...
I have a query that is supposed to assign a wage to a production job if the range output falls within a certain percentage range. For example, if they produce 125% to 130% of their goal, their wage is 15 cents higher. If it's between 130% and 135% of their goal, their wage is 30 cents higher. There are about 20 ranges that my bosses want set up. Short of making the longest Iif-Then-Else statement in history, does anybody have any suggestions? Should I use a separate table and somehow try to integrate it into the query?
I have updated a database I created with additional reports, some UI enhancements, etc., but have not changed anything about the table structure, field types, etc. The database has been in use for about a week by the user and I was working on my local copy. How do I provide the updated database to the user without incurring key violations on the auto-number PKs when their data is imported into the new version?
I have a query that is pulling data from a table containing a Tamestamp field. I want to set the query to pull my data but don't include anything that was logged in TODAY. How can I set this in my criteria?
I want to avoid duplicate dates in the date field of my form. It's a little more tricky than it sounds though. Imagine two fields in a form; one list box of eleven values and one date field. Once i have chosen a value from the list box i enter the date in the second field. It is ok for one value to have the same date as another value (in another record) but i don't want to have duplicate dates for one value. Any idea how to SIMPLY overcome this problem?
Not really looking to fully understand how it all works, i would be happy just to fix the problem.
I have three subforms on my main form. They all display salary details from different sources, and each subform has 2 calculated fields in the form footer to show the average salary and the number of records on which the average has been calculated.
I then have a field on the main form to calculate the average of the averages which works OK unless one of the subforms has null or zero in it, in which case it returns no information.
Can anyone suggest a way of bypassing this divide by zero error? The code I'm using to calculate the average of averages is as follows:
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
Hi, nuther question from the forum dunce I'm afraid, I want to ensure the user of the form cannot enter the same [cboPlayerName] twice for the same [txtMeetDate] (thanks for the advice trubolotta, I changed the name of the [Date] field.) The user will select the PlayerName from a combo box. Can I have the list in the combo box diminish by each player as they have been selected for a [txtMeetDate] I suspect a monstrous amount of code & intellectual input will be required to achieve this. Basically, what I'm asking (this time !) is how can I have my program search my tblScratchScores for duplicate names for the [MeetDate] that I am currently inputting Data for?
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
I have a modest sized data base and have been having trouble keeping out duplicate records with the same last name, first name, and DOB. I know I could use all 3 fields as a primary key, but I would like a simpler way of checking for duplicates.
If different people will be entering data into a "new donor" form, how do I keep them from adding the same person a second time?
It's possible people could have the same name, and it's possible that two people at the same address could be donors. So I assume I would want a "no duplicates" on the combination of name and address, right? But how to actually do that.
I have a function to write some data to a text file (as an activity log, separate to the live data contained in the DB)
It uses the FileSystemObject.OpenTextFile method and works fine
However, I'm conscious that, although perhaps unlikely, there is the possibility (which is enough for me to worry about it) that multiple users could trigger the function simultaneously, which could cause a conflict when multiple FE's try to write to the same log file at the same time.
So I'm trying to add some code to prevent this from happening, by checking if write-access is available before proceeding.
Below is the piece of code I've added. Was considering, as an alternative, looping until the objFile is no longer Nothing, rather than depending on the Err.Number?
Code: Dim objFSO As Object Dim objFile As Object On Error GoTo ErrorHandler ... Set objFSO = CreateObject("Scripting.FileSystemObject")
I'm getting back into Access after retiring and about 10 years of isolation (from Access). I have 2010 version and know that one should avoid duplicate entries and a way to do this is make those fields unique key fields. I have a Customers table and have bounced back between CusID (AutoNumber) and Compound Keys (CusFName and CusLName) as the key fields. The compound keys prevent duplicates but become very hard to work with later in code and expressions. The CusID is preferable from that standpoint, but can't prevent inadvertent entry of duplicate names.
Currently I have MS Access DB which has some Oracle database table reference using database link concept. I have entered DSN Name,user id and password which it asks when I try to run some queries which will fetch data from oralce DB linked tables.If some wants to use my MS Access DB, they need to have DSN and everytime they run queries related to Oracle database table reference, it will ask for DSN,user id and password. For this I need to share the user id and password credentials to everyone who wishes to use my MS Access DB which I don't want to. Is there a way to avoid the login window whenever some one wants to use my MS Access DB without asking for oracle user id and password credentials everytime? My ultimate aim would be encrypt password for others.
Iv got a user table with an admin record and head teacher record with the highest access level
I then have 2 teacher records with a lower access level
The admin and headmaster have access to the whole system this includes assigning user name and passwords. However I don't want the head master to be able to change the admins password, so is there a way to lock this record or even hide it from a user form
I have a small database for my warehouse that sells washing machines, fridges etc.
I am trying to get a grip on the stock control. What I am looking for when a order is filled out on our order form is the stock number will reduce by the amount sold. Not sure how to make this happen. I have the number of units in stock under the products table
hello, i am new to this forum. i have been struggling on a project i am doing where i am tring to calculate the stock levles and keep track of them along with the re order level i have calculated the current stock levlel, in a query called "stock level 3" Here the total amount prodcuts sold are taken from the field called stock level.
Here is a break down of my original tables
CUSTOMER
Cu ID And all their personal details such as contact details
CUSTOMER ORDER This is where the order takes place and contains the following information
•In the query “All customer order details”, I have worked out the line total for each prodcut with in one order. I have timed the Qty_on_Order (customer order details table) by price (in the produt table) and by the discount(customer order details) this then gives the line total
Now to update the stock level I made a new query called “stock level Query”, where I added up the total number of each products sold, by adding the “Qty_on_Order” from the query “All customer order details”
I then made a new query called “stock level 2” where I calculated the amount left on the shelves by taking “SumOfQty_on_Order” of each product from “Qty_in_Stock” (form prodcuts table) to produce the field called “Qty_in_Stock”
I then made a new query called “stock level 3” where I inserted the same fields but an extra field called “Re_Order_level” for some reason could not add this to the query "stock level 2"
So far the system can add up the total number of products sold, for all the orders, and then automatically calculate the Qty_ in_ stock by taking away the “SumOfQty_on_Order” once a new order is made.
I then made forms where I go in my form to “customer”" and then to “making new order”. Below is a sub form called “all customer order details sub form” . Here orders are made with defining "Qty_on_Order
I want to make a system which calculates for me i have ordered more stock then there is. Howcan i now when i make sure when i place a new order in the query"All customer order details" , that if i order above the number of "Qty_in_Stock" (from query srock level 3), from the query “stock level 3”, that when I order more products then ““Qty_in_Stock”, in the query “stock level 3”, does not allow me to enter this and if it is possible please can you insert error messages on it.
I do not know if this helps .- I can not make the “Qty_in_Stock”, to appear in the actual table “Products”. At the moment I can only update this in the query “stock level 3”. I am not sure if I need this so I can then set re order level which then comes up with an error message telling me when the re order level has been reached. -
As later on I will need to add on any products brought in from the suppliers to the Qty_in_Stock”, in the products table to be automatically updated.
Code: SELECT [Customer Order Details].Prod_ID, Products.Price, [Customer Order Details].Qty_on_Order, [Customer Order Details].Discount, (Products.Price*[Qty_on_Order]* (1-[Discount])/100)*100 AS Line_Total, [Customer Order Details].Order_ID, Products.Prod_Name, Products.Prod_Type, [Customer Order].Cu_ID FROM [Customer Order] INNER JOIN (Products INNER JOIN [Customer Order Details] ON Products.Prod_ID=[Customer Order Details].Prod_ID) ON [Customer Order].Order_ID=[Customer Order Details].Order_ID ORDER BY [Customer Order Details].Order_ID;
stock level 3
Code: SELECT [All customer order details].Prod_ID, Products.Prod_Name, [stock level Query]. SumOfQty_on_Order, Products.Qty_in_Stock-[SumOFQTY_on_Order] AS Qty_in_Stock, Products.Re_Order_level,Products.Prod_Name, [stock level Query]. FROM (Products INNER JOIN [All customer order details] ON Products.Prod_ID = [All customer order details].Prod_ID) INNER JOIN [stock level Query] ON Products.Prod_ID = [stock level Query].Prod_ID GROUP BY [All customer order details].Prod_ID, SumOfQty_on_Order, Products.Qty_in_Stock-[SumOFQTY_on_Order], Products.Re_Order_level;
please may help me and gice m,e soem tips it will be much appreciated or website/downloads. Thankyou for your help, and if you find easier ways to do this can you help me. Thank you so muh.
Has any one found out a way to change Macro Security Levels!! Microsoft seems to have kept this as a secret. The code below given in the help files doesnt function. Application.AutomationSecurity = 1 or Application.AutomationSecurity = msoAutomationSecurityLow
Hi, I have a data base that is used to keep track of stock level. When i issue a stock item and move to the next record I would like the stock level to be reduced by 1, any ideas.
I have a database which seems to open many copies when it is accessed over the internet (via ASP) but If I open it on my compter over my LAN then it seems to open 'exclusive' and those on the Internet get the error message that 'file is already in use'.
So I have devised a method where I will make a copy of my database on my computer, clear all the stock levels and then add in the new stock. My question is - how do I write a query that adds the 2 stock levels together?
I've written a db which I will be putting on someone elses machine.
I want it so there is a short cut that directly opens the db from their desk top. I'm happy about setting up the 'on open settings' so it goes to the default page etc, however what I do want to avoid is the macro warning at the start when access opens up.
I know one way to avoid this is to drop the security level down from medium to low. It mentions that this is ok providing the machine in question has good anti virus software installed - which it does.
So, here's my question. Is it really ok to drop the security level down or am I potentially going to screw up my mates machine . Alternatively, is there a better way of doing it
I'm fairly new to access, having done a lot of what would have been easier with access in excel. I've decided to make the switch but need one particular thing that I can't seem to work out.
I have a series of "Courses" which contain a series of "modules" and each module has various amounts of data that needs to be accessed.
Is it possible to have module tables embedded in the courses table. Or, do I have to just have one massive table that contains ALL the modules with a field that says what course they belong to. Then make queries that display only the modules of that course? Or is there another way to do it?
I also haven't gotten to learning about queries yet, so if this is the way to do, can anyone explain how or better yet, point to a tutorial that can explain how this is done?
I run a school library database (Access 2003) and want to set up a password logon system so that different users can get at different parts of the program - e.g. Administrator (everything), Librarian (can add and delete records but not change the database design), Student (can only access certain areas, read only).
I've tried using the security wizard to set this up, which seems OK until you then open the database again. I would expect to then get some sort of log-on screen next time I open the database, but instead it opens normally, but then won't let me do anything because I "don't have permission". I had to restore a back-up copy.
I must be missing something obvious here. Is there a place to look for instructions? Couldn't find anything useful in the help screens.
I have produced a database in Access 2003 and saved it as an Access 2000 format. However when it is used on PC's with Access 2000, some (not all) users are getting messages displayed when they open the database that Access cannot open the database due to the Security Levels.
Can anyone tel me where I can find the security level settings on Access 2000, I've found them in 2003, but not 2000!
