Help A Newbie Understand Simple Math Functions

Aug 6, 2006

I am completely new to Access but probably all I will need is a gentle push in the right direction. I'd like to think I'm fairly quick at learning new things. Thank you in advance, here's where I am, (first step anyway):

Table 01 Parts List
ID - name - cost

Table 02 Item List
ID - nick name - description

Table 03 Items to parts relationship (this is where I fall apart)
ID - Item ID (relationship to table 02) - Item nickname - PartID (relationship to Table 01) - Part name - Part quantity.

What I am trying to do:
I resell items in table 02 which are built of various parts from table 01. I need to generate a table that says:
Item 1 is built from 4 of part 1, 2 of part 2, 9 of part 3, and the total cost of parts for Item 1 is x. I want to update the parts list cost on a regular basis and have the item price change reflected.

If I can understand how to make this work, I can ultimately add in labor and overhead etc etc. Baby steps for now.

Thanks again
-Richard

PS
Here's a copy of my sad little db

http://www.biggsandsmith.com/temp/Trial-db2.mdb

View Replies


ADVERTISEMENT

A Simple, Yet Frustrating Question From A Newbie

Jul 19, 2007

Hey all,

Just found the forum -- love it. I'm hoping it'll help me out with a lot of my problems. I've extensive experienced coding for PDAs, and with other databases or VBA-based programs, but I've just run into a frustratingly simple problem with an Access database I'm building for medical school (I'm a second-year med student).

Here's the situation: I've two linked tables -- e.g., TblNeuroQuestions and TblNeuroAnswers -- in which the primary key in both is the Question # field. I've created a form to easily enter data enter the questions table (question #, question, and 5 answer choices.) What I want to do is have the question text automatically be copied into the corresponding Question field in the TblNeuroAnswers table, so that I don't have to do it manually (the structure of the TblNeuroAnswers is merely Question #, Question, Answer (A,B,C,D,E), and Explanation). In other words, I want the entry of the question data in the main form (the NeuroQuestions form, which enters data in the TblNeuroQuestions table) to automatically be entered in the corresponding Question field of the TblNeuroAnwsers table.

It seems like it should be an easy thing to do; run a simple update query, for example. But I'd like the form do dual entry, so to speak. Any suggestions?

View 2 Replies View Related

Newbie Confused With (probably) A Simple Query Issue

Oct 23, 2004

Hi, I've looked all over the forum for an answer to my problem and can't find it. This will probably make regular contributors groan, but I'm totally confused and if someone could help that would be ace.

I've got a popular budget airline website (never mind it's URL, I don't like Spam either) and want/need to move it to Access and thence to run user queries via SQL.

So far so good.

So I set up an access DB:-

I've got three tables:-

Airline Info has three fields:- Airline Code (3 letters, primary key), Airline Name and Airline URL.
Airline Routes has four fields:- RouteID (9 letters, primary key), Airline Code (3 Letters), From Airport Code (3 Letters), To Airport Code (3 Letters).
Airport Info has three fields:- Airport Code (3 letters, primary key), Airport URL and Airport Name.

Hopefully anyone reading is still with me.

I've set up the relationships so that Airline Info.Airline Code is linked to Airline Routes.Airline Code.

Now I want Airline Routes.From Airport Code and Airline Routes.To Airport Code to both link to Airport Info.Airport Code (the ultimate idea being to find the Airport Name), and Access will let me do that but therein the problem lies.

Essentially I want a report that would say (eg) Jet Blue fly from John F Kennedy New York to Los Angeles.

All I can manage is to get "Jet Blue from John F Kennedy New York to LAX". I can convert the From airport code to its name, but not simultaneously convert the To airport code to its name. It's gotta be something to do with the query, but I don't know what and have spent two days on this now Anyone a) understand all of that b) know what's going on?

Ta in advance,

Alex.

View 7 Replies View Related

Math Help

Aug 21, 2006

I have an artwork book which I am cataloging. Each page of the book contains 24 pices of artwork.
What I need to do is display a page number to whatever record number I enter into the field.

Example 2: I enter any record number from 1140 and the field will display 48 for the page the artwork is on in the book.

Example 2: I enter any record number from 1153 and the field will display 49 for the page the artwork is on in the book.

Example 3: I enter any record number from 457 to 480 and the field will display 20 for the page the artwork is on in the book.

