I need some help. My brain just can't grasp how to do this.
I have an Access DB. I sell vinyl records. I have an order form. When a customer orders a record, each vinyl record has a unique ID#. No two vinyl records are alike. I have four tables: Customers, Orders, Inventory, & Order Details. I have an "Order Form" form where I input all the info. It's an all-in-one form where you can view and data entry in one screen. The form has two subforms in it.
Here's the problem:
If a customer orders more than one item (i.e. a vinyl record), each Inventory_ID (representing one vinyl record) is associated with one Order_ID which is, in turn, related to one Customer_ID. If I have one Order_ID that has multiple Inventory_IDs (i.e. has ordered two or more vinyl records) that belongs to one customer, how can I calculate that total cost?
Currently, I have a calculated field within my all-in-one form that calculates only the price of the Inventory_ID that one currently sees on the subform. The Inventory_ID item has a set price, for instance $5.00. If I scroll through the ordered Inventory_IDs, the calculated field will only display the price of the product that you currently see. It's not really calculating anything, I guess. It's just bound to that field, but I need to sum up all the price fields for all the Inventory_IDs that are associated with one Order_ID & Customer_ID whether it's a query or calculated field...I just need it done so that it'll display on this form.
Any help would be much appreciated. I can show you whatever you need to see. Thanks in advance.
David
Fields in Query2 TestID from Table1 Select from Table 1
Run Query Can’t update “Select” field
So I’m thinking that I should create a 3rd query to update the summed Quantity Delivered into a new field in table 1. Which would in effect create a stored calculated field.
I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.
What I have tried:
1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.
2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.
3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.
The first two above result in showing no values at all, even if there are some. The third didn't work. How to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL.
I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.
I have an annoying error which has always appeared but I don't understand the root cause of it.
When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):
"syntax error missing operator in expression 'name of field'".
This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:
Conditions: <>"CINEMATIC" And <>"SFX" Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*" [Forms]![FRMscriptPrintReview]![selectLangCombo]
I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.
I have set up a quesry based on 2 forms 1 orders and 2 order details. i have the set a criteria on the report to print a order number
i have then set up a report based on the query. Then i have created a command button on my form up untill here it is fine the problem is when i create the order and send it to print the criterai box pops up which is ok but because i am using a touch screen it will not be viable for the user to enter the number each time he prints i would want it to pick it up from the current order id.
Is there a way to do this as i am sure all invoice systems and everything should be working like this or is there a better way to do this or am i doing something wrong i have attached a screen shot of what si going on
I'm having issues with getting UK dates to show in access.
I created a table and then undertook an insert with the following:
docmd.RunSQL "INSERT into tbl_test([dates]) VALUES (#01/06/2005#);"
Note that my machine is in UK local and the format of the field is short date. The date in access is 06/01/2005. ie 6th Jan , not 1st June as it should be
If I set up a query via the query wizard to look at this field then I see that the date is shown in US format.
I tried to use the following in the field section:
result: format(dates, "dd/mm/yyyy")
and the date still showed up at US ie 06/01/2005. argghhhh. (assume due to the fact that format returns a string and I'm looking at a date field)
I have a main form [mainform] with several subforms on it, two of which are on a tab.
On the second tab [page79] there is a subform [subformlist] which has like 25 checkboxes on it, any combonation of which can be picked by the user.
I need there to be at least one pick required of this subform, else the record won't show in a general query I have, since the table the subform is based on is linked to the record.
Is there anyway to make making a choice of one of at least one of these checkboxes checked a manditory thing before the user can close or move on to the next record?
I've tried making an "Other" box that on the Table has a default value of "True" which I thought would cause it to show in the query record...to no avail.
The only true way to make this work is to have it mandatory that the user pick from AT LEAST one, usually more before the record is complete.
Any help? Other than writing a MASSIVE If...Then statement...?
Or is there a way to make it so the choice isn't required for the record to show up in the query?
I'd post...but my db is too massive, even zipped. :(
I am trying build what I thought was a super simple data base, but this is driving me crazy.
I have a group of building maintenance manuals (12) each of which is subdivided into sections. I have created a list of all the section headings and respective page numbers and have imported them into a table (tblMASTER ) with these fields:
MasterIndexpkf SectionNumber (unique sequential number 1-311) SectionName PageNumber
I have a second table (tblCROSSREF) with these fields:
The two tables are linked in a one-to-many relationship on the respective MasterIndex fields.
I also have a query (qryMaster) which includes ALL the fields in tblMaster which serves as a lookup to the SectionName field in tblCROSSREF.
What I want to do is to select a section number in tblMaster and enter all the possible cross references in tblCROSSREF by selecting the available section names from qryMaster.
This seems to me to be sooo simple, but I am having a real headache!
My primary problem is that when I use the lookup function in the SectionName field (in tblCROSSREF) it returns not the Section Name, but the Primary Key Field number.
This thing is making me feel like the AFLAC duck. Can anyone give me a clue about where I have gone wrong?
I've been searching in vain for a solution to a relatively simple bit of mathematics that I’d like to Access to undertake.
It involves interpolating between data, as illustrated in the following example. Basically have several items, and let's say different "prices" dependent on the number of units purchased. The relationship between the “prices” is not a straightforward formula, therefore I need a query to return an interpolated price for a given number of "units".
DATA ItemID Price10Price20Price35Price50Price100Price 1000 1£70£135£200£315£800£6500 2£23£31£61£89£210£1500
OUTPUT ItemIDUnitsPrice(int) 117? 231?
So, for example if Units <10, Then Price(int) = Price10 if Units = 17, Price(int) = Price10 + (17-10)*(Price20-Price10)/(20/10) if Units = 32, Price(int) = Price20 + (32-20)*(Price35-Price20)/(35/20) if Units >1000, Then Price(int) = Price1000
I'm aware that IIF statements might do the job, but I have 15 different 'Price' points, and don't particularly want to nest them all.
This problem was solved previously in Excel using a clumsy set of VLOOKUP/HLOOKUP functions in conjunction with If statements.
I got a headache to changing my form's filename... i am totally a newbie in Access while in the few months ago... so i just create all those form or table name's for the way i like. But now i had already know how important of management of the filename because when u r using the module to write the code u will be headache to look for the filename that u want in the code. So that is important to add 'tbl', 'frm', 'Qry' infront of the filename.
So do anyone have a good way to edit all the file name with all match to the other in the module with a quick way rather then one by one editing (really tired for one by one) Thanks!!!
Ok, here's something I think I've done dozens of times, successfully. Now I can't get it to work.
I have a Parent Form with three Subforms (based on 3 separate, linked, tables).
I have a combo box on the parent form which looks up a piece of EQUIPMENT by its unique ID and the other two forms display equipment DETAIL and SERVICE HISTORY related to that equipment. It works just great until I use the ParentForm's navigation buttons. The two Subforms change to display the information relevant to other pieces of equipment, but the combo box continues to show the original look up.
I am trying to create a calculated field in a query, that shows money owed from customers. I have 4 different job codes that have their own prices. I also have customers with the amount of hours they owe for, for each job. There are two customers that owe for two different jobs. I know how to write in : Money Owed: [price per hour]*[number of hours]. but i want to have only one line for each customer when i run the query. How do i combine??
But, if both the Annual Savings and SKUAnnualSpendInPounds text boxes have a value of 0 the result of the calulation comes out as #Num!. I assume this is the error because I am doing 0 divided by 0.
Does anyone know if this is the reason for my error and if so is there anyway of changing the #Num! back into a 0 so that I can submit it to my tables.
I have a table called inventory that stores my different products and the quanitities i have in stock. I also have an orders table that handles a customer palcing an order for a certain product and the amount they want. What i want to happen is this: If a customer orders for example 5 widgets, I want my inventory table to automatically reflect the 5 widget deduction from the quantity in stock. Anyone know how to do this?
I developed a query that calulates when a contract is reviewed i.e 5 weeks and 9 weeks after the commencement date. These are called CDE1 and CDE2. This works well and the CDE1 and CDE2 dates are displayed ok.
The query above is: SELECT Contract.ContractNo, Contract.APSNo, Contract.Firstname, Contract.Surname, Contract.AgencyDesc, Contract.FacilityName, Contract.Designation, Contract.WeeklyHrs, Contract.PositionNo, Contract.ComDate, Contract.EndDate, IIf(DateAdd("ww",5,[comdate])<[enddate],DateAdd("ww",5,[comdate])) AS 1stCDE, IIf(DateAdd("ww",9,[comdate])<[enddate],DateAdd("ww",9,[comdate])) AS 2ndCDE FROM Contract;
The problem is that I now want to access contracts under these CDE dates between two dates that is user defined (startdate) and (enddate).
As the above are calculated fields, how can I develop a new query based on the above as CDE1 and CDE2 are already calculated fields?
New to Access and learning the hard way. Im sure this is an easy one, but sure would appreciate the help. I am trying to sum a group of cells and display the total, which i have done. But I also want to store this total in my database. So my question is, how do i move my calculated total to my field that has the "record source" bound back to my field in the database? Hope that makes sense. Thanks in advance.
I have an Access 97 database with a form and a subform for invoice and invoice details. When I change the qty or price in the subform I have a calculated field on the main form that updates. I also have a button that when clicked brings up the payment screen with the totals from the previous screen. The problem lies when I change the qty or price and click the button right away, the next form pops up but does not take the new totals, it grabs the old ones. The calculated fields do not refresh fast enough. I have tried requery, refresh, and even a loop to make it wait before opening the payment form, but nothing seems to work. I'm at a loss. Any ideas would be appreciated
Now I can create a Query that adds, subtracts a specific column, but im having a hard time trying to get the information into a form since it don't seem to allow me to pick the query field which is calculated as a datasource. Can someone tell me how to make a calculated query column appear on a form as a field? Such if I type in monday in the regular table, the form will add a few days to the next available game? I have the information/code already in a query that displays the query right in a new table using the Expr method but I can't get the form to just simply display the field. Someone please help!
I see a few people asked close to the same question but nobody never answered so I am thinking it is impossible
I keep getting caught up in the same point over and over again. I need to save a calculated field's calculation to a table field. The problem is how do I do this? I already know about the rules but I need to do this and store that specific information. Can someone post a sample code? The problem is the code and codes I been using dont' seem to work any more. I was putting
fieldname = textbox2.txt in the form before update box. The problem with that is it would give me an error if the data source the calculation pulls from is not in the table/form.
I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.
I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.
For example:
Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).
So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.
Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?
I have done a query which has calculated fields, but I can't edit the stored fields directly from the query, instead I have to go to the table so as to edit it. Is this the way it works or is something wrong?
SELECT [INSTRUCCIONES DE EMBARQUE].N_OP, [INSTRUCCIONES DE EMBARQUE].[N° FAC EXP], [INSTRUCCIONES DE EMBARQUE].[FECHA CUMP], [INSTRUCCIONES DE EMBARQUE].[DERECHO PGC], [INSTRUCCIONES DE EMBARQUE].[VTO DER EXP], [INSTRUCCIONES DE EMBARQUE].[IMPORTE REINTEGRO], [DETALLE CONTENEDOR].CANTIDAD, [DETALLE CONTENEDOR].[VALOR FOB], [INSTRUCCIONES DE EMBARQUE].[TIPO DE CAMBIO], [TIPO DE CAMBIO]*[VALOR FOB] AS [PRECIO VTA (ARS)], [PRECIO VTA (ARS)]*[CANTIDAD] AS [TOTAL(ARS)], [TOTAL(ARS)]/[TIPO DE CAMBIO] AS [TOTAL(USD)] FROM [INSTRUCCIONES DE EMBARQUE], [DETALLE CONTENEDOR];
Could someone tell me what Event I need to put this code in to calculate my fields when the form first opens?
I have this code on both the After Update and On Change events which is working fine, but, when I first open the form, those calculated fields are blank and I have to perform a change or update before the calculations will appear each time.
If Not IsNull(Me.fcstloc) Then Me.prodvolume = DSum("[volume]", "qryMonthlyEngineVolumes", "[product] = '" & Forms!Projects!product & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12") ElseIf Not IsNull(Me.prodvolume) Then Me.prodvolume = DLookup("[2004volume]", "tblProjectDetails", "[projectid] = " & Forms!Projects!projectid & "") End If
