Multi Querie
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 Replies
ADVERTISEMENT
Sep 7, 2007
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain
[IntakeMainID]
tblIncidentDetails
[IncidentdeatailsID]
tblPersonnel
[PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
View 5 Replies
View Related
Apr 10, 2008
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
View 7 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
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 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
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
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
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
Aug 8, 2006
I have one field with complete url´s.
Example
http://www.rocketspanish.com/links_spanish_immersion.php
or
http://apps5.oingo.com/apps/domainpark/results.cgi?q=naco&cid=ca-dp-260com_nopop&url=http%3A%2F%2Fnaco.com%2F&hl=en&ac=s
I like to make a groupby but only from the left side up to ".com"
Is there a way to select only the text untill ".com"
Thanks
Wouter
View 2 Replies
View Related
Nov 20, 2006
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
Thanks for any help!
View 7 Replies
View Related
Mar 23, 2007
Hello,
I have a table exported from excel. I intially imported this in to access to form a list of due dates for services.
Every week I get and e mail with the updated version.
I am trying to figure out how to update the dates in the 1st table with the new weekly ones.
I can't get it to update. Also there may be the addition of new services as well.
Any tips would be grealty recieved.
Many thanks in advance.
View 2 Replies
View Related
