I want to make a query that would show ID 1 once based on the criteria of having both Home and Office. I will apply the concept on a bigger database.
ID is from table 1
Date and Location from table 2
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
View 4 Replies View RelatedI have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
Is this even possible?
I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
fkEmpID
tblEmployee
EmpID
EmpName
[code]....
What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.
I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:
Code:
DateDiff("d",[Forms]![frmReportBuilder]![cboEvent1],[Forms]![frmReportBuilder]![cboEvent2])
But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.Is it possible to assign field names in DateDiff this way?
I have a query where I prompt for a Report_Date to create a new field; Report_Date:[ Enter date for report]
Is it possible to use the result from this prompt as a criteria in a different field?
for example, Order_Date based on the criteria of <=[Report_Date]
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedIt might be a simple question but I don't know how to solve this:
First: I've created a simple query which gives me the result for a given user input "p".
Code:
SELECT TOP 1 val1
FROM tblPerson
WHERE( (val2)<=[p] )
ORDER BY val2 DESC;
Result looks like:
...|..val1...
...| 12345
Now I've a second query. I would like to get the value for discount from the query above by "putting in p".
Code:
Field.......level......... discount
Table.......adbCases...... qryDiscountGet
Total.......expression.....Group By
Criteria...................[p]=[level]
I have made a database to catalogue my coin collection, I have a table with various fields for logging data about each coin.I want to try and make a query that can easily filter the coins that I have the same year so I can easily see which sets of coins I have in each year, without having to type in each year.
So basically I want the query to detect that I have a 3 pence, 2 pence and shilling all dated 1887 and crown and half crown dated 1945 etc.
This is not the normal calculated fields in query's. What I want is different, I want it to be like
IFF(ShowID="A",[TotalSales],0)
Thing I have is, I want to know all the Total sales in on row that has Show A in it and then Total sales for Show B. Problem is, I have over 130 shows. How would do that?
I am using a predefined query to define the sql statement that will serve as recordsource to a form. Syntax is fine. The predefined query does not have the primary key of the table. The selection in the recordsource is done using this key. So I add this field to the design of the query. I save the query. It says the query is saved. But it is never saved. Run the query again it is not changed.
View 5 Replies View RelatedI have a query that pulls info from a table. One of my fields is frequency and in that field i have weekly, annually, daily, etc.
How can I make the query sort starting at the daily one and move out to annually?
I have a table which has 8 fields. The task is to design a query which always shows the first two fields (or any other two specific fields) and any one of the remaining 6 fields based on the input from the user. I am using a simple form with combo boxes to get the choices from the user.
View 11 Replies View RelatedI have a database about certain accounting datas from week by week and it's growing. I should make a Crosstab query for see the amounts weekly, it's will be exported to an excel workbook, wich have macro's(this is the problem, because the exported field will be bigger week by week). The difficulty of this query is the future weeks. I want to see all of the weeks in the columns. I made a table wich contains the weeks(Hetek_1.CW) wich I want to see, and the source is also contains the items accounting weeks.
Code:
TRANSFORM Sum(CWall_hetekkel.[Knyv# ssz# kltsg]) AS [SumOfKnyv# ssz# kltsg]
SELECT CWall_hetekkel.[Ktgh# kdja], Oka.Oka
FROM Oka RIGHT JOIN CWall_hetekkel ON Oka.Oka = CWall_hetekkel.Oka
WHERE (((CWall_hetekkel.[Ktgh# kdja])=1250 Or (CWall_hetekkel.[Ktgh# kdja])=1251 Or (CWall_hetekkel.[Ktgh# kdja])=1252 Or (CWall_hetekkel.[Ktgh# kdja])=1253))
GROUP BY CWall_hetekkel.[Ktgh# kdja], Oka.Oka
PIVOT CWall_hetekkel.CW;
I Have some calculated fields in a query and want to update it in the table .So,is there anyway to store these fields.Otherwise can i store this Query data every month in database so that when i re-run the query the previous month data will not be affected.
View 1 Replies View RelatedHi!
How do I write this in the criteria box?
Thanks for your help!!!!
I have a table with dates in field1 and an amount of seconds in field2.
field1 field2
01/01/2015, 1345
02/01/2015, -132
04/01/2015, 259
I would like to produce a query that performs a running total in the third column like so:
field1 field2 field3
01/01/2015, 1345, 1345
02/01/2015, -132, 1213
04/01/2015, 259, 1472
This is quite simple to achieve in Excel. (eg =SUM($B$1:B3))
What is the query formula for Access?
In a query, I have the following data. If a number has more than six digits, I need to parse past the fifth digit and that becomes a new field. If it has five or fewer digits, no record is entered into Column2. For example:
Column1 Column2
12345
54321
123456 6
12345678 678
123
9876541 41
6
What simple code does it take to perform this in a query?
I don't know why is it so complicated to add an auto number field in a query. I would like to add a increment number (auto number) on each line and then an auto number on each product.
View 7 Replies View RelatedBasically, some clever soul has used coded fieldnames but then added a description telling you what the field contains, and I need to return this description.
View 5 Replies View RelatedI am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to
AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))
This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA
I've inherited a database that has a table with a large number of Yes/No fields. Apart from normalizing the database, I was wondering if the following was possible:
ContractTable has Yes/No Fields for rights granted for each order (television, dvd, internet etc).
Using a form, the user selects the specific right they'd like to run a report on - selecting this right from a drop-down menu, this is labelled "RightsCombo21" on the form.
When the button is clicked, the right selected is now the "Field" section of the query.
The SQL query right now looks like this (shows all rights sold for the territory):
WHERE ((([Contract Table].[Contract Cancelled])=False) AND (([Contract Table].[Contract End Date])>Now()) AND (([Contract Table].[Contract Type])="License Agreement") AND ((ContractIDAndCountry.CountryName)=[Forms]![TerritorySpecificRightsChooser]![TerritoryCombo7]));
I'd like to add something to this WHERE statement:
AND (([Contract Table].RightsCombo21)=True)
Not sure if it's a syntax issue, or I'm attempting something that isn't possible.
I have a database which tracks the performance of my team and how long it took them to send something out to the customer. We have a target of 5 minutes.
So I quite a few queries to drill down this information. I have a summary query that takes that information and tells me the total amount of things sent, the amount of things sent in time and the amount of things sent late.
My team have to get at least 95% out on time. So how would I go about adding this bit into the query. The calculation is:
The amount sent on time / the total amount * 100
But is there anyway to add this into a 4th column displayed next to these figures?
Looking around a few people have talked about SQL but I no nothing about this and it seems quite daunting, is there a way to do this as a calculated field?
I have a table that contains a memo that is delimited by line breaks. For each of these breaks i need a new record in the query results that i can then use in a Labelling application.
My current query looks something like:
ID | Product | Pack Size
1 | item a | 1x1000,1x1050
2 | item b | 1x1000,20x25
3 | item a | 1x1000
(Where the , is a new line)
Whereas my ideal output is:
ID | Product | PackSize
1 | Item a | 1x1000
1 | Item a | 1x1050
2 | Item b | 1x1000
2 | Item b | 20x25
etc.
I think im supposed to use the Split() Function though i dont have a clue wherw to start.
is it possible to name a field in a query or a table with a variable name?
For example:
I want to choose in listbox "fieldname" the name of the field.
And the query field name looks like:
Code:
forms![testform]![fieldname]: FieldOfTheTable
Unfortunately it doesn't work.
Also if I save the choosen fieldname in a vba function.
Code:
fieldNameFunction(): FieldOfTheTable
This doesn't work, too.
I have a value in an unbound field on a Form, which is 1234 OR 765 OR 356.
In the QBE criteria grid, I used builder to reference this form:
Forms!myform!myunboundfield
The column this is in is for the ID field, which is a number.
However, it is not filtering the data correctly. If I copy the above text and paste it into the QBE grid, then it will work. But when I reference it, it fails. If I change the value to just a number on my unbound field, it works. So the issue seems to be that its bringing across the text as a string and so perhaps effectively puts quotes around it when referencing it.
I have made a function returning True/False values. I used this function in a query and now it return value Error as well...Is there a way to set criteria to values received in that field (0/-1/#Error). I've tried putting Like 0, Like True with or without quotation mark.Also every workaround comes into play as long as it works.
View 2 Replies View Related