Funky Query Question
I'm building a new query that uses 2 inputs; a query and a table. The input
query has 3 fields (a,b,c) that combine to be a unique identifier for each
record. The input table has the same 3 fields (a,b,c) that make a unique
identifier for each record.
I'm trying to match records from each input source using all 3 keys (a,b,c),
incldung a field from the table in the new record. But my new query output
only includes the first match of the unique identifier (a,b,c), and doesn't
pick up the rest of the matches (a,b,d - b,c,f - c,d,h - etc.)
I've tried various combination...Total field on query
I used the fomular below to sum the fields. I works fine. I need to add the
table name (Student) to this fomular. Please show me how. thanks. Chi
Total Score: Nz([Section 1 - Pain Intensity],0)+Nz([Section 2 - Personal
Care],0)+Nz([Section 3 - Lifting],0)+Nz([Section 4 - Walking],0)+Nz([Section
5 - Sitting],0)+Nz([Section 6 - Standing],0)+Nz([Section 7 -
Sleeping],0)+Nz([Section 8 - Social Life],0)+Nz([Section 9 -
Traveling],0)+Nz([Section 10 - Changing Degree of Pain],0)
Just include the table name at the beginning of each field in brackets,
followed by an exclamation mar...Check boxes in forms to call a query
I have around 50 queries. These queries are run on a requirement basis.
For example, i have query1,query2,query3,query4,query5
If i select query1,query3 through a check box, the selected query should run.
How can i create a form and call these queries by selecting through check
What kind of queries are they? Select queries or Action queries?
Dave Hargis, Microsoft Access MVP
> I have around 50 queries. These queries are run on a requirement basis.
> For example, i have query1,query2,query3,query4,query5
> If i select quer...query to extract the portion of text of fields in a Table
All: I have a table have several fields that I would like to modify
from its original text content to only retain its first 25 characters.
How to do it in a query such that it could overwrite the fields of
that table? Thanks.
USE an update query and the Left function.
SET FieldA = LEFT([FieldA],25)
, FieldB = Left([FieldB],25)
This is a PERMANENT change.
In the query grid
-- Select the fields you want to modify
-- Select Query: Update from the menu
-- in UPDATE TO under each field you want to change type Left([Name of
-- Select Query: Run from the menu.
WARN...prevent ad-hoc queries?
I think I already know the answer to this question, but is there a way to
prevent users from issuing ad-hoc queries via Query Analyzer, Management
Studio or some other query tool? I want their access to data to come only
from my program. Thanks.
You don't say what type of application you are coding but one possibility
would be to use an Application role, see:
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent...Running a query that totals my Columns
I am trying to run a query that will give me a total of the given fields in a
given time period. For example, my form consists of several check boxes,
they are represented by a 1 if checked and a 0 if unchecked in my table. How
can make my query so for the month of January I can see the sum of how many
times each box was checked? I have the query set to show me the specific
months and subjects for each month but I can't figure out how to get the
total for the month in there. Does that make sense? Should I even be doing
a query for this, or is this a report function I'm look...Querying a Linked table
I am trying to do this:
SELECT * FROM MySybaseLinkedTable WHERE dtDateTimeField >
(SELECT MAX(dtDateTimeField) FROM MyAccessTable)
When I run this query without the subselect and manually put the date
in like #5/26/2007# then the query comes back immediately. When I try
to do the above it crashes Access.
Is what I am trying to do possible? If not, what is the best way to
get a MAX value from an Access table and pass it into the linked table
On 28 May 2007 21:35:36 -0700, Kayda <email@example.com> wrote:
>I am trying to do this:
&g...Query filter by code
I cannot find similar answer...
I am interested for correct syntax to filter query by VBA function.
Query filter: Between [Table1].[ID1]+1 And [Table1].[ID1]+4
When I use function this way:
Query filter: fGetFilter([Table1].[ID1])
Function fGetFilter (intID As Integer)
fGetFilter = "Between " & intID+1 & " And " & intID+4
Function fGetFilter (intID As Integer)
fGetFilter = "Between [t_properties].[group]+ " & 1 & _
" And [t_properties].[group] +" & 4
This does not work. How to properly use...Active Directory Query
I have a list of users in a .csv file. The users are listed via firstName,
Is there anyway I can use dsquery or any other tools to grab the user's
login id after it's compared to the name?
Normally for a single name I would do something like
dsquery user -name "Nik Test" | dsget user -samid
use for loop in a batch script or u can use Power Shell too (that
will give u very good formatting option)
for /F "delims=;" %i in (User_List.txt) do dsquery user -name "%i" |
dsget user -samid >> C:\Temp.tx...Query Expressions
If I have a record that contains a field that is a type-code and another
field a number, is there an expression that will allow me to state if certain
information is present in one field of a record, then change the quantity to
a negative value?
For example, in my inventory transaction table one field of each record is a
transaction type that has a either an "I" or an "O" (in or out) and another
field contains a positive quantity. What expression can I use (in a new
field) that will change quantities to negative for all records that have "O"
in the tran...Date calculation in a Query
I have this expression in a form and it works perfect
But, where should I write it in a query? The query should show all the items
that shoul be removed from the list on the calculated date, and update the
"removed from the list" the field from there.
Add the expression as a calculated field in the query
Field: Removal: DateAdd("d",63-Weekday(FirstContact,7),FirstContact)
Criteria: = #2009-11-27#
Then you can apply criteria against the calculated field.
Another option would be...Queries with loops and variables (query vs. vba)
Again, I am new to access but was wondering about queries versus going to
VBA. I have a sql server script (containing some loops and if statements)
that I would like to run after the user enters information into a form. Does
access queries allow you to do loops with variables, etc or is that when you
need to start using VBA.
Is there someone to find a tutorial on access queries that is more advanced
then the normal select, insert, update or delete.
Any information would be great.
On Tue, 13 Nov 2007 19:24:00 -0800, AJ <AJ@discussions.microsoft.com>
Access quer...m_strFilter version of SQL query?
How is the following done with an m_strFilter in MFC:
(RID is a double or int, '1' is a String)
SELECT * FROM [dbo].[ADB] WHERE CAST(RID AS CHAR)='1' (this works in SQL)
SELECT * FROM [dbo].[ADB] WHERE RID=1 (this works in SQL)
SELECT * FROM [dbo].[ADB] WHERE RID='1' (this works in SQL)
I've tried strings like any of the above but none work in mfc.
"CharlesC" <Charles@bscinforma.com> wrote in message
> How is the following done with an m_strFilter in MFC:
> (RID is ...Displaying Data From Joined Queries
In the Account form, it displays the Primary Contact. Which is really the
Can I display other info from the Contact on the Acount form, like Contact
Is there a way to grab data from other tables that are related/joined (in a
query) in v1.2 or v3.0?
This would not appear to be possible. I have tried to get this in serveral
In a form you could do this using an Iframe and some aspx, but in lists I
can find a way to do this. I would like to see this functionality (out of the
box) pretty much especially in lists.
&g...IF statement in a query....
I am fairly new to using Access and I need some assistance in working with an
Working with Access 2007 I have a table that has a column (Name) with
several clients names in it and I would like to write an expression that
isolates Bank of America and labels all other clients as "Misc".
Current Return Desired
Name (Column Name) Name (Column Name)
BofA ...query or link?? wont update with parent file
I have been using a foxpro query (modified) to pull in a list of part
numbers with associated data. The data is set to update in the satellite
spreadsheet when the business system has new part numbers added.
The problem with this is that it gives us fits with vlookups (sometimes the
imports are text, sometimes numbers- so it is a bit troublesome to
Instead, I thought I would embed a good excel file in this bill of material
worksheet, but now I find it does not update at all- I hit refresh and it
will not refresh.
At first I thought it was having autofilter turned on- but we seem to be
...query relationship text --> Number 18,6 type mismatch
I need to pull a list of posted invoice with prepaid terms (sql below)
using two tables and INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER.
[InvoiceHeaderHistory].Invoice is 18,6 Number
[ARTrans].TRANS_NUMBER is text
How can I overcome the yype mismatch?
SELECT [InvoiceHeaderHistory].[CustID], [InvoiceHeaderHistory].
[CustNm], [InvoiceHeaderHistory].[Terms Desc] AS OPEN_T,
FROM [InvoiceHeaderHistory] INNER JOIN [ARTrans] ON
[InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER
WHERE ((([...Using a form to create dynamic Query and chart
I was hoping someone could give me a steer to whether I should use
Excel or Access...
I have a SQL Server database that has data that I need to query from.
I am trying to build a query form, in excel or Access, that will allow
the user to choose certain criterial. This criteria is the basis for a
select statment and data to create a chart.
I am far more familiar with Excel's object model than Access...the
interesting thing is that Access has an ability to use a query as a
data source whereas excel has to select the data, put on a
spreadhsheet and then graph.
Any thoughts on the best way...Change an update query
I have an update query I need to make changes to but when I open the design
view it onlt shows the field that is updating. Can anyone tell me how view
the other fields?
Are you in query design view (with the grid)? If so, you should see the table
that you are updating and be able to see the fields. If you are basing the
update query on another query, check the base query to see if it has the other
fields in it.
If the upper portion of the query is missing (tables area) then you need to
drag down the section of selecting fields and setting criteria. If that does
not work fo...Problems with Multiple Rows in Query for Report
I have a database that captures two distinct pieces of information
One table (tblPayWeek) captures the timesheets for employees and costs, this
is captured by employeeID and also by ProjectID, the project being the large
job being worked on.
We also have sub jobs to the large job(Project), and the sales information
is captured here(tblSalesDetails) with an index field called SalesID, along
with (if they record it) the hours the men have worked on a sub job. The
costs for labour, materials are stored in seperate tables using the Field
SalesID as the foreign key link back t...Access form tab control query
I have a form in MS Access. This form has 3 subforms -- sf1, sf2, sf3.
Is it possible to have each of these subforms displayed inside the tab
When user clicks on tab1, user will see sf1.
When user clicks on tab2, user will see sf2.
When user clicks on tab3, user will see sf3.
Any help or sample codes will be greatly appreciated....
firstname.lastname@example.org <email@example.com> wrote:
> I have a form in MS Access. This form has 3 subforms -- sf1, sf2, sf3.
> Is it possible to have...Querying data
I have to create a report for the query below
SELECT ClericalMetrics.PS_ID, PerformanceSupervisor.PerformanceSupervisor,
ClericalMetrics.[Time Sheet Errors], Count(ClericalMetrics.[Time Sheet
Errors]) AS [CountOfTime Sheet Errors], ClericalMetrics.TimeSheetErrors2,
Count(ClericalMetrics.TimeSheetErrors2) AS CountOfTimeSheetErrors2,
ClericalMetrics.TimeSheetErrors3, Count(ClericalMetrics.TimeSheetErrors3) AS
Count(ClericalMetrics.TimeSheetErrors4) AS CountOfTimeSheetErrors4
FROM ClericalMetrics LEFT JOIN PerformanceSuper...Using Access query data lookup values
Hi, I have a data base with names and address. The street addresses are
stored in two fields [AddrHouseNum] and [AddrRoadCode] the [AddRoadCode]
field is stored as a three character code, for example Locust Lane is LOC.
When I display the fields separately, they show correctly as i.e. "1234" and
"Locust Lane". When I try to concatenate the the house number and road name
into a query field I get "1234 LOC". My question is how do I get the look-up
field [AddRoadCode], in this case "Locust Lane" to show, rather than the
code "LOC"?...Fonts AA Excel Report from Distribution Query Wizard
The Distribution Query Analyzer generates a font of Arial Regular 8 and I
need to go to an Arial 11 on the Excel report. Can this be done?
...Form Controls unavailable to query field expression
Hi All -
This is a follow-up post to Subject: ‘Pass form parameters to query
expression’ dated 1/3/08.
1. Stand-alone Access 2003 mdb database.
2. Custom dialog form with two textboxes for user input (latitude &
3. The btnOK on the form sets Me.Visible = False, but does not close the form.
4. A stored Select query named based on an underlying table.
5. An expression in the stored query that uses fully qualified references to
the latitude and longitude textbox controls on the form. The expression is: