I'm Very New To Access & Need To Figure Out How To Subtract One Field Form Another Wi
Apr 11, 2005
Hi,
I am very new to access. I have designed a db for my employer, but I can't figure out how to subtract one field from another without having it subtract fom all others.
My employer is a counselor and wants to know how many units are left per client. There are several diffent services available per client. (88K, 86H, 81H, ect) For instance I tried to use a query, but when it was subtracting
(number of units)-(number of used) It subtracted from all the different service types.
Then I tried to do it on the form and all I get is #Name?
I can email you a copy of the database with a mock client if i'm not explaing myself enough. I know it's probably something very simple and I would appreciate any help. Also, if you know of any good books or websites where I can learn more I'd appreciate the info. I'm learning from Access 2002 Bible.
I have a 'tblStock' with fields 'ProductID', 'InitialStock', 'Buy', 'Sell' and 'UpdatedStock'. I also have a form 'StockUpdate' add values and also add new records to 'tblStock' .
If I have value [100] for IntialStock quantity, Buy [0] and sell [10], UpdatedStock will be [90] (that's done and fine!).
The problem is, I would like to make the UpdatedStock value [90] to be the NEW InitialStock, so that any BUY or SELL will keep updating the UpdatedStock and making it the NEW InitialStock for the next transactions and so on....
Hi, I am working on Forms and subforms in Access. I have a main form, a tab control on the main form, 5 pages on that tab control,a subform on each page and many controls on each subform. Main form's Record Source is maintable and all the subforms have the same Record Source subtable. maintable and subtable have one-to-many relationship. The Navigation Buttons property is set to Yes for the main form as well as for all the subforms. Now here is my problem: I want to view the same record on all the subforms at a given point of time. For example: I go to page1 and navigate through subform1's records and I stop at 4th record and then I click on page2, there I should see 4th record on subform2 and if I click on page4, subform4 also should display 4th record. In other words, whenever I stop at nth record on a page-X all other pages should display nth record. Any help would be appreciated.
I got a form with a field called [copies on hand] so i made a command button and when i click the button, i want the value in the field [copies on hand] to be subtracted by one.....
The code i have thats not working is
Private Sub Command 25_Click() If [copies_on_hand] >0 Then [copies_on_hand] = ([copies_on_hand]-1) End Sub
Most of the project is done...I am stumped on calculating a field for the report...any help is greatly appreciated..thanks so much.
Use a Microsoft Access database to create the tables and column headings show in Figures 1-3 and discussed in the Background section. Create the tables in the design view. Populate the tables with the data as shown. Make sure you create a composite primary key field for the ORDERS table. The key should consist of the fields Invoice Number and Product Number to allow customers to order more than one item.
Customer Customer NumberCustomer SchoolCustomer StreetCustomer CityCustomer StateCustomer ZipContact Name 105Central High123 Main StreetDenverCO80208Mary 107Central Middle35 South ParkDenverCO80278Sue 423JKF Elementary69 5th AvenueCheyenneWY90200Pat 516Toddler Time8000 ParkwayFt. CollinsCO80487Jan 799Georgetown High819 Rt. 909BoulderCO80303Jack
The owner wants to find out how much of each product is selling for the date ll/15/2005. Develop a query in the design view to give that information using the headings Date, Product Name, and Quantity. Save the query as Today’s Sales.
Creating another Select Query
Tomorrow, the owner is going to be in Denver, CO, and she would like to call on the customers there personally. Make a query to display the contact name, school, and street address ONLY for those schools in Denver. Save the query as Denver Schools.
Generating a Sales Report
Finally, make a report to show the dollar amount sold to each customer for each product. To create the report, you will need to:
•Make a query first and then bring that query into the Report Wizard. •The query will contain a calculated field for Total Owed. •The query should show the fields Customer School, Customer City, Date, Product Name, Quantity, and the calculated field, Total Owed. •Name this query “Report Data” •The report will be grouped by Customer School and Customer City, with subtotals for each school’s sales and a grand total. •Make sure you delete any bogus total lines, and make all appropriate fields currency.
Deliverables
Three tables Query output: Today’s Sales Query output: Denver Schools Query output: Report data Report: Current School Sales Defined relationships (I can press the relationships button and will see that the relationships between tables are defined)
In my database I have a list of organizations with a certain goal. If that organization can not hit that goal we have to "reallocate" the left over goal to another organization. As it stands now I have the following fields, Goal (which should stay stagnant), change (to either subtract from one organization and add to another) and New. What I would like to do is have a way to have a way to calculate if there is a change to maybe have a drop drown that when the change is subtracted I can add that to "new" of another organization.
I have a form with a textbox, listbox and a button.
my textbox is based on one of my tables, and when I enter a value (which is saved in my table) in my textbox and press enter, certain values in my table goes to my listbox, and my listbox will just additem whenever I do same thing in my textbox all overagain.
how can I subtract listbox.column(1) items in my table field "Item_Quantity" where my Listbox.column(0) is equal to my table field "Item_Description".
My Listbox column count property = 4 Row source type = value list
I'm sure this is really simple... apologies, but a search hasn't thrown up any clues.
I have a table with Invoice Value, and Commission Value in it. On the associated form I enter the Invoice Value, and also the Commisison value [which 99% of the time is set at 20%.
I am trying to make a button which, when pressed, enters the calculated commission in the form field (and underlying table) - I realise this could be in the 'On Click' property on the form, but in VBA I am after something like.
Private Sub btnCommCalc_Click() tblSales.Commission = tblSales.Invoice * 0.2 End Sub
Could someone help me with the syntax? (I can then move on to using a variable, rather than hard-wiring the 20% ;-)
Here's a sticky one? (maybe...) I have a number of aggregate totals from several queries all linked to the Input Query and all Nz'd as 0's
From the image enclosed the totaling text box (top right left) is calculating the value in the properties box. The value should be 3.52 + 5 - 8 = 0.52 the value showing is -4.475 (which is 3.52 - 8) so I see it's ignoring the 5 value but I can't see why.
The ACDays and ALDays values are both generated by the same module, but with different Leave types. and as I mentioned previously all Nz 0'd
I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.
On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.
On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.
I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.
Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.
=[rptP&LExpensesOverview]![AccessTotalsAmount]
=[rptP&LSalesOverview]![AccessTotalsTotal Sales]
To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.
As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).
I'm creating a new database for an investment brokerage company. The company invests clients' funds for a specific length of time and pays out a return on their investment on a weekly basis for the duration of the investment.
This is my first real database so I'm gonna need all the help I can get. The company has some pretty huge requirements but right now I'm focusing on phase 1 which will be very simple.
Right now I'm working on the database design but there's been one thing which has been bugging me for a while so I figured I'd ask you guys to help me get my head around this.
Let's say we have a table called Investments. This table will include data on the amount invested, the number of weeks invested for, and the ROI percentage. For example...
Investment Record Example Amount: £10,000 Weeks: 10 weeks Percentage: 10%
One of the requirements of the database is to produce a report that will list each week of the cycle and the amount paid to the client. This will require calculations in order to produce this data. Here's an example...
Payment Report Example Week # | Payment Date | Payment Amount | Notes 1 | 1/1/01 | £100 | Enjoy your first payment! :-)
I can see how this can be easily done by producing a query and then a printing a report based on it. However, here's the tricky bit... for various reasons, the payment date or the payment amount for a certain week may need to be modified, and a note value may need to be added for particular weeks.
Of course, I could just create another table called Payments to contain this information, but I don't want to have to enter the data manually. It would be a lot more productive to have the database calculate these values, but somehow let the user modify these values, if necessary. That's what I need to figure out!
Can someone advise me how this can be accomplished? I've been reading up on the Make Table feature, will that do the job? Note that I do NOT want to create a payments table for every client so I'm quite sure.
This is probably a long shot, but I figured I'd post it anyways. We have a form that is used to input customer data. It was written about a year and a half ago and uses some programming in it. It's worked fine on the people's computers it was originally installed on, but recently we've tried to put it on some of the newer computers and when we run the form, it just freezes Access up instead of going to the form log-in screen. The weird thing is, it works on some of the new computers. They all have the same version of windows and the same version of Access, and the code in the form itself hasn't changed at all. I was thinking maybe the form was looking for something that wasn't there, and freezes because of that. Anyways, any ideas would be appreciated.
Situation: I have to seperate tables(table A and B), both have a part numbers field, manufacture field, and manufacture p/n fied. But only one of the tables(table B) has manufacture and manufacture p/n filled in. Also, some part numbers are missing from table A that need to be in B and some are missing from B that need to be in A.
Goal: My question is how do I make a query or whatever i need to tell access to find all the manufacture and manufacture p/n info(related to its designated part) in table B and put it into the fields in table A respectively. I guess i'm basically trying to join two fields in two different tables, i mean i need to the manufacture and manufacture p/n copied over to its related records in table A.
The reason for this is because we are migrating from Quickbooks to a program called Shoptech E2(access based) to do all our supply chain management. Things were correctly imported from QB except for the fact that the manufacture and manufacture p/n was not imported for some reason. Now E2 only looks at table A to pull its info and does not see the relationship, thats why i need manufacture and manufacture p/n copied over to table A to its respective parts.
Hi This is probaly really easy, I want to identify just minus figues in a query ie -5.00. I have tried iif([TotalPrice])<0,[TotalPrice]) this shows no result. Can any one advise. Thanks in advance.
I have a query in which I need to display just the data that was entered from 9:00 p.m. the previous day until now. There is a date/time field called "QtyTime".
OK, this form has a series of buttons that lead to other forms or open up into reports. The problem is I can't figure out how they do that. On the click event procedure of one of the buttons it says =HandleButtonClick(1) . This is NOT a macro. I don't think it's a command button either usually those say [Event Procedure] in the click event. I can't figure it out. Anyone have any suggestions please?
i have got a database which consists of table, queries, forms and reports. I have one problem now is that I wan to do a query that will be able to bring the figure from another field to the current one. ok say i have got Ref_No, Event_Date, Balance_BF, Adjustment, Balance.
so if say this is the first event for this particular ref_no (1234) it will bring the amount from another table (Principle_Table) linking the same ref_no (1234) to the Balance_BF fields, If this is not the first event for this particular ref_no (1234) it will then bring the amount from the previous event Balance field to the current event Balance_BF field.
I am very lost how to do this. Is there a way doing this using query?
This is wat i mean Ref No Event Date Balance_BFAdjustment Balance 06/00014 31-May-06 115140-5,757.00 $109,383.00 06/00014 31-Aug-06 109383-5,757.00 $103,626.00 06/00014 30-Nov-06 103626-5,757.00 $97,869.00 06/00014 28-Feb-07 97869-5,757.00 $92,112.00
U can see that the Balance_BF is actually brought down figure from the previous Balance starting from the second events. For the first event, the Balance_BF is actually draw down from another table (Principles_Table)
First of all, thanks for any help in advance, I really appreciate it. This is my first big project in Access. I am trying to design a database for timesheets for all the departments at work. We have 21 departments with various numbers of employees in each department. At this point, I would like this database to be able to have users enter their own hours each week and print their individual report of hours worked then designated users add employees, look at previous payperiods and print out a master report as well as past reports. One pay period here is two weeks, with the days starting on Saturday, though I would like the database to keep track of one week at a time and then be able to group two weeks together to make a pay period. Each employee can work regular hours in a day and can also take up to 6 type of leave plus work overtime. So there will be 8 types of hours that could be entered. So far, I made tables for each different type of hour and created fields for each day of the week and created relationships to the employee table and pay period table. I made a form in datasheet design, but it didn't work out well. For lack of a better thought for them to enter their hours, I was going to design the form to look like this (but with spaces like a table);
Employee(fn) Employee(ln) Payperiod
Sat Sun Mon Tues Wed Thurs Frid Totals Regular Hours Vacation Used Comp Used Sick Time Overtime Holiday Unpaid Leave Paid Leave
The only thing is that I can't get the forms right and it seems like a lot of wasted space since employees will rarely take holiday, paid leave and unpaid leave. Is there a better way to design this?
I also have to take into account security. I eventually have to be concerned about departments only having access to their own records and employees within those departments only being able to access their own hours (except for designated users to do the master sheet). I haven't had any training or experience in access database security. Is there a way to limit records in a table or would I need to create a seperate database for every department?
I am attaching my table relationships in case I didn't describe this very well.
Thanks again for any help. If you need any further information, let me know.
Hi to everyone. I am trying, have been trying, will probably kill myself trying...to figure out the best way to make tables/relationships with the information I have already imported into my db. If someone could please PLEASE help me, I would appreciate it. I'm a visual person, and I have so many pieces of paper around my desk that I can't even see the top of it anymore!!!
Background info, I am building a db for employees and courses they MUST take. However, there are 60+ courses, only 4 of those courses are frequently taken (the others are more "train the trainer" and such). But which of those 4 courses you need to take depends on whether you're a supervisor or tech. So if you're a supv, you need to take courses 1, 2, and 4. Tech, you need to take 1, 2, and 3. My "dream" is when I later make a form, I can get the boxes to darken for the courses that don't apply to the employee.
Attached is a print screen (sorry it's .doc but I'm limited with the programs on my PC) of my current ONE table in design view so one can easily see how many fields there are.
Now, I realize to normalize, it's ideal to have only ONE subject matter per table. So, in a dream world, the tables that SHOULD be created to be completely normalized are as follows:
T_JobDetails Unit DutyLoc Activity PositionTitle Type
T_Courses (contains date course taken, IF taken) NSPAE NSPAS .......etc..
So, hopefully you can see why I'm having such problems. I know that I could only create lookup lists for "Type", "Para", and "Rank". All other fields, although some do have repeating information, would result in HUGE lookup lists of probably more than 200 things.
I have no idea WHY I can't seem to figure out how to construct relational tables, but I just can't get it! I've built 2 other databases, but always had to come here for help on making the tables. So here I am again, only this is a bit more complicated than those I've posted about before. So I apologize for the length, but I'll try and post EACH field now so whoever may answer can possibly understand my frustration and confusion. The fields I need to have are as follows (separated by categories to shorten length here):
SoldierLastName (txt) FirstName (txt) MiddleName (txt) Suffix (txt) SSN (#) DOB (date) Street (txt) City (txt) State (txt) Zip (#) Ht (#) Wt (#) DOR (date) PEBD (date) APFT (date) Blood (txt) Meds (txt) Allergy (txt) PHA (date) Mobd (yes/no) 93 (date) 8286 (date) Tags (yes/no) Profile (#) FCP (txt)
MomLastName (txt) FirstName (txt) MiddleName (txt) MaidenName (txt) Deceased (date) Street (txt) City (txt) State (txt) Zip (#)
DadLastName (txt) FirstName (txt) MiddleName (txt) Suffix (txt) Deceased (date) Street (txt) City (txt) State (txt) Zip (#)
SpouseLastName (txt) FirstName (txt) MiddleName (txt) MaidenName (txt) Suffix (txt) DateMarried (date) DateDivorced (date) DOB (date) Street (txt) City (txt) State (txt) Zip (#)
ChildLastName (txt) FirstName (txt) MiddleName (txt) Suffix (txt) DOB (date) Street (txt) City (txt) State (txt) Zip (#)
EmployerName (txt) Supervisor (txt) Street (txt) City (txt) State (txt) Zip (#) LastUpdate (date)
Ok, now perhaps someone can at least see where I'm coming from...or perhaps I'm making a mountain out of a molehill...it's possible. But the hard thing to remember is that one soldier can have more than one mom/dad (with divorces and such), more than one child that may live at more than one address, as well as more than one spouse (former and current), and last, more than one employer.
I'm pulling my hair out...strand by painful strand...trying to figure this out. Can anyone, ANYONE please please please help?!? I'd be soooo appreciative of any advice!
I finally am figuring out this Access db stuff. I have managed to produce what I believe are normalized tables, built a query or two, and now a report. However, what I need to do now is something that I don't even know is possible. Further explanation is that I need to produce a query that automatically counts how many of a particular course has been taken. So, in a sense the query would show that 500 people have taken course A. If I have to build a query for each course, that is fine (because there is a course B, C, D, etc.). The problem is that the tbl the courses are contained within has the following properties:
I thought the easiest way to build a query would be by the CourseID. Problem? Well obviously ALL courses are contained within this field/column.
I was able to produce a query with the following SQL that produced totals for all courses...which was fine.
SELECT tbl_EmployeeCourses.CourseID, Count(*) AS Expr1 FROM tbl_EmpoyeeCourses GROUP BY tbl_EmployeeCourses.CourseID;
But the problem is that I can't get these totals in their OWN queries. When I print the report, I want it to say in the report footer (obviously in separate textboxes) "Course A total:___" or "Course B total:___"
I know I will later be able to build on that to produce a grand total if the need should arise. but it's pulling these apart now that's whipping me.
Does anyone have any pointers or can tell me what I am doing wrong please? I would greatly appreciate it. Thanks so much!
