Greetings everyone. I am not an Access Guru so please take me request with a grain of salt.
I am the marketing admin and web developer for the company I work for (co-op telcom). I have built a simple Access database that interfaces with a legacy service order system we utilize company wide. The database simply tracks marketing jobs we work and publishes them as service orders to the system. The problem I have is that I have to format the job number very specifically to fit the legacy systems expected input requirements and I haven not been able to figure out how to do this.
Below is an example of what a series of job numbers must look like. Note the date info preceding each example
Jobs entered on April 20, 2007
070420-01
070420-02
070420-03
Jobs entered on April 21, 2007
070421-01
070421-02
070421-03
The structure of the string is YYMMDD-'Sequential number restarting each day'
The date portion is no problem and nor is the dash. Where my problem lies is in the sequential number restarting each day.
Any thoughts on how to do this. I am guessing this should be some kind of VB or if then statement, but I have no idea where to begin.
To show that I appreciate the help anyone can give with regard to this mater, I am offering a free 1 year hosting package (200M disk space/2 Gigs of bandwidth/mysql/php/unlimited email addy's w/hsphere control panel) to the first person who can help me with this problem.
OK this is a pretty big question, but here it is....
I am not quite sure that a query is the best thing to use in this case, but I think it might be possible. Here is what I need to do: I have a table with information about oil well production. In this table the production of each oil well is recorded once a month (Barels of Oil, Water, and amount of Gas produced, and the number of days that the well produced in that month). This table ranges in dates from 1986 to 2006 and has records every month in that time span. You can see a sample of how the design of this table is in the attachment (Table: [Well Data]).
What I need to do is have a query look at this main table and create another table with some calculated fields. This new table will have only 1 record for each well. You can see a sample of the table that needs to be created in the attachment (Table: [New Table]). In this table, the first 8 columns are not calculated at all, they are just recorded from the main table. The next columns are calculated. As you can see in the attached preview, the query needs to find the first 180 days (6 months) that a well is producing and sum the oil, gas, and water in that time. It also has to do this for the first 365 days (12 months). Then it has to record the earliest date that a well has in the [Well Start Date] column and the latest date a well has [Well End Date]. And last, it has to determine how many total days the well was producing (Subtract [Well End Date] - [Well Start Date]).
I know this is a lot to comprehend, so please ask questions if you don't understand me. I really appreciate your help!!
OK Guys, I am building a sales database for a client he has supplied the text files which I have loaded into tables. Problem on the sales file there are multiple part numbers in the part field these are separated by '-' the same format is on the Qty and value fields. The logic behind this is: For the 1st part in the part field the qty and the value are the 1st in there respective columns, for the 2nd part in the part field the Qty an value are the 2nd in there respective columns and so on eg
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
Hi, im relatively new to access but my problem is fairly complex.
I'm creating a database for a school project, and it is focused on improving the way a flying club runs. I have been able to solve most problems myself, but this one truly has stumped me.
The basics: There are two tables - Instructor Details - Flight Completion
And one form - Flight Completion
On the instructor details table i have a field called 'Hours Flown' The purpose of this field is to show the total hours flown by an instructor. On the flight completion table i have a field called 'Flight Duration' This field is for the duration of the flight the instructor just flew.
I want it so that when the instructor presses a button on the flight completion form, the duration entered into the 'Flight Duration' field is added to the 'Hours Flown' field and then the 'Hours Flown' field is updated with the new total.
I hope that is clear to you, but if not please let me know and i will try and clarify it for you.
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales and then adding Criteria like: EndDate = #10/07/2015# It doesnt work.
Hi, I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number: OrderNum: [OrderPrefix] & [PartNum]
This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.
How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*
Having been trying to do this for ages but having trouble with syntax.
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I have a table with customers, one with products and one transaction table. How can i produce a report, based on a query that will add up the total any one particular customer has spent to date from the transaction table?
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
i have opened a field (which was created in a table) in a form as a calculated fieled the calculations are ok but only in the for the same field in the table remainded empty
how can it be set so the table's field will get the clcaulation ?
I have this query which selects two fields and calculates it in another field. But the calculated field has more than 1 expression.Eg If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..
This seems a daft question but I have searched the forums and can't find an answer so I hope someone can help me.
I have a table: Register(RegID, Pupil, Activity, Duration, Preparation)
I have a query that I want to calculate the total hours the pupil has spent on activities.
The Duration and Preparation fileds are held as number of minutes so for a 1 hour event the number 60 is held.
I have a query with 4 fields: Pupil SumofDuration SumofPreparation TotalHours = ([SumofDuration]+[SumofPreparation])60
The problem I have is that TotalHours is calculating as an whole number. e.g. I have a Record Pupil = 701 SumofDuration = 270 SumofPreparation = 0 TotalHours = 4
But I want TotalHours = 4.5
I have set the Duration and Preparation field sizes to Decimal and Format to General Number.
I am trying to do the following in a form. 1) (Travel hours + work hours) * dollarperhr to get the "Total" bill to a customer.
2) The results will be put in a field "TotalDollars" that is a part of a table.
Please help.
I tried to put an event (on change) on the "dollarperhr" box so that after entering the values in "travel hours" + "Work hours" * "dollarperhr", the calculation will be done to populate "TotalDollars", but it didn't work.
I have a table named= Commun tasks which has 3 fields= [Commun tasks],[Quantity] and [Unit Cost]
I created from that table a form who shows these 3 fields. I also created in that form a calculated field (named Total) who shows the Total. I simply wrote in the control source =[Unit Cost]*[Quantity]
I have another table (Project Information) who has 3 fields=[Prior total],[New Total],[cost addition]
What I need to do is each time the [Quantity] or the [Unit Cost] are changed, I need to show the [New total]. But also i need to show the [Prior total] which is based on the [Quantity] and [Unit Cost] before the change. So i think i need to store the calculated field (Total) in a table, but I don't really know how to do that (I,m a newbie).
Ok let's simplify my question
How can I store in a table a value from a Calculated field? I know that we should not store calculated value but in my case I need to store them.
How to Add a calculated field named CustomerName as the first column that is either the company or the concatenation of the last name, a comma a space, and the first name. Sort in ascending order by Customer Name ?
I've came up with CustomerName: IIf([CompanyName] , [LastName] & ", " & [FirstName])
I am using a function to calculate the number of days in a quarter. I have the Control Source for field "A" set to =CalcQtr1Days([AdminDate]). I am calling CalcQtr1Days and passing the AdminDate. It calculates the number of days I'm looking for and returns that number in my field. Then, I want to use that number, along with DaysAbsent, to calculate DaysPresent. So, in field "B" Control Source, I have =CalcQtr1Days - DaysAbsent. This gives me #Name? in field "B". I've also tried =DaysPresent = CalcQtr1Days - DaysAbsent but doesn't work either. What is the syntax for using the value calculated in field "A" in my calculation for field "B"?
I'm trying to get some equations to work for a database involving ski lifts.If I can get it to work right it should give me the resulting tension on the lift. I have a two combo boxes that contains two values, counterweight and Hydralic, the other one has the values extends cylinder and retracts cylinder.
if counterweight is selected it should calulate the value with the equation ([L/bore]*[w/Rod]*[D/quan]*145+[additionalweight])/[counterweightparts]
If Hydralic and extends cylinder is selected it should calculate it with (3.14*[L/bore]^2)/(4*[D/quan]*[additionalweight])
If Hydralic and retracts cylinder is selected it should calculate it with (3.14*([L/bore]^2-[W/Rod]^2))/(4*[D/quan]*[additionalweight])
I'm trying to get this to work using IIF statements in a query but I can't seem to get it right.
I hope I was clear enough in my explanation. If any body know how to get this to work or nows a better way of going about it please help, Thanks.
