MS ACCESS :: Advanced Querie Problem

Help With A Querie
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 Replies !

Between Querie
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 Replies !

How I Can Run The Querie With VB???
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 Replies !

My Querie Keeps Duplicating
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 !

Using A Querie As A Filter
First let me say that I have been reading these forums alot recently. I knew nothing when I came here and you guys have been a great help. Now heres my problem.

I have a table for amusement businesses. I have colomns for inspector, insurance exp. date, and year they have applied for licences. I've mannaged to to sort out the ones that have expired insurance and expired licences. Now I need, above all else, sort it by inspector, so each inspector can see wich one of his assighnments needs to be checked up on.

Heres my code
SELECT Yearly.[Co licence #], Yearly.[operator name], Yearly.inspector, Yearly.[inurance exp date], Yearly.[2005], Yearly.[2004], Yearly.[2003], Yearly.[2002], Yearly.[2001]
FROM Yearly
WHERE (((Yearly.inspector)="DP") AND ((Yearly.[comments 6/2/2005])="follow up")) OR (((Yearly.[inurance exp date]) Between #1/1/2004# And #6/2/2005#));
I can tell already that this is a bit embarising, but like I said I learned everything I know from here.

thanks in advance.

View Replies !

What Is Wrong In This Querie?
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 Replies !

Another Date Querie
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 Replies !

2 Table Querie
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 Replies !

Printing Querie
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 Replies !

Multi Querie
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 !

Querie Criteria
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 Replies !

Querie Help Needed
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 Replies !

Percentile In The Querie
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 Replies !

Creating A Querie
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 Replies !

Date Querie
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 Replies !

Join Querie Help For A Newbie
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 Replies !

Querie Then Count The Results...
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 Replies !

Is This A Querie Or Sorting Problem?
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 Replies !

Querie For Last Entry Date
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 Replies !

Excel To Access Querie - Help!
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 Replies !

Selecting Parts Of A Field For Querie
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 Replies !

Querie Order By Num Then Text In 1 Column?
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 Replies !

Update/amend Querie Woes!
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 Replies !

Querie Involving Related Tables
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

View Replies !

Entering Data In Form And Updating A Querie
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.

Thanks you

View Replies !

Make A Querie That Searches For Other Queries That Contain A Certian Word
ok, i wanna make a query that will display the information from other queries containing the word "complete" in their title. All of these queries have the same columns names to them so they should fit together nicely but if someone adds a new query in the future with the word "complete" in the title i want this query to pick it up. Is this possible?

View Replies !

Advanced Customization
This should be a piece of cake for someone with a few experience years on me. I have a client for which I am building a custom database. They use this for various reasons. One main reason is to track man hours, cost, billing information on a per job/customer basis.

Each of their customers has a unique contract. Which specifies the information required in order to bill the customer for a job. For example: Customer "Big Factory" requires PO# and Job#.

The contract also specifies the billing rate for each piece of equipment and each type of employee. For example: For customer "Big Factory" again, we charge $10 a day for a pickup truck and $20 an hour for a welder. Customer "Little Factory" has a different contract with different rates and required fields.

*Now I have all of the above under control (in my opinion)*

Following is part is where I struggle. Each contract also specifies which hours (on a daily basis) are standard work hours and which are premium work hours, as well as, at what point (# of weekly hours) to start billing only premium hours during the remaining part of the week. For example: "Big Factory" Standard hours are from 07:00 to 17:00, Premium are from 17:00 to 07:00, Weekly premium start once an employee has worked 40 hours for that week regardless if the employee has worked some hours for another client(*Exceptions do exist where the employee must work 40 hours for the client before the weekly premium kicks in*).

I need this to automatically calculate for each employee. It must update each time sheet that the employee exists on.

Currently I have it working in a rather unorthodox way, but it isn't exactly scalable to work for each customer's unique contract. I can post how I have it, but it will be quite a long post. It has to do with a running sum query then a make table query then an update query.

One other caveat: My client pays their employees premium pay on a 40 hour work week (>40 per week is premium time) regardless of which hours they work. (Seems like they are ripping the employees off I know). I posted this once before, but had received only a negative post chewing me out for not being fair. Anyway...

If presented with this how would you do it?

Any help is greatly appreciated.
Thanks,
Eric

View Replies !

Advanced Search Box
I want to make something similar to the Google search box, although not quite as complicated.

I would like to be able to type more than one word in a search box.
I am only searching on 1 field [Title]. The query would show all the records with the first search word in the Title, plus all the records with the second search word in the Title.

I have a query now that has the below in the Title field criteria:
Like "*" & [forms]![Searchform]![searchcontrol] & "*"

This searches for the word typed into the searchcontrol no matter where the word is in the title. It works great, but I can only type in one word at a time.

View Replies !

Advanced Query Help
The below Stored Procedure was refined to incorporate a UDF. My problem now is that I need it to be even more dynamic. I ideally need the user to have the option to select which fields are incorporated into the query. I have the front end framework although not sure currently how to make field selection dynamic...

CREATE PROCEDURE dbo.sp_ClientQuery

(
@inputRegion varchar(500),
@inputPub varchar(500),
@inputCountry varchar(500)
)

AS

SELECT dbo.tblClient.Country, dbo.tblClient.cName, dbo.tblClient.clientID, dbo.tblClient.Wholename, dbo.tblClient.TelNumberG, dbo.tblClient.TelNumberD, dbo.tblClient.City, dbo.tblPublication.pName
FROM dbo.tblOrders INNER JOIN
dbo.tblClient ON dbo.tblOrders.clientID = dbo.tblClient.clientID INNER JOIN
dbo.tblPublication ON dbo.tblOrders.id = dbo.tblPublication.id
WHERE (dbo.tblClient.Region IN (select value from fnSplit(@inputRegion,',') )) AND
(dbo.tblPublication.pName IN (select value from fnSplit(@inputPub,',') )) AND
(dbo.tblClient.Country IN (select value from fnSplit(@inputCountry,',') ))
GO

This is becoming pretty complicated so any help appreciated.

Phil

View Replies !

Advanced Sql Query
Hi Guys

I am having a bit of a delimma and am wondering if there is someone out there that could suggest how i could write a SQL statement that would alow me to return data in a certain way,

I have data as follows...

FIrst problemi is the Date in the LogDate COlumn is of Text Type not Date..

Second problem is i need to take this data and transform it to look like the table below

RCDIDEmployeeIDLogDateLogTimeTerminalIDInOut
411 07/23/200620:45:02iGuard# IN
421 07/23/200620:46:17iGuard# OUT
431 07/23/200620:48:08iGuard# IN
441 07/23/200620:48:18iGuard# OUT
451 07/23/200620:48:24iGuard# IN
461 07/23/200620:48:30iGuard# OUT
471 07/23/200620:48:36iGuard# IN
481 07/23/200620:48:41iGuard# OUT
501 07/23/200620:49:57iGuard# IN
511 07/23/200620:50:14iGuard# OUT
521 07/23/200620:59:34iGuard# IN
531 07/23/200620:59:40iGuard# OUT

Employee IDDateInOutIn OutTotalTimeIn
123/07/200620:3520:3620:3820:3900:02

Basicaly i need to transpose it..

If anyone has the sql or knows the sql on how to do this i would be very greatful

View Replies !

Help With Advanced Problem
Hi there - I'm trying to produce an application in Access to populate a CD information data base. I have signed up for (and downloaded) the GraceNote SDK (they're the folks who operate CDDB these days) and it seems to work - at least early tests show that the VBScript sample works to a point with their ActiveX component.

My problem is that of retrieving the TOC from the cd. Now, I'm not a professional developer though I have written code before (C for an OS/2 application - no, don't laugh) and some stuff in REXX. However, I am totally baffled by this one.

Their SDK indicates that I need to call their function and pointing it at the CD device by using the MCIDeviceID. Now, try as I might, I can find no reference which is helping me to identify what this is and how it is formatted. As I understand it, I should be able to do this from within a procedure behind a control on a form - as I say the other bits seem to work ok.

Can anyone help me please?? It's probably a very simple thing if you know it - unfortunately, I don't :(

Thanks in advance, regards, Chris B

View Replies !

Advanced Sort/filter
I am using a handheld scanner to scan in barcodes. However the part numbers contain more data than I need. Is there a way to get ride of the extra data I scan in in Access.

Say I have part ABCD- 7G675 .. I only need 7g675 to be in the field. Is there a way to do this besides a find replace, that would take way to long.

View Replies !

Advanced QBF And Importing Into Access
Hi everybody! :)

After a week of hair pulling problems with no solution, I've decided to ask my question here, knowing someone will be able to help me...

To the point:
I'm to use a QBF with more then one criteria,
(I'm using: like "*" & forms![f_name]![TB_first_name] & "*" or is null),
I'm having a problem with this because when I leave a text box blank, it retrieves everything (because is null = true), what I'm trying to say is: I have a QBF when using, lets say, 3 text boxes, unless I fill all of the text boxes with text, the query retrieves all of the records, can it be fixed ?
I've tried to use IIF but with no success... can anyone help ? :confused:

and another problem: while trying to import data which is not in English, from a Visual FoxPro data base to access, it turns out unreadable, what can I do to transform ASCII to ANSI or Unicode? :confused:

p.s. - Sorry for my English

TIA

WildCat

View Replies !

Advanced Employee Schedule
Hello Everyone
I need to make an employee schedule in access. I am not sure if it is possible, but here are my constraints:

20 employees

5-6 different truck locations (sm1, sm2, sm3, sm4, sm5, sm6) These locations are scattered within our service area.

2 employees must meet at the same truck, (each truck has 2 employees working on it)

some employees can only meet at certain locations (ex. paul can meet at either sm2 or sm3)

all employees are PT so they can only work on certain days or a certain amount of days per week. (ex. paul can work up to 4 days a week and jim can work only sat and fri)

I would like to make a program in access that will take all these factors into consideration. EX. if i try to schedule paul on sm4 the program will not let me do it or it will give a warning or if i try to schedule paul for 5 days it will give me a warning.
It would also be great to have a counter that will show how many days each employee has left to be scheduled for, EX: if i scheduled paul for 2 days it will show that he can actually work 2 days more if i need him to.

I know this might be a lot, but i also know there are so many talented people here so i am sure it is feasible. Please let me know if you know how to do this or if you could guide me through it.

or if you know about a different program that can solve this please let me know
Thanks in advance

View Replies !

Advanced Search More Than 1 Table
Hi,

I'm a newbie (I know nothing in VBA & SQL but I'm learning) and I've a database of 10 tables. I want to make a query & form to search through these tables and display results(in a form & report) according to the search criteria.

example of my request is attached..

I can PM you my real database if you need it.

Help is appreciated

View Replies !

Advanced Searching Through Forms
ok, i have just had a lengthly conversation with my user and i need an advanced search system.

what i would need is a search function, that does not only list the full field details from each record in the combos..

here is what i mean.. i have a quotation form with different locations and prices in a list box. i have a text box at the top of the form with an onchange event that alters the results in the list box.

so if the user types 'a'.. only the axxxxx records are displayed.. if they type 'ab' only the 'abxxxx records are shown.. and so on.

because my customer may not remember the exact address of their pickup or destination, i cant use this system, because i wont know the first letter of the details previously entered..

eg.. a user may have been picked up from '5 house street, new hampshire crescent.

if all the customer remembers is 'hampshire crescent.. then the above system will not reveal my record..

therefore i would need a search box at the top that allows me to search for any word (or identical consecutive characters) within a chosen field.

can this advanced string search be done in access?

View Replies !

Advanced Search Form
Dear All,

I need to create an advanced search form. I need to search for.

Customer
Or And
Ref Number
Or And
Product
Or And
By date range

If someone could help me I would be most grateful.

View Replies !

Advanced Import Question
My company just sent all of our contacts to a data company for them to update and "scrub" them with what they have. We sent out 5000 contacts (company, contacts, address, etc) and they sent back a list 0f 2000 that they matched in their database and gave us the "updated" version.

My question is...what is the best way to upload them back into the database replacing the original versions without losing the other data the Scrubbing company didn't have?

thanks a lot

View Replies !

Advanced Form/Report
Please help me if you can. We are in a state of panic right now in trying to figure this out and any help would be appreciated.

Basically I have tried many different angles and have not come up with a solution that works. I have spent 80+ hours trying to figure this out with no luck.

Here is what we need to do: We have a database that has data that needs to be printed on a 3"x6" sized tag. Now there are probably 5 or so different tags that need to be printed. What we need to happen is be able to provide a way to print out all the tags at the same time. The problem is I cannot seem to create one report that will generate all the tags correctly (I could create the code to do this in a form, but I can't get it to work in a report), and I don't know how I would combine multiple reports so that all the tags can be printed at once.

Do you have any ideas on how I can do something like this?

View Replies !