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
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????
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 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.
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????
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];
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
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.
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?
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....
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.
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 ??
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?
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
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").
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?
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?
Hi, I have a querie that returns various data based on the page of a part's Sub-Assembly. There are 4 basic items returned for each page that display in my subform it displays as follows:
SpecID - Page # - Part# - Description - etc.
4 basic SubAssembly Descriptions are
1 - A Text Description of Assembly as a whole (varies for each record and usually has a number such as 2" or 3" or 4" etc at beginning of field) 2 - Valve 3 - Selenoid 4 - Actuator
I want to sort these records so that the first field is that Text description field. I tried sort descending and this does sort descending but puts text field last. (i Know that this is because it is sorting on text first and then intergers. But I don't know how to program multiple OrderBy for this column. How can I sort by intergers first and then by text on this field? :o
hello, I have tables which have relationships in them. for example, ID appears within 3 different tables for the same item. How can I set up a query to delete all records with that ID from all of the tables in one go? Thank you, JDRasq
I apologies for this sinmple quesiotn to some of oyu, but being fairly new with Access, Im having problems when I update my form and then print our a querie, to obtain certain information, the new data is missing.
HOw can I make sure that my querie gets updated when I update my form.