Thanks for any help I can get on this

View 11 Replies View Related

Best Way To Do The Math

Nov 10, 2004

I am a wiz at formulas in excel but am not sure yet how to make them work in access. I have a main form into which I put the date recieved of many items for each facility. Next to each of the dates I have entered I want to have a non inputable text block that will look at the date then do the necassary math automatically. Some of the formulas will also contain conditional statements based off of a date, type and rating. Can anyone suggest the best way to do this or point me toward a resource that explains how to use formulas in access. Here are a few examples of the formulas that are already in place in the excel version.

=IF(I28="Unsatisfactory",C28+60,(IF(F2="AA&E",C28+365,(IF(F2="Major Funds",C28+365,C28+1470)))))
in the above formula C28 is a date entered by the user, otherwise most formulas I need to input are simply (date + 1 year or date + 4 years)

Any advice would be greatly appreciated.

View 2 Replies View Related

Math Funtions

Jul 20, 2007

What the best way to perform math functions on data in a table? I have a form and a subdatasheet in it. I want to be able to: Average out data in cells. I.E One field will have a list of numbers and I want to be able to average those out.

The other is that i want to be able to check the previous cell(year) and see if the number has gone up or down?

Many Thanks :)

View 4 Replies View Related

Failed Math Can't Add

Dec 4, 2004

I have 6 fields that display a price based on a combo box selection
I can get one to subtract from another but not all add up
It should also be a running total so it updates as I pick more options

And how can you get the combo baxes to start out blank they all have
selects already made when I open the form


Thanks I'm fairly new to access

View 1 Replies View Related

Please Help Me Understand This.

Oct 25, 2007

