I'm trying to build a query that groups data by a code that is six digits. however, I only want to consider the first 4 digits when grouping by. (ex. table has code 100023 and I only want to group by 1000). Is there a way to do this within a query instead of modifying the table itself?
View RepliesIs there a way to filter based on a partial field match. I couldn't find anything posted before, but it doesn't seem like an unusual problem. I have an inventory database and have a field for part number. I would like to create a filter that would display only part numbers that begin with "FNQ" for example. The entire part number would include other numbers and letters, of course. How can I do this?:confused:
View 2 Replies View RelatedI understand that a query can accept wildcards in the search criteria but this is different.
I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".
I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.
In other words, the record entries themselves need to be treated like wildcard entries.
How do I get an update query to only copy part of a fields value?For example: The original field has a date in month, day, and year. I only want to update the new field with only the year.
View 7 Replies View RelatedI have a table with Part No. and their correlating descriptions. I have a second table with a Product Number and its assosciated Part No. I want to view all of my Part No. with descriptions and IF there is a match to the Part No on the second table, I want to see the associated Product Number. Below is the layout and SQL on my test tables.
Test_Items1 (My Table)
Part No = 1317667223606 Desc1 = ABC123
Part No = 1317667223614 Desc1 = ABC456
Test_Products1 (Customer Data)
Product Number = 14_01 Referenced Item = 1317667223606 1317667223735 1317667224517 1317667225406
SQL (Which isn't working for me)
SELECT Test_Items1.[Part No], Test_Items1.[Item SX Descrip1], Test_Products1.[Product Number]
FROM Test_Items1 LEFT JOIN Test_Products1 ON Test_Items1.[Part No] LIKE "*" & Test_Products1.[Referenced Item] & "*";
The Desired Results from my above scenario would be this
Part No = 1317667223606 Desc1 = ABC123 Product Number = 14_01
Part No = 1317667223614 Desc1 = ABC456 Product Number = Null/Blank
The Part No from Test_Items1 may not always be the first string from the Test_Products1.Referenced Item. It may appear anywhere withing that group of Part Nos. The customer has them separated by spaces.
Running 2007
I never ran into this problem before.
I have a qry, with showing totals (group by). One of the fields is called comments, which is a memo field. I am only seeing partial comments.
When I redesign the query and take out the "totals", I see the entire comments.
Is there a fix to this?
Trying to get a formula or function for calculating profit for each month between selected dates. Partial month calculations are required.
Source Data (record) examples:
Contract1: Startdate = 6/30/12; EndDate = 9/22/12; DailyProfit = $500; MonthlyProfit = $9500
Contract2: Startdate = 7/7/15; EndDate = 12/17/12; DailyProfit = $600; MonthlyProfit = $11400
Contract3: StartDate = 8/1/12; EndDate = 6/30/13; DailyProfit = $350; MonthlyProfit = $6650
User Query #1:
Notes:
ReportStartDate will always be entered as the first day of the month;
ReportEndDate will always be entered as the last day of the month
The report will always show 6 consecutive months
User selects
ReportStartDate = 7/1/12;
ReportEndDate = 12/31/12
Notable conditions:
-The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0.
-The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value.
-The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
I 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 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.
I was wondering if it is possible to determine weather to show a field based on the values from another field.
Field1, SHOW_Field1, Field2, SHOW_Field2
Field1 and Field are text, SHOW_Field1 and SHOW_Field2 are Boolean values
So if SHOW_Field1 is true, then show Field1 in the query else hide. I was wondering how to do this, I seem to not be able to do this for some reason.
Is Access SQL able to do this:I want the value from a particular column, whose name is derived from the value in the field of another column?
Eg.
Col1 Col2 Col3 Col4 Col5
1 ABC 234 Col2 ABC
2 BCD 345 Col3 345
3 CDE 456 Col2 CDE
4 DEF 567 Col2 DEF
5 EFG 678 Col3 678
6 FGH 789 Col2 FGH
As you can see, I want the value in column 5 to be derived from the value in Column 4.Is this possible to be done in SQL.
I tried writing a SQL query to produce the list of details in year wise DESCENDING order for the input - Agent ID.
In addition to that I need to get the sum of amount fields year wise in the report. How to achieve it???.
Find the attached report for better clarification.
First three column in the attachment is the outcome of the below listed query. Whereas the fourth column is the expected result which needs to be included..
Code:
SELECT CessioneCredito.Data_Movimento, CessioneCredito.Anno, CessioneCredito.Importo FROM CessioneCredito
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
ORDER BY CessioneCredito.Data_Movimento DESC;
How to alter the code in order to provide the SUM of Importo field every year.???
I have a seating chart with 33 tables. Under each table there is a number that represents seats taken. I want to do a query that changes the number when it is a certain value. For example, when the value is null I want it to say "Empty", when the value is 10 I want it to say "Full" and when the value is over 10 I want it to say "Over". If the value is 1-9 I want to leave it as is.I know how to accomplish one at a time but not all three together. I've been useing:
Nz([Table#],"Empty")
IIf([Table#]=10,"Full",[Table#])
IIf([Table#]>10,"OVER",[Table#])
[code]....
the relevant fields look something like this:
TellerNum - Date - Field1 - Field2 - Field2
I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:
TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
View 1 Replies View RelatedI would like queries to do some calculations for me based on a field. The field in the form is a combo field and the user can choose either 1, 2 or 3. Based on the selection, I would like queries to generate some numbers for me.
This is what I have in mind but its not working.
Code : TEST RESULT: If([tlkpTEST].[RESULTID])=1, ([tlkpTEST].[RESULTID])*2, 100)
I would like to know what criteria is used to return records based on a field with data type attachment.
I am using "IsNull" and "IsEmpty" but neither of these work.
I can do it using multiple queries but I would love to find a way to do it in a single query..I have formulated a couple sample tables to show what my source data looks like and the result I would like to get. (See below)
I have a table with items and pricing. Each item will have multiple lines showing the various prices with another field determining what the price is related to.I would like to query the table to output a table showing a single item with the various pricings each in a field. To toss another variable into the mix there are multiple sets of data per item based on a dataid field. So the source has , Item,, Type, Price and dataID. I want to show Item, Price1, Price2,Price3, for a specific itemID.
Source Data
itemtypepricedataID
10115.01aaa
10126.75aaa
10139.95aaa
10227.25aaa
102313.25aaa
103119.95aaa
10327.25aaa
[code]...
I would like to filter for only the aaa.how to do it in a single query.
For my study on academic research I need to match patents that refer to academic research as prior work with the actual prior work.
I have two tables (see attached images below).
One regarding AcademicPublications (AP), which is neatly organized with title, year, journal, volume, pages, first author, etc... 480,000 rows
One regarding Patentswhere all this information is hidden within one field, in the most messy way possible... for instance, a field could have:
Quote:
Sugita et al, "Nonsurgical Implantation of a Vascular Ring Prosthesis Using Thermal Shape Memory Ti/Ni Alloy (Nitionl Wire)," Trans. Amer. Soc. Artif. Intern. Organs, vol. 23, pp. 30-34.
or
Quote:
Willingham et al., Cell 13, 501-507 (1978).
Or many other ways.
I want to create a new table that is set up like this:
Patents.PatentNumber | AP.ID | Patents.Reference | AP.Title | AP.Year | AP.Volume | AP.PageStart
The question is: How do I match different fields from one table on one field of another and make it return another field (the ID)? Some references are too horrible to match, but I need as many as I can get.
I can imagine two queries would give me the bulk:
A match in [Title] AND [Year]
A match on ([SourceTitle] OR [AbbreviatedSourceTitle] ) AND [Volume] AND [Year] AND [PageStart]
I understand that I have to make use of the Like "*"&[value]&"*", but how do I make it return the matching ID?
Have two tables: Assignment and StudentHeader - they are related by AssignmentGUID
Have the SQL:
SELECT StudentHeader.[Student ID], StudentHeader.GUID
FROM Assignment INNER JOIN StudentHeader ON Assignment.GUID = StudentHeader.[Assignment GUID]
WHERE (((Assignment.[Assignment Type])="Q") AND ((Assignment.[Assignment Number])=2))
GROUP BY StudentHeader.[Student ID], StudentHeader.GUID
ORDER BY StudentHeader.[Student ID], StudentHeader.GUID;
This returns:
Student IDGUID
al003072274
al154636303
al154636463
al595048272
al941626429
am180614426
am750033300
am750033462
I want a third field - Sequence - based on the Student ID and GUID, therefore
Student IDGUIDSequence
al0030722741
al1546363031
al1546364632
al5950482721
al9416264291
am1806144261
am7500333001
am7500334622
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002
I tried formatting the source field with Left -4, but it only shows the first four characters in the result:
4084
4084
4081
4078
4078
4078
How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3
I have the query below that return a table like:
PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp
What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)
Code:
SELECT[Q:INV1-Basal_area_plot-spp].PLOTNR,
1 AS period,
[Q:INV1-Basal_area_plot-spp].Value,
[Q:INV1-Basal_area_plot-spp].ID,
[Q:INV1-Basal_area_plot-spp].[Basal_area/ha],
[Q:INV1-Basal_area_plot].[Basal_area/ha],
([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_spFROM[Q:INV1-Basal_area_plot-spp]
[code]....
I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
I have a query that is based on Property & tenants. It runs a report that gives me all the property listed with the tenants & other info for the property's. Sometimes the tenant moves out & the property is Vacant, so i have a Vacant flag in the property table.
But when i run the query it prints out the last tenant. Can i pick up the vacant flag & change the tenant name to Vacant on the report? As i don't want to delete the tenant from the tenant table.
I am looking to get a query to show my list of customers "Grouped By" [CustomerName], that show only the single [CurrentBalance] field for each customer based on the "Last or Highest" [RecordID].
Also, each customer can have up to 4 different [StockType]'s but at least 1 [StockType].So my results would look like this:
[CustomerName] - [StockType] - [CurrentBalance]
Customer#1 StockType#1 5
Customer#1 StockType#2 4
Customer#2 StockType#1 5
Customer#3 StockType#1 5
I have two tables of data. One contains balances for a set of accounts (one unique balance per account, per date). The other contains transactions for a subset of the accounts (multiple records - or possibly none at all - per account, per date)
I'm trying to write a query to return the sum of the transactions from the Transaction table, for each unique account + date combination which is present in the Balance table.
Here is my SQL :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(tblTransactions.Amount) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
This works fine with one major problem; if there are NO transactions for any given account + date in the Transaction table, I get no record for that combination in the dataset.
What I need is for the query to return a 0 in those situations (i.e. I should have as many records in my queried dataset as there are in my Balances table, but SumOfTransactions may be 0 for some of those balances)
I've tried the following but it has no effect on the outcome :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Nz(Sum(tblTransactions.Amount),0) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(Nz(tblTransactions.Amount,0)) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;