My Querie Keeps Duplicating
Nov 25, 2005
Hi
Please can some help me asap. I need to do a querie which joins two tables together. I have a telemarketing table and an appointment table. The idea is that when an appointment is made telemarketing should be done afterwards to the same company. Therefore I want to view all of the telemarketing done and all of the appointments done and then match companies which have had both an appointment and telemarketing. However when i do this if there has been more appointments made for one company that there has been telemarketing the telemarketing duplicates itself so that the two have the same amount done which is not what I want??? Does this make sense?? Can some one please help????
Thanks
View Replies
ADVERTISEMENT
Nov 25, 2005
Hi
Please can some help me asap. I need to do a querie which joins two tables together. I have a telemarketing table and an appointment table. The idea is that when an appointment is made telemarketing should be done afterwards to the same company. Therefore I want to view all of the telemarketing done and all of the appointments done and then match companies which have had both an appointment and telemarketing. However when i do this if there has been more appointments made for one company that there has been telemarketing the telemarketing duplicates itself so that the two have the same amount done which is not what I want??? Does this make sense?? Can some one please help????
Thanks
View 10 Replies
View Related
Sep 12, 2005
i have 2 tables jobs and jobs_details jobs have the fields name date account etc.
jobs_details have the print area, file, etc.
i need a querie to give me the total print area for each account on a particular date but i can't get it to work.
I've tryed this:
"SELECT DISTINCT jobs.Account, Sum(jobs_details.PlottedArea) AS SomaDePlottedArea, Sum(jobs_details.UsedArea) AS SomaDeUsedArea, jobs.JobDate
FROM jobs LEFT JOIN jobs_details ON jobs.JobID = jobs_details.JobID
GROUP BY jobs.Account, jobs.JobDate
HAVING (((jobs.JobDate) Between #3/1/2005# And #4/1/2005#))
ORDER BY jobs.Account;"
but it returns all the jobs.Account(it doesn't give the distinct) by all the dates
what am i doing wrong???
View 4 Replies
View Related
Jul 13, 2007
Hi there
I've got the following query in my application:
SQL = "INSERT INTO tblreport3 ( exchange, NoCustomers2, [time] )"
SQL = SQL & "SELECT Table1.Exchange1, Table1.Customers1, Table1.Duration"
SQL = SQL & "FROM Table1"
SQL = SQL & "WHERE (Table1.IncStart Between #txtdatefrom# And #txtdateto#);"
DoCmd.RunSQL (SQL)
The problemis when I run the application it gives me a error 3075 saying missing operator in query expression.
Can someone please tell me what is missing here?
Thansk
View 1 Replies
View Related
Feb 18, 2005
Hi
How i cun Run Queries with VB
My Querie : Delete Records in table1, It Query in access no string of VB
I want when i delete one record in table2 run this Querie
I try with this command DoCmd.OpenQuerie DelNoExistsRec.
but i hav one message : The action or method requires a Query name argement????
View 5 Replies
View Related
Jul 2, 2005
Hi everyone,
Now, I'm totally lost!
When I create a query with the Query Design view in Access with this SQL query:
SELECT T_Cryzout_Disponibles.LCLCL_Disponibles_Total, T_Cryzout_Disponibles.DateEmission, T_Cryzout_Disponibles.NomReceveur, T_Cryzout_Disponibles.Ordre
FROM T_Cryzout_Disponibles
WHERE (((T_Cryzout_Disponibles.LCLCL_Disponibles_Total) Like 'C*') AND ((T_Cryzout_Disponibles.DateEmission) Is Null) AND ((T_Cryzout_Disponibles.NomReceveur) Is Null Or (T_Cryzout_Disponibles.NomReceveur)=''))
ORDER BY T_Cryzout_Disponibles.Ordre;
everything is good, I get what I'm suppose to get. But When I'm doing it in VBA with this code:
RExtraction.Open "SELECT TOP " & NbrLCLCL & " LCLCL_Disponibles_Total FROM " & QuelleTable & " WHERE (((LCLCL_Disponibles_Total) like 'C*') AND ((DateEmission) Is Null) AND ((NomReceveur) Is Null)) ORDER BY Ordre", ConnectionBD, adOpenStatic, adLockOptimistic
RExtraction.MoveFirst
It gives me an error that says:
BOF or EOF is egal to True.
And I really don't know what is wrong with this. It is pretty much the same as the query I entered in the Query Design.
I'm really confuse.
Thank you in advance for your help!!!
View 1 Replies
View Related
Aug 27, 2005
I'm trying to construct a query that will show all fields where the date is greater than today’s date less eight days, and also pick up fields which contain no data.
The first part I have achieved with the criteria >Now()-8
But I've been unable to find the correct syntax for picking up fields which contain no data.
View 2 Replies
View Related
Nov 25, 2005
Hi Guys
I have two tables appointments and telemarketing. I want to be able to view all companies which have had appointments AND telemarking. However when i have previously tried to do this if more appointments have been made to say the company called *lucy* than there has telemarketing the telemarketing will duplicate itself?? Does anyone know how to stop this from occurring.
E.g
Company Tele date, month, appt date, month
Lucy 12 nov 14 nov
Lucy 12 nov 15 nov
So here the telemarketing has only been done once on the 12th but the query is saying that 2 calls have been made on the 12th??!!
This is the SQL statement but I am a newbie to access and don't have a clue how to resolve this. Can anyone help please?
SELECT [Appointment Commentary].Day, [Appointment Commentary].Date, [Appointment Commentary].Month, Telemarketing.[Company Name], Telemarketing.Day, Telemarketing.Date, Telemarketing.Month
FROM [Appointment Commentary] LEFT JOIN Telemarketing ON [Appointment Commentary].[Company Name] = Telemarketing.[Company Name];
Thanks
View 1 Replies
View Related
Jul 20, 2006
How Come When I Change The Page Setup To Landscape In A Querrie..
Then Hit File Save...when I Close And Reopen This Querrie And Go To Print Preview It Is Still In Portrait Mode
View 2 Replies
View Related
Nov 7, 2006
Good Afternoon,
I need to be to create a Query that will show a 0 for no record.
My form is made up of a Name and a Course name.
e.g.:
Michael Underwriting
Scott Underwriting
Michael Claims
Michael IT
My End result needs to be
Name Underwriting Claims IT
Michael YES YES YES
Scott Yes 0 0
I have created 10 individual Queries filtering out each course
but then I join them together by Name it will only show Michael.
I hope its not a case of adding records for scott as I have 10,000 Names
Thank you
View 3 Replies
View Related
Apr 27, 2007
I havent used access very much but I am wondering if there is a way to write a criteria that would take info from a field if it wasnt eqaul to another field? I tried a couple time but it doesnt querie anything. If the data in the cells are the same I would like the data to not populate in the single cell! if c3 = a1 set c3 to null or blank, something like this. Any help would be greatly appreciated, thanks Neal.
View 5 Replies
View Related
Sep 4, 2007
I need to build a Querie that will filter about 50 fields for "Trainer", but all i need to know is if I have more than 2 for each field. I am drawing a blank. Any Ideas on how to do this?
Thanks
View 9 Replies
View Related
Oct 29, 2007
Hi
I found the code to calculate percentile value and It works fine,
my problem is that code is calculating value for the whole data set, not for the filtered data.
PercentileRst («RstName»; «fldName»; «PercentileValue»)
is it possible to put some function that can filter or sort data like it do access queries for the mean calculation
for example:
dp1 100
dp1 90
...
dp2 89
dp2 78
...
percentile value for dp1, dp2....
thx in advance
View 4 Replies
View Related
Aug 19, 2007
I'm currently trying to create a database for a estate agent, its a little project just to help get used to access. criteria i need to fullfill is the estate agent wants to record each time a buyer goes to visit a property, also the time date and what estate agent showed the buyer needs to be recorded.
so from that i figured that 3 entities would be required - Buyer, Properties and Agent. ive create each of them and put in what i think are relevent fields for each one. but im just not sure how to go about actually creating this requirement, i had a go with Queries but couldnt get them to work. any help would be great.
thanks
View 1 Replies
View Related
Aug 26, 2007
Hi,
I have a table that contains customer orders (customer name, order date, items etc.), and i need to build a querie that lists all the orders from this week (so lets say Aug 12 -18) of each prior year. Does anyone know how i would do this ??
thanks
Matt Cameron
View 1 Replies
View Related
Aug 3, 2005
I just got done creating a join querie (two tables one relationship to make one querie) and now I can't edit the fields. is this normal? if its normal is there any way I can edit the fields?
View 14 Replies
View Related
Apr 5, 2006
Hello gurus
I have a d/base (Access 2000) and within this I need to filter out some records
insurnace related
so I have a location code of ten venues coded 2-11 in a table
and I need to extract out informaiton on all of these in one table
I date a date of event and a sum insured , what I need is date difference between date to be enter on a form (default Now()) and the date of the event - this is the easy bit
Now i need to split these out in batches of 30 days into columns
ie 1-30 days
31-60 days
61-90
etc until >240 days
the sums insured need to be in the right date column
example event 1 sum insured @ 25,000 is 45 days away from now so it should appear in the 31-60 column and if there more than 2 events at that venue one being 45 days and the other being 55 and with sums insured @ 25,000 and 35,000 I need them to either be on a line each or combined to give a total of 60,000 in the 31-60 date column
There could be up to 20,000 - to 100,000 of which the code will extract the venue codes of 2-11 and these could be high hundreds records to check and this will be a regularly run question
any thoughts on this would be welcome - each appraoch I take seems to weird and getting nowhere -
i thought about putting a date difference field on my table but it doesn't seem liek the right thing to do as I would have to update this consantly or rather remember to run this query before I run my report - and this just seems wrong
any thoughts
View 2 Replies
View Related
Apr 13, 2006
I have two tables: "Vender" and "Jobs". I want a field (named "2006") in my vender-profile form to display the number of times in 2006 that that vender's name appears in the "Vender" field of my Jobs table. I hope to repeat the code for other fields ("2005", "2004" & "2003").
Any suggestions?
--Brando
View 1 Replies
View Related
Jul 11, 2006
Ok, new to this relm. First off I'm using Access 2002 working in a retooling company, writing a tool tracking DB. What I have is :
Liner ID # Inspected Date Dia 1 Dia 2 Dia 3 Dia 4 OD 1 ID 1
I have only 15 liners now but will add more in future, that we use and inspected and enter measuring data. I have tolerances in place, when the liner gets out of tolerance it's flagged and replaced. So when I put in measurments I have multi-number of liners in my records.
Question:
Is there a way to do a query to only show only the lastest or most currert by [inspected date], liners with all the measurements?
If there is a way.... Can there be a drop down box for the other dates bringing the other data with it?
Any help with my quest for knowledge
View 14 Replies
View Related
Feb 8, 2007
I manage an Access 97 database that tracks production entries per requisition by date stamp. I need to create a query that will show me the latest entry. I created a query that would sort the requisition number by ascending order and sort the entry date by ascending order. Then on the total field I entered "last".
The problem I am having is the consistency of the data. Some of the results are the last entry in the production field. Others are not. I cannot find anything that would cause this discrepancy.
Am I doing it right? Is there a better way? Any idea why this is happening?
Thanks for any help you can offer
View 2 Replies
View Related
Apr 22, 2007
Hi,
Below is my data as in Excel which I have to put into Access as shown:
CDate PlotPalm Pos2 CDate Pos2 FP1
19-Aug-06 11 19 04-Jan-07 12 11.2
FP1 = (04-Jan-07 minus 19-Aug-06)/Pos2
In access
CDATE Plot Palm Pos2 FP1
09-Dec-05 1 1 23
19-Aug-06 1 1 19 12.2
04-Jan-07 1 1 12 11.2
FP1 calculated using current date - previous date / Pos2
Please assist - is this better in a query or should I code it?
Thanks,
View 2 Replies
View Related
Feb 10, 2008
Hi
I'm hoping this is quite easy as I'm new to Access.
I have a record similar to the below
recordID: 1
animals: cats
breed: persian
number: 3
price: £50 each
What I would like to do is dupicate the above data three times, changing the field in "number" to show as 1 on each occassion...so, theoretically, the data will be exactly the same in each new record except for recordID....
e.g
recordID: 2
animals: cats
breed: persian
number: 1
price: £50 each
recordID: 3
animals: cats
breed: persian
number: 1
price: £50 each
recordID: 4
animals: cats
breed: persian
number: 1
price: £50 each
Can anyone tell me how to do this?
Many thanks, Tim
View 1 Replies
View Related
Jul 4, 2007
Hi There,
I am trying to copy a table and put the copy in a folder called "My Data Sources". The reason being when I set up a mail merge letter it defaults to "My Data Sorces" for the recipient data. I also need the second table to be updated every time we enter data in the original table.Has anyone got any ideas please????
Best Regards
Keith
View 1 Replies
View Related
Nov 5, 2007
I have a query where I want to type in the part number and the begin and end dates and have that information appear.
In part number I have
[form]![frmTest]![Test_Part]
and in Date I have
Between [forms]![frmTest]![Test_Date].[startdate] And [forms]![frmTest]![Test_Date].[enddate]
It is duplicating my records and not pulling all of them, any help here what be great.
View 14 Replies
View Related
Jan 10, 2008
Hi Guys
Not sure what I should do here. I would like to have a cell populate with the value of a specific cell previously.
explain:
in excel, we can use formulas like this:
A1: 00:00 B1: 08:30
A2: 08:30 B2: 13:00
A3: 13:00 B3: 15:00
etc, etc
Notice that each cell in A is the same as the previous B Cell. So the formula for Cell A2 would be something like: =IF(B1>0,B1,"00:00")
and Cell B3 would be =IF(B2>0,B2,"00:00")
The fields I have in Access are:
TimeIn TimeOut
So I would need to get TimeIn to be the same as the previous timeout???
Not sure If I'm making any sense right now :)
View 5 Replies
View Related
Sep 21, 2005
Hello,
I know it's against any database logic but I need a code behind a a button to copy data from one field to another.
What I am trying to do is:
I have a table with two fields:
Father Last Name (txtFLN)
Child Last name (txtSLN)
When writing my data to the table (I use a form named MASTER), I type the father's last name and would like a button that copies that same entry in the other field?
Any help?
View 1 Replies
View Related