I have always had trouble with this and no matter how much I read I cannot get a visual picture of how to do this.So if any of you could show me how this can be done. It would be appreciated more than you can ever know.A company has many employees.Each employee can have many certificates.So how do I set up the tables and the RELATIONSHIPS(this is the part I can't understand), so when I create a form and enter the different types of certificates they all go to the right table?This is what I have so far.Employee TABLEEmployeeID(PK)EmployeeFirstNameEmployeeLastNameEmployessAddressEmployeeCertificateOneEmployeeCertificateTwoEmployeeCertificateThreeEmployeeCertificateFouretc....toEmployeeCertificateEightCertificate TABLECertificateID(PK)CertificateNameCould someone explain in detail how I can do this?I really would like to understand this.I'm sure it is simple but for some reason I just can't get a visual picture in my head on how to do it.I'm a visual learner, I have to see it work and then read how it works to fully understand how something works.Thanks in advance.P.S. I forgot to mention that I need DATE fields for the Employee Certificates because they expire after a certain time.I was not sure where to put those.

View 14 Replies View Related

Need To Understand Why?

Oct 13, 2004

Hi,

I designed a form to provide options for a group of reports. Each option when clicked, runs a specific report. On my machine when I created and ran the form, worked very well. This database is on a shared network drive with few other people having access to it. When these other users opened this form (they don't have problems running ANY other form), they got errors such as "Undefined Format function in the expression". I checked on their machines, the Access version and permissions of the database (I haven't set any special permissions on this database at all). It is same as mine. Please help me. Why is this happening and what should be done to avoid it? The database opens in a default shared mode.

Thank you.

View 4 Replies View Related

Help!! I Don't Understand ANYTHING

May 14, 2005

I'm really sorry to bother with my stupid questions, but I'm definitely not a programmer, and after reading about Access, SQL and ASP so much, I feel really confused...

I've already explained what I want to do in the SQL server forum.
It's for an experimental project, I need a database that keeps only 100 records, and every new entry of data replaces the oldest record. So in the end there are always only 100 records.

Well, Lauramc nicely gave me this code that should do that:Code:CreateTrigger YourTable_Trigger1On YourTableAFTER INSERTAS DECLARE @OldestID intSET @OldestID = (SELECT MIN(YourTable.ID) FROM YourTable)--Table name is YourTable, with a primary key of ID.IF (SELECT COUNT(YourTable.ID) FROM YourTable) = 101 --You can also use @@RowCount to get the number of rows.BEGIN DELETE FROM YourTable WHERE YourTable.[ID] = @OldestIDEND

But all I know is how to do is put some simple SELECT statements in an ASP page to call a csv database.

I've realised that I might need to use Access but I don't understand anything.

- Should I use Access and if yes, where am I supposed to copy this code? Should I bring any modification to it?

- Apparently it's possible to create a database from SQL, does it mean not using Access? Where should I type the code? In what sort of file?

Please please, can someone tell me it's actually very simple?

View 5 Replies View Related

Query Math With Dates

Aug 18, 2006

I want to have a query that will subtract a Due Date from todays date and tell me how many days are left/passed in either a positive number or negative just like the DateDiff Function. how do i do this?

View 5 Replies View Related

Failed Math Update

Dec 5, 2004

Its in the control source of the text box I want the total stored in
=Forms![Estimate Maker]!Tank.column(2)+Deck.column(2)+[Internal Valve].column(2)


The data entry setting did the job thanks much

View 3 Replies View Related

Anybody Understand Winfax DDE Pls?

Dec 30, 2005

I am now right at the last fence in what has been an "interesting" assignment! Sending an attachemnt from Access through winfax!

I have found the following excellent code but, the problem is that the documemnt appears in a "preview" kind of pane, I just want it to go ahead and send! Everything else works fine

Any sugestions pls?

Many Thanks
Andy

Public Function SendFax()

'On Error GoTo ErrorHandler

Dim strRecipient As String
Dim strFax As String
Dim strattach As String

'Test for required fields
strFax = Forms!frmFinanceProposal!Text1077
If strFax = "" Then
MsgBox "Please enter a fax number"
GoTo ErrorHandlerExit
End If

Debug.Print "Fax: " & strFax

strattach = "C:InboundFaxesFax.snp"
Debug.Print "Attachment: " & strattach

'Start DDE connection to WinFax.
'Create the link and disable automatic reception in WinFax
lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL")
DDEExecute ChanNum:=lngChannel, Command:="GoIdle"
DDETerminate ChanNum:=lngChannel

'Create a new link with the TRANSMIT topic.
lngChannel = DDEInitiate("FAXMNG32", "TRANSMIT")

'Start DDEPokes to control WinFax.
strRecipient = "recipient(" & Chr$(34) & strFax & Chr$(34) & ")"
Debug.Print "Recipient string: " & strRecipient
Debug.Print "Length of recipient string: " & Len(strRecipient)
DDEPoke ChanNum:=lngChannel, Item:="sendfax", Data:=strRecipient

'Specify attach
DDEPoke ChanNum:=lngChannel, Item:="sendfax", _
Data:="attach(" & Chr$(34) _
& strattach & Chr$(34) & ")"

'Show send screen
DDEPoke ChanNum:=lngChannel, Item:="sendfax", _
Data:="showsendscreen(" & Chr$(34) _
& "0" & Chr$(34) & ")"

'Set resolution
DDEPoke ChanNum:=lngChannel, Item:="sendfax", _
Data:="resolution(" & Chr$(34) _
& "LOW" & Chr$(34) & ")"


'Send the fax - heres where it falls over

DDEPoke ChanNum:=lngChannel, Item:="sendfax", Data:="SendfaxUI"
DDETerminate ChanNum:=lngChannel
lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL")
DDEExecute ChanNum:=lngChannel, Command:="GoActive"
DDETerminate ChanNum:=lngChannel

lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL")
DDEExecute ChanNum:=lngChannel, Command:="GoActive"
DDETerminate ChanNum:=lngChannel


'*************NEED A COMMAND HERE TO GO AHEAD AND PERFORM THE SEND!

ErrorHandlerExit:
'DoCmd.Close objecttype:=acForm, objectname:=Me.Name
Exit Function

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Function

View 2 Replies View Related

Need Help Trying To Understand Options

Jan 18, 2006

ok I have an access db which is all on access. the dbase has been operational now for 3 years and working well however, do to business needs and changes I'm trying to figure out a better way to collect and share data.
So the backend of the database is stored on our local office server when users log on to enter or review data they must be in the local office. We now had the need to access the database remotely from customer sites. Now the way this works is we use a local dialer to connect to our company's network and then are routed to our local server (you can imagine how slow this is) it is impossible to operate the current database this way. So I have been reading different posts and different options (front page, sql, asp etc.) What
I would like to do is convert my dbase to a program which can allow input and review of data both remotely and locally without a huge speed loss. What is the best approch for me?

thanks

jon

View 2 Replies View Related

Don't Understand The Logic

Jan 28, 2006

I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?

View 2 Replies View Related

Help Me Understand This Code

Jan 25, 2006

Dim rstComment As Recordset
Dim strCriteria As String

Set rstComment = CurrentDb.OpenRecordset(Me.OpenArgs)
strCriteria = strItem & " = " & nNumber & " AND ProgressionID = " & cboProgressionID

With rstComment
.AddNew
![CommentNo] = DCount("CommentNo", Me.OpenArgs, strCriteria)
.Fields(strItem) = nNumber
![ProgressionID] = Me![cboProgressionID]
![Comment] = Me![txtComment]
![PersID] = 0 'Will be changed by SQL Server
![Date] = Now() 'Will be changed by SQL Server
.Update
.Close
End With

This code is used in a form that is accessed via a main form when a progression is selected. The form that opens allows the user to selct a type of progression and to enter a comment. This data is then transferred to a table.

I get an error:

Error No:3184 Couldn't execute query; couldn't find linked table.

This form is used elsewhere in the database and works fine. The code is exactly the same. The form name is different and the table it points to is the same design of table as the other but obviously named differently. i am confident it is referenced correctly. But you lot are the experts.

I am not trained in VBA and have only just started to understand bits of it.


Cheers All

View 4 Replies View Related

Math Operations On Values Question

May 13, 2005

I have created a table that holds the inventory information of some items i have in stock. What I want to do is this:

1.)When a customer orders something, i enter the quantity ordered and hit a process button. When this button is clicked it automatically reduces my inventory count to reflect the reduction

