General :: Sum Fields In Query
May 13, 2015
I am currently running queries from a table and I get a list of part numbers and the totals. I would like to collate this into one row for each material instead of multiple - so essentially it would be like using a pivot table and summing the totals.
Example - currently looks like this:
Material Total
F12 33
F12 21
F12 5
F13 44
F13 60
But I want the query to produce this:
Material Total
F12 59
F13 104
View Replies
ADVERTISEMENT
Mar 17, 2015
I have a query which working fine:
Code:
strSQL = "Select [nazwa], [id_wycena_pre], [Format_(X)], [Format_(Y)], [kolory p], [kolory t], [nazwa_id], [iloscstron], [numerarkusza], [naklad_pracy], [Kolory P], [Kolory T] from tblGoraZleceniaNowaWyceny where [id_wycena_pre]=" & Forms!frmWycenyObszarRoboczy!ID_wycena_pre & " and [nazwa]='" & "środek" & "' or [nazwa]='" & "okładka" & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)...
But now I would like to add next condition to this query
Code:
where [kolory p] is not null or [kolory t] is not null
- these fields are string
I don't know how to write this condition and add to my exiting strSQL string
View 4 Replies
View Related
Aug 13, 2013
Currently using Access 2007-2010. I have a text file that I am trying to import into Access. I am having issues with the time fields. I need to be able to sum them in a query or report. When I import it in, Access is put the 12:00:00 am as default and adding numbers to achieve the 11 character field. I need to be able to sum these fields later in the database. The file looks like this:
ACD Calls Avg ACD Time Avg ACW Time ACD Time
Totals 103 3:49 1:07 6:33:45
2 1:25 :00 :02:49
8 2:02 :00 :16:15
2 3:17 3:06 :06:34
17 4:49 6:06 1:21:53
View 1 Replies
View Related
Jun 16, 2015
Basically, I have a table showing our clients and when their next service dates are:
I need the query to show NEXT SERVICE DUE dates in January, AND/OR if NEXT LOLER has any January dates.
These should show as either or both fields have January dates:
Next Service Due: February
Next LOLER: January
Next Service Due: January
Next LOLER: December
Next Service Due: January
Next LOLER: January
I managed to do the NEXT SERVICE DUE with January dates, but not both
View 3 Replies
View Related
Nov 7, 2014
I have a query criteria where the data in the query field are numbers.
The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.
Code:
Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Also tried
Code:
[FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox
Code:
Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2]
Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5]
Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]
[Code] .....
View 5 Replies
View Related
Sep 5, 2014
I'm trying to concatenate two text fields into a memo field using an expression in a select query. My problem is that the text fields together end up more than 255 characters, so I need the resulting field to be a memo instead. I can't change the underlying text fields to memo fields because this is a large database used by others who need those fields to be text.
View 3 Replies
View Related
May 29, 2015
I have a main screen that has a tabbed form in it. Each of those tabs has a sub-form in it that displays information and allows some information to be updated.
In one tab I have a query based sub-form that returns information related to the project. This information cannot be manipulated or changed as it comes out of a company managed database. I would like the analysts to be able to add more granular information to a new table by inputting information in a dropdown field for one of the new table fields, but utilizing two of the existing sub-forms fields as identifiers in the new table.
View 14 Replies
View Related
Oct 24, 2014
I have a invoice system I have created in Access and it did used to work perfectly and then maybe an update Who knows stopped the system working.
I have an customer order screen that has customer details and then a subform within the same form this takes a total of items & costs for this order and then it entered it into the customers table from the order_Details table using me.Order_total = Order_Total.
I know this is bad design but I store it because once the order is places I need the total to be static because the invoice has been sent and so if someone changes the order then the total owed doesn't change.
I then have a reconcile form which is on a datasheet form straight from the tab;e so it is editable which has a checkbox that then once ticked copies the total from the Order_Total in the table to the reconciled field in the same table and then I use a report to show who owes me still.
So I have made a Select query from the Orders table & Order_Details table and used a Group by Order number (Which is the link between the 2 tables) this shows correctly but now not editable because of the rules So I am trying to add an editable checkbox on the same form.
I tried to use a dlookup makes all of the boxes either ticked or not. I've been looking at Recordsets but I'm unsure if this is what I need or not really
So really I just need to see if I should be putting the Total from the Subform into the mainform and then entering it into the table (Like previously) using a calculated unbound field and then using the Afterupdate event to insert the data into the table. but for some reason it just wont work.
I can get the OnClick to work but then as soon as i go to the next record for some reason it then resorts to 0.00 but then the table shows correctly which ever record the form is working on.
Or should I be using the new query based idea to create the reconcile form and if this is the best way I just don't now where to start on how to get a multiple table query that I can then add a reconciled checkbox which then copies the Order_Total from the table to the Paymet_Recieved field.
View 13 Replies
View Related
Apr 30, 2013
Any way in report that I have 2 reference queries just 1 is to populate all details and 2nd query to filter details and will be the final reporting information???
View 5 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
Jul 15, 2014
I have an Access 2010 database that tracks Classroom Training. I have a "Start Time" field for each class and a "Duration" field for the length of each class. The Start Time field is a date/time data type and the Duration field is a number data type set to Double with 1 decimal place.
Example:
Start Time = 8:00
Duration = 1.5
So the class should have an End Time at 9:30. I have been able to convert the "Duration" field to a time field for example 1.5 now displays 1:30.
My question is how do I add the 2 fields together to get an End Time of 9:30 ?
View 5 Replies
View Related
Oct 9, 2013
I am trying to validate two fields in a form against each other. Neither are required, but If one is filled in, then the other must be filled in. If neither is filled in then they can both be blank. It is for a time, and description related to that time.
So if they enter in 5 hours, they have to type in the description field describing those 5 hours. If they type in the description field, they have to type in the hours. Vice versa. Or they can both be blank.
Right now I put in the table properties validation rule
([Other Description In] Is Null) Or ([Other Time In]>0)
This works half way. It stops them from putting in a description without time. but they can still put time without a description.
I can't figure out how to do multiple validations of the same two fields. If I do an And or another Or statement it breaks the whole thing and they can enter with no restrictions.
Also, I tired to do it at form level for the other half (to stop them from putting time without a description) but it brings up a pop up and they cant tab to the description field to enter a description without putting time back to "0" first.
View 5 Replies
View Related
Aug 20, 2012
How do I change all the currency fields (many many of them....)from $ to currency without going in and changing every form and fields manually.
View 3 Replies
View Related
May 24, 2013
How would I go about importing 850 fields into access (Multiple tables) with its 250 field maximum?
View 8 Replies
View Related
Feb 1, 2014
I'm using the Access Find command from the ribbon.
Problem is Access searches only the field that has the focus.
I must first click on the field and then use Find.
Any way to get Access to utilize Find to look in all fields?
I have set General search in the options.
View 8 Replies
View Related
Sep 16, 2013
I have a score marking template for an internal audit. A criteria is either met, or it is not. All of these questions have Yes/No data type. each question has a weighting. I have written a VBA function to work out the total score by using a bunch of If statements.I need to store the final result in a table, by entering the answers (yes or no) into a form.
View 4 Replies
View Related
Jun 19, 2012
I am very new to Access and I am not familiar with SQL. How I can create fields that are similar to each other?
Let's say I have 3 fields in my table: Name, State, Area Code.
I want it so that each time I select "Name" the appropriate State and Area Code populate. Anyway I can do this?
View 3 Replies
View Related
Apr 6, 2014
I actually have about 13 separate fields in a table I'm trying to summarize in a single report field. Each field corresponds to a particular action taken. If the action was taken the field value is 1, if the action was NOT taken the field value is 0. The report needs to contain a single field that lists all actions taken. I tried doing this with an If statement formula on the report, but it does not work.
=(IIf([ACTION_VQ_SCAN]=1,"VQ Scan")) And (IIf([ACTION_OTHER]=1,[ACTION_OTHER_SPEC])) And (IIf([ACTION_CULTURE]=1,"Culture(s)")) And (IIf([ACTION_DRUG_THERAPY]=1,"Drug Theraphy"))........ and so on
It looks like it expects all if statements to be true, thus the formula does not return anything. Usually only 1 or 2 actions are actually taken, but I need to account for the possibility of any and all combination of actions.
View 3 Replies
View Related
Aug 6, 2014
I have a large application that was built 8 years ago and the user now wants to remove some of the data fields. Is there an efficient way to figure out what objects would be affected if we delete these columns?
View 3 Replies
View Related
Apr 10, 2015
Here is an explanation of my database and what I am trying to accomplish. The tables and their fields I have in the database are as follows .....
EmployeeT
EmployeeID - Primary Key - Number (not an autonumber because they are county assigned)
LastName - Text
FirstName - Text
EmailAddress - Text
JobTitle - Text
MobilePhoneNumber - Text
Notes - Memo
[Code] .....
All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either "ES Building" or "D3" when inputting the data).
For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.
View 11 Replies
View Related
Aug 19, 2013
I've been creating a database. I prepared forms to enter records. In these forms there are many fields. I want some fields to stay fixed while going up to next record unless changed. How can I do this?
View 6 Replies
View Related
Sep 27, 2012
query is refering to 2 tables
Table_1 with below column
Mode
6
6
6
6
6
4
4
Table_2 with below column
Type
06
GL
PL
04
16
Both tables are joined, and the pseudo code i'm trying to get to work is;
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
I have tried below, but doesn't work.
Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") , returns incorrect results.
Ideally would like this
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
or
if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH
View 6 Replies
View Related
Aug 23, 2013
Im in the process of developing my first database for my bonsai (small trees) collection. So far, ive developed a table of each of the trees I have, with fields such as Species, Where Bought, Cost etc and ultimately a link to a recent photograph (my next challenge). Ill be adding to this as my collection grows (in number and size). To avoid information duplication I have a separate table of care instructions for each species of tree with type, position they favour (eg Hardy/Delicate), watering requirements, wiring advice etc.
I now want to have a field in each of the records in the first table to its particular species care instructions as simple as that, or so I thought. ie for each different pine I want a link to the care guide for pines, and only pines. And preferably in a table format.
I need a one (second table) to many (first table) relationship and have linked them appropriately through primary field (species) and foreign field (care details). I have tried various Controls in the Design View but cannot get the field to show just one care record (ie the one for that particular species of tree). I realize this is possible, as its a common feature of a good database, and have found it being used in the various templates available - but not how they were created. The best Ive got to is using a Combo Box but this shows all of the care details records and in a column view.
View 5 Replies
View Related
May 29, 2014
i have code to open up an email from a form, what i would like is certain field values of the form to appear in the email
View 5 Replies
View Related
Nov 11, 2014
I am importing data from an Excel spreadsheet in Msaccess table. I am using Docmd.Transferspreadsheet method. I am able to import the data, but what I would like to do is to import only certain fields from the spreadsheet. I am able to do with the wizard, but the Transferspreadsheet does not have that option.
View 7 Replies
View Related
Jul 2, 2012
I am trying to take similar fields from unrelated tables. both fields contain information about the age of my clients. The tables they are in are unrelated, as they are different types of clients, and therefore can not find anyway to relate the tables. I need to come up with a total number of clients at particular age groups. Meaning i need to know how many clients from both tables are between the ages of 8-20, even though the records have no relation. All i need is a count, i dont need to display the records, i dont need to change anything, I just need to know how many.
View 4 Replies
View Related
