|
To find duplicate records in a query that is already finding duplicating records
i have a table "X". and it has columns "A" and "B".
"A" and "B" have duplicate records.
so i have created a query "Y" that reduces the duplicate records in column
"A".
now the results that are shown in "Y" have the column "A" with no duplicates
but has "B" having duplicate records.
how should i reduce the duplcuate records in "B" which exist in the query "Y".
when i tried creating...
|
8 |
11/13/2008 3:17:12 AM |
|
|
Help With Query for Two Tables with Different Structures
I am working in Access 2003 (soon to be 2007).
The first query I built sums the scores for six categories. This pulls data
together from several different tables and seems to work fine.
QUERY 1
InspID StoreNo DM Insp Date DMCatID DM Score Sum
171 1 07-Feb-08 1 12
171 1 07-Feb-08 2 13
171 1 07-Feb-08 3 18
171 1 07-Feb-08 4 12
171 1 07-Feb-08 5 5
...
|
4 |
11/13/2008 2:04:01 AM |
|
|
Leading zeros
I have a field in one table in different dB that I would like to use to match
data to another table in another dB. The field in table 1 has leading zeros
while the field on table 2 doesn't. I have insterted a new field/col in my
2nd table and used an update query as follows:
selected the new field (Customer_Nbr) then on the update to:
Right("00000000" & [Cust_Nbr],8) and in the criteriea f...
|
4 |
11/13/2008 1:35:03 AM |
|
|
Date calculation using Iff statement in update query????
Good evening all,
I am currently a novice at access but have high hopes of becomeing much
better. As it stands I have a problem that I need some expertice in. I have
a database built with dates built in that we input manualy using a form and
such. Needless to say I am trying to learn how to do a calculation with in
an update query that will read a code either 5, 6, or 7 in one column fro...
|
7 |
11/13/2008 12:58:24 AM |
|
|
Query Criteria
Hi,
1. Like to find out how do I compare login name with value from table 1,
results search lists in table to
in query criteria ?
2. Eg: Leave Application> Supervisor Login> Display Lists of Names FROM
Personnel_Table (ONLY) Where Dept= '" & LoginID!Dept & "'" & " AND
AccessRights > '" & LoginIDAccessRights'" FROM PendingLeave_Table [PS:
Higher Authority = Lower A...
|
1 |
11/13/2008 12:41:21 AM |
|
|
HELP NEEDED: Running Balance with Data Containing NULL
I need to calculate running balance on data containing Null, which is sorted
by few columns.
As example below, Clr is sorted by Null Desc, then by date, then just by ID.
So far, I just come out with this...
SELECT tr1.Clr, tr1.ID, tr1.Date, tr1.Acc, tr1.Dbt, tr1.Cdt, (SELECT SUM(Nz
(tr2.Dbt) - Nz(tr2.Cdt)) FROM tbl00 AS tr2 WHERE (tr2.Acc = tr1.Acc AND tr2.
Clr <= tr1.Clr)) AS Bal
FROM...
|
3 |
11/12/2008 11:43:33 PM |
|
|
MS ACCESS query
Below is the sample SQL we basically use to compare fields from two tables
and display the result. However when I have to convert this to MS ACCESS
query, I found it difficult as SELECT CASE works differently in MS ACCESS. I
was able to compare just one condition and throw result using below MS ACCESS
syntax.
SQL
Select Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4, Table...
|
6 |
11/12/2008 11:06:59 PM |
|
|
Merging a Column from 1 tbl with a 2nd tbl filtered by a share col
Okay, I need to merge a column from my Locations table with my InvLocs table
sorted by a shared column in both (Locations.Descr & InvLocs.LocName)
Locations table goes as such:
ID1-internal auto number
Location - location #s (ie. 001.01.00001)
Stortype - (ie site, whse, bin)
ID - Bin name, etc
Descr - Bin name merged with a whse name (same as LocName in InvLocs)
Locdesc - formatted (sit...
|
1 |
11/12/2008 9:34:08 PM |
|
|
Format field to Percent w/ 2 decimal places using IIF statement
Hello all,
I'm using the following IIF statement in a query:
Avg OCA: IIf(Sum([total audited])=0,"0.00%",(1-((Sum([total pmnt
errors])+Sum([total non pmnt errors]))/Sum([total audited]))))
The statement works but it doesn't show the result in percentages. It
displays in whole numbers Ex: 1 or .987654345345 And I can't seem to change
the format to display percent with 2 decimal plac...
|
5 |
11/12/2008 9:26:09 PM |
|
|
Text Expression
I am working on a query and I have a field called "Internet Address". The
contents of the "Internet Address" contain a combination of a user id and a
"/" followed by an email address.
Example:
AB123456/MYEMAILADDRESS@ME.COM or ABXY123456/MYEMAILADDRESS@ME.COM
I would like to write an expression to pull the two items apart so in one
column I have just the user id of "AB123456" or "ABX...
|
3 |
11/12/2008 8:54:02 PM |
|
|
Adding two queries
I have two queries. Query "A" has the following information as overtime
Name Duration
Mark 1
Ted 2
Bill 2
Alex 1
Query "B" has the following information as lost time:
Name Duration
Mark 1
Ted 3
John 5
Eric 6
What I would like to do is overtime minus lost time...
|
5 |
11/12/2008 8:44:26 PM |
|
|
Combining 2 crosstab queries into 1 query: Help
Hello all,
I have a couple of crosstab queries (pend & process), and I want to
combine them into one query. The problem is, I haven't had any luck,
so I am in need of assistance.
Breakdown:
pend crosstab query has 14 records.
process crosstab query has 15 records.
The reason why pend query have 14 records is because one of the user
did not pend any; however, the user did process some...
|
2 |
11/12/2008 8:18:15 PM |
|
|
problem with query
I try to do connection beetwin excel and query in my acess databases.
Everything is ok but wizard does not shown all of my querys in list and it is
ussually one I need.
What is happening?
--
everything glitters is gold...
|
2 |
11/12/2008 7:49:21 PM |
|
|
Append? Update? Start over?
I have a table with a tag number field, remaining field, completed field, and
area field.
TAG COMP REMAIN AREA
19 200 6
15 28 8
19 6 6
55 20 12
How would I get the table to look like this:
TAG COMP REMAIN AREA
19 200 ...
|
3 |
11/12/2008 7:24:16 PM |
|
|
Print Pivot Table
I have a query that displays in a pivot table. I have inserted that pivot
table into a form. In that pivot table, they are able to change a few
parameters so the data can fit the users needs.
Is there a way to create a print option so that it prints out the pivotable
exaclty the way the user has adjusted the parameters?
Thanks,
Jasper
...
|
1 |
11/12/2008 6:47:06 PM |
|
|
Nesting IIf Statements in Microsoft Access 2000
I need to specify an IIf statement to look at another field and depending on
what that field contains I need it to peovide a number associated with the
contents of the associated field.
The following IIf statement is what I am trying to use. However, there is
OBVIOUSLY an error in it because I cannot save the table when I enter this as
the default value for the LineCallPriorityFee fiel...
|
5 |
11/12/2008 6:17:02 PM |
|
|
Want to build a query to return quarterly data
Table with three columns - Order number, Created, Shipped. Order Number is
repeated because an order may have several line items.
I want to create a query that shows the number of orders that did NOT ship
in the quarter it was created. Please help - I am a light user of access but
I need this for a report this afternoon. Thanks a lot....
|
3 |
11/12/2008 5:51:38 PM |
|
|
Append? Update? Start over?
...
|
1 |
11/12/2008 5:37:04 PM |
|
|
Append to excel worksheet
Is there a means to append access records to an excel worksheet? I've seen
work arounds to where people suggest importing the destination excel file
into a temp table in access, append the necessary detail, then turn around
and re-export that file. I would like to avoid that if necessary.
Thanks
--
Chris Unger
Analyst - Tyler, TX...
|
2 |
11/12/2008 5:07:01 PM |
|
|
??????
zugeerspam@yahoo.com
...
|
1 |
11/12/2008 4:17:02 PM |
|
|
DateDiff Select Statement
I have the below select statement to basically pull all records that have
data for a full year. The below statement works.
SELECT Member.[Store#], Member.StoreName, Member.TermDate,
SIVolume.VolumeDate, (DateDiff("yyyy",VolumeDate,TermDate)) AS YearDiff
FROM Member
INNER JOIN SIVolume ON Member.[Store#]=SIVolume.StoreNumber
WHERE ((((DateDiff("yyyy",VolumeDate,TermDate))) Is Null...
|
3 |
11/12/2008 3:34:09 PM |
|
|
ODBC timeout
Hi,
I have written a UNION query and would now like to set the ODBC timeout
interval for this query to zero. How do I do that? Also, is there any way by
which I may set the global ODBC timeout parameter that acts as the default
for all queries that get created in the future?
Thanks and regards,
Duncan.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums....
|
3 |
11/12/2008 1:43:24 PM |
|
|
Subquery doesn't return GUIDs
BACKGROUND:
I'm using microsoft access 2003 Version 11.8204.8221 SP3
I have a database that contains a table "Parts" and a table "Tests"
Parts contains the following columns: Name (text) and PartId (Replication Id)
Tests contains the following columns: PartId (ReplicationId) TestId
(ReplicationId )TestTime (Date/Time) and Data (double)
Tests has a many to one relationship with Parts on...
|
3 |
11/12/2008 1:31:00 PM |
|
|
IIF function
I have a checkbox field name Include:Sum(IIF([Include]=0,0,1)) and in have
this expression in my QBE
i want to count the number of TRUE value or the Yes value in my checkbox.
in my queries i've checked the data it is correct or it counts will the true
value
but when I pass it to the report it doesnt give me the correct count.
for example I have 12,330 records so when i uncheck 5 records i...
|
3 |
11/12/2008 12:59:01 PM |
|
|
parsing a field for a join
Hi, this seems like it should be easy but I have a newborn and no
sleep :)
I have a form with a listbox where a user can select multiple
questions. The questions come from a table called
[CompetenciesAndQuestions] which has an autonumber primary key for
each question. When a user saves their question selection on the form,
it saves to another table in one field. It formats like:
6,8,10,12...
|
8 |
11/12/2008 10:21:52 AM |