2.)When I receive shipments, i want to enter the information and hit process so that it automatically sets my inventory counts to reflect the addition of the new units

I am having trouble figuring out how to go about doing this. I was thinking of somehow setting up some textfields and then adding buttons that set fields accordingly.

Any help would be most appreciated.

View 14 Replies View Related

Forms :: How To Do Math With Two Different Units Of Measure

Oct 30, 2013

I have a table with some prices that are charged by the pound and some that are charged by each.

tblPrices
ID 1 - 1.00 per pound
ID 2 - 1.50 per pound
ID 3 - 2.00 per pound
ID 4 - 5.00 each
ID 5 - 7.00 each

My form is set up so that way I count how many containers I have and their weight, separately. I need ID 1 through 3 to do the math based on the pounds (essentially $1 per pound at 36 pounds) and ID 4 and 5 to do math based on how many of each are there ($5 each, and there are 3 of them).

View 14 Replies View Related

HElp Guys, I Can't Understand The Error

Mar 18, 2007

Private Sub cmdSearch_Click()
Dim LSQL As String
Dim LSearchString As String

If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

LSearchString = txtSearchString

'Filter results based on search string
LSQL = "select * from MCS_All_Services_v8"
LSQL = LSQL & " where Country LIKE '*" & LSearchString & "*'"

Form_MCS_All_Services_v8_subform.RecordSource = LSQL

lblTitle.Caption = "Customer Details: Filtered by '" & LSearchString & "'"

'Clear search string
txtSearchString = ""

MsgBox "Results have been filtered. All Company Names containing " & LSearchString & "."

End If




it kept mention that is Identifier Under Cursor Not Recognized? on the line "FORM_MCS_All_Services_v8_subform. RecordSource....

ideas people?

View 1 Replies View Related

Dealing With Null Values In Math Expressions

May 11, 2006

Hi all,

I am in desperate need of some advice. Today I set to work building some math functions in my queries to calculate delays/turnaround times for work, etc.

I have found that where there are null values in my table - the calculation seems to break down.

Let me explain...

In it's simplest form (I have removed some fields and will deal with DATE only, not TIME which is a seperate field) I have the following -

Date_Received
Date_Started
Date_Pended
Date_Recommenced
Date_Completed

I have the following expressions in my query -

Turnaround: Date_Completed - Date_Received
TimePended: Date_Recommenced - Date_Pended
TimeInactive: Date_Started - Date_Received
Workrate: Turnaround - (TimePended + TimeInactive)

The query calculation will only work when all fields contain a value.

For instance, sometimes the work may be started, then completed. Skipping the 'Pended' and 'Recommenced' steps.

Can anyone tell me what I need to include in my maths for Access to treat a null value in the table as zero? Or any other approach.

Please help!!!!!

Thanks

Rob :eek:

View 3 Replies View Related

Baffled: Gosh! I Hope Someone Will Understand And Be Able To Help Me!

Nov 28, 2007

Action:
1) Update the blank fields in a table + add newly imported records but...make sure that there are no duplicates.
I need to Append the records from a table called: XLS_Imp_11_27_07 to my main Table " Invoice Tracking for A/P 10_30".
The fields found in the XLS _ table: Release Dt, Entry Dt, Liquidation Dt may have been populated by a live report found on Internet. Since it's a live report, when Appended, to the other table, it will create numerous duplicates.
The fields found after the 7 first fields in the Invoice Tracking Table may have been updated by users

Is there a way to avoid duplicates and get the data from one table to the other without wiping out whatever is already filled-in?
:(

View 8 Replies View Related

Help-dont Understand Dlookup In This Situation

Jan 17, 2007

Hi i'm after some time saving advice

i'm writing an Access database to quote for windows & doors,
there are many variants like style, width, height, glass types, security specifications and about 4 other options.

what would be the best way of working out how to add the extras for each option,

The price would be based on the style first, then depending on width and height, then on what options were ticked . i.e securtity spec yes/no, then on what glass type, the price gfor that would be based on the entered width x height etc.

is it possible to do a look up, like you can in Excel where it looks at at grid/matrix based on style number and width x height to get the price, but then how would i get it to add the other options which will depend on size and number of openings.

any help greatfully received.....

View 1 Replies View Related

Forms :: Performing Basic Math On A Specific Column / Row

Oct 10, 2014

how to do anything with it outside of tables and queries. I have been tasked with creating a form to do fairly basic things. Via tutorials I have managed to use the Expression Builder to do virtually everything I need except one thing.

I need to pull a value from row 2 in a specific column from a subform and subtract it from row 1 in the same column and subform leaving behind a + or - number for the user. Similarly, I need to do the same but subtracting the LAST ROW from row 1. If there is only one row at the time the operation is being performed, a value of No Data needs to be returned instead of a number.

View 2 Replies View Related

Need: Inserting Pictures. Need To Understand Or Need Better Way Of Adding Pictures

Jan 31, 2005

I am using Access 2003 in a simple database and want to add digital pictures of each item in my database.

Before trying to enhance the database with pictures; the various fields are filled with either descriptive text or numbers. The size of the mdb database file is 464k.

I recently wanted to add a picture of each database entry. After adding a picture that is only 300k in size the mdb file increase to 18MD. Then after adding another picture also of 300k bytes my database grew to 25MB.

In the drop down menus Under Tools - Database Utilities - I ran the Compact & Repair Database. This did not reduce the size.

In the database Form I am using a Bound Object Frame to hold the picture and yes I am not linking the picture I am including it in the database. But, would only expect it to increase the database size by slightly more than the picture size.

I should also point out that the only way I have found to get the picture to be visible (not just an icon of the type of picture file) is to right click on the Bound Object Frame scroll down and choose Insert Object. Click on Create New and select Adobe Photoshop Image. This open adobe photoshop I make sure the picture properties are creating a picture of 300k.

So my question(s) is again. Why is my database increasing in size so rapidly? Is there a better to insert picture into a database?

Regards, Roy (HerlocherR@aol.com)

View 2 Replies View Related

Functions

Jun 15, 2005

i have the following line:

Me.Text2 = Right(Me.Text0, InStr(1, Me.Text0, ".") - 1)

and i would like that if Text0 is "test.exe" i want to return "exe", however, with the above i am getting ".exe" ... can someone tell me what i am doing wrong please ?

Thanks

View 4 Replies View Related

Help With Functions

Jan 4, 2005

I currently have a text box that is calculated off of 6 other text boxes. I want the one field that is calculated to update any time I change any of the other fields. I can do this by putting the same calculation in each ones afterupdate event. Below is one section of my code for the arithmetic, and I want to perform that code many times. Can I use a function to do this, and if so some help would be nice? thanks

Private Sub HEAVY_AfterUpdate()

Total_Sheets = SECOND + HEAVY + LIGHT + SCRAP

Total_Sheets = Total_Sheets * YIELD

Total_Sheets = Total_Sheets + D_Sheets

Total_Sheets.Text = Val(Total_Sheets)



End Sub

View 2 Replies View Related







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