Help Needed With This Simple Query

Sep 12, 2005

I'm missing a bit of experience dealing with this query. It's very simple. I'm selecting log activities from COMLOG. However I only want the most recent DateStamp entry of each Cust_FK entry listed.

Because there are more logactivities, there are more/identical Cust_FK values. Would I first have to group these?? I just don't know how I would go about it.

SELECT COMLOG.LogID, COMLOG.DateStamp, COMLOG.Cust_FK, COMLOG.LogActivity
FROM COMLOG;

thanks

View Replies


ADVERTISEMENT

Simple Query Needed

Aug 22, 2006

Hi,

I have data that I imported into access (Table1). Basically, the file shows two values with Header, Component. I need to group the Component together with a delimiter & (or any symbol) when the header is the same.

For example of what I have:
Table1
Field 1 – Header
Field 2 - Component

FG1 | SA1
FG1 | SA2
FG1 | CO1
FG2 | SA3
FG2 | CO2

What I am trying to get is
Table2
Field 1 – Header
Field 2 - Join Component

FG1 | SA1&SA2&CO1
FG2 | SA3&CO2

Thanks
Andrew

View 2 Replies View Related

Simple SQL Help Needed

Feb 22, 2006

Hi all !

Im having an annoying debate with my brother who knows very little about SQL. Anyhow what would you guys say the SQL query for the following would be:

'What is the first and last name of customer 405'

The table is called 'CUSTOMER' and the respective columns are 'CUST_FIRST', 'CUST_LAST' and 'CUST_NUMB'.


Thanks in advance !

Phil

View 2 Replies View Related

Simple Solution Needed Please

Jan 1, 2007

Hi the specialists.

I do not want security on any of the files in my computer.

I'm placing this in "general" because I'd like a direct and practical answer, please. And if there is none, please say so. Computers are immensely complicated and I am just a little bit tired of people superciliously refering the uninitiated to "Microsoft's FAQ's" That source is to most normal humans as obscure as the software programming itself.

I have also searched this source and Google for hours, without getting an intelligible answer.

This is my problem:

I am the only user and administrator on my computer.
I have a back-end file which is easily accessed through the front-end.
.... but
I cannot open the back-end to access the tables directly.
I get the following error message:

You do not have the necessary permissions to use the 'C:Documents and SettingsAll UsersDocumentsAccessThingsWORKLOG_be.mdb' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.

Of course the person who created this back-end is me, but I have no clue what I did, because it is at least two years since I created it.

Could someone please help?
I have tried to use the "shift", click method. It does nothing - just gives me the same message.

Pierre.

View 4 Replies View Related

Help Needed With Query

Nov 19, 2006

Hi,

I was wondering if i could get some help here. I got problem while trying to perform sql query. I got an error message saying

"No value given for one or more required parameters".

I think i know what caused this but i just don't know how to get around this.

The query I'm trying to perform requires 3 tables and 2 queries.
Tables: Projects, Plan_Names, Reason
Queries: PSQuery, ProjectQuery

I think this is due to the fact that those two queries are empty for a start. Hence, it needs more value.

My question is how do i in the code, tell it to execute the two queries before attempting the query i want to perform.

Thank you in advance

View 7 Replies View Related

Query Help Needed

Aug 28, 2005

I have a query where I need to find out the average score of employees over a date range. I have a field called date where I enter a date range (Between [START DATE (MM/DD/YY):] And [END DATE (MM/DD/YY):]) and another field where I enter the name of the supervisor. Now the table I have created contains the scores on a daily basis. So what I want is that when I enter the date and the name of the supervisor it should give me the average of the scores over that date range of all the employees under that supervisor along with the name of the employee.

View 1 Replies View Related

Query Help Needed

Sep 6, 2006

I have two tables, a Property table and a System table. They are linked so that there are many Systems linked to one Property.

Each System has a risk score and these vary within each property. My Property table has a Property risk score field and in here i would like the maximum System risk score to go.

I'm just wondering how i would do this using a query? All the system risk scores have been entered already.

Any information would be great!!

Matt

View 4 Replies View Related

Query Help Needed Please

Jun 11, 2007

Hello everybody,

I am unsure whether this is best done in SQL or VBA, however, I do believe it is possible in SQL so I will put it here.

I have a field 'value of purchase' in which an items cost is inserted. What I am wanting to do initially is automatically deduct 20% off the value of purchase every year until it reaches zero. Now I don't want to deduct 20% off the new value every year, for example:

The price is $1000, 20% deduction is $800. But then year 2 it will be $600 then the next year $400 and so on. So I suppose the first year 20% is taken off the original price, then for year 2 40% and so on (always off the original price not the newly found values).

I want to display this in a new field 'current price'.

I am just not sure how to create the query so it will automatically deduct these amounts per year. If anybody has any advice/help it is muchly appreciated.

Thanks :)

View 2 Replies View Related

Query Help Needed,will Pay For Services.

Sep 7, 2005

I have a database that was designed for our needs (not by me) and I have limited database experience. I am looking for someone who can guide me through simple query modifications to existing queries for a flat fee. Please respond if interested and the amount charged for services.
Chris O'Reilly

View 1 Replies View Related

Help Needed With Date Query!

Feb 27, 2006

Hi all,

I've searched the forums but can't seem to find anything similar. I work for social services and we have a database that stores information on children who may need services from adults when they reach 18.

What I need to do is create a query (so that a report can be run) that will show those children who will turn 18 during the year Apr 1st - Mar 31st but I'm not sure where to start. Can anyone offer any pointers please?

The fields within the database are:
Client ID, Name, Surname, Date of Birth, Address

Many thanks

View 3 Replies View Related

Query Assistance Needed Please.

Mar 9, 2006

I have DB with a Switchboard that has a button for Bin Number (Part Number). It is based on a parameter query that asks you to input your Bin Number. Once you enter the Bin Number it pulls all information for that Bin Number from a table that contains the Bin Number, description, Manufacturer P/N and Manufacturer and shows this in the query.

What I need to do is have this information each time a different Bin Number is entered to be stored in specific table (datasheet style) for printing out just a list of specific Bin Numbers and their information. As the parameter query stands now, each time I input a different Bin Number it clears the previous information. Can I tie in a make table query in some manner that will store the selected Bin Number info?

Can someone make suggestions on the easiest and quickest way to set this up? Keep in mind I am still learning Access so please be detailed in your comments. Thanks in advance for any assistance.

View 5 Replies View Related

Nested Query Help Needed

Apr 26, 2006

Hello everyone,

I’m looking for some help with a “nested query”

I have four fields that typically have a quantity amount in them.

I also have a field with a date.

I would like to say something like this

If fielda = 0 and fieldb = 0 and field = 0 and fieldd = 0 and in the date field, the date is >30day from today’s date add a 1 to this field, otherwise leave it blank


Thanks a lot

Corey

View 2 Replies View Related

Group By/Sum Query Help Needed!

Aug 16, 2006

Ok, here's what I've got.

I have an access database that links to some SQL Server tables. These tables hold time tickets which keep track of the time that an employee is logged into a job and records the associated product code and work code. All jobs have a work code, and some have a product code. The product coce is the most accurate way to track time, so if a product code is present I need to use that. If it's not present I use the work code.

I currently have a query called 'main' that gets all records in a specified date range and creates a 'code' column. This is a hybrid of the product code and work code using the method described above.

IIf([ProdCode],[ProdCode],[WorkCode])

Everything looks fine there, but when I go to a new query to get total hours by code ('TotalHoursPerCode') by grouping code and getting the sum of ManHour, it doesn't work.

Instead, it lumps all lines that have a product code together but then combines all lines that have no product code into a single line at the top. Instead of using the 'code' column to group.

Here's the SQL from the 'TotalHoursPerCode' query.

SELECT Main.Code, Sum(Main.ManHrs) AS SumOfManHrs
FROM Main
GROUP BY Main.Code;

Any ideas??

Thanks!


http://i45.photobucket.com/albums/f73/VolcomGrrrl79/access.jpg

View 7 Replies View Related

Help Needed To Build My Query!!!

Oct 26, 2006

Hi everybody,

Let's say that I've got the following tables:
- tblCustomers(IDCust,NameCust)
- tblEmployees(IDEmpl,NameEmpl)
- tblProjects(IDProj,IDCust,IDEmpl,Duration)

My query:
SELECT tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl, Sum(tblProjects.Duration) AS SumOfDuration
FROM (tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust
GROUP BY tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl
ORDER BY tblCustomers.NameCust;

What I'd like get when running my query is:

NameCust ------ IDProj - Duration ----- NameEmpl ------- ?????????
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 7
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 9
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 9
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 17
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 4
CUST2 --------- 10 ------ 5 ------------ EMPL3 --------- 17
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 4
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 27
CUST3 --------- 14 ------ 2 ------------ EMPL1 --------- 14
CUST3 --------- 15 ------ 2 ------------ EMPL4 --------- 27
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 14
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2

where ???????? (manually done) = the sum of the Employee duration for ONE Customer !!!

But what I get instead:

NameCust ------ IDProj - Duration ----- NameEmpl ------- SumOfDuration
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 3
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 8
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 1
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 12
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 3
CUST2 --------- 10 ------ 5 ------------ EMPL3 ---------- 5
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 1
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 25
CUST3 --------- 14 ------ 2 ------------ EMPL1 ---------- 2
CUST3 --------- 15 ------ 2 ------------ EMPL4 ---------- 2
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 12
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2

Any suggestion to build this Query ??
Thanks in advance

View 3 Replies View Related

Help Needed On A Query Operation

Mar 27, 2007

I am asked to display this, "List the Client Numbers, Name, and Balance fields in the design grid. Sort the records in desending order by the Balance Field."
AND THIS: "Display the top 25%"

I need to know how to show the top "25%" when I press run, in the datasheet without manually having to arrange them.

I would love detailed instructions ASAP!

View 3 Replies View Related

Help Needed With Query And Form

Mar 29, 2007

Great forum guys! Just had to join it :)

I've been doing Access for just under 2 weeks now, so far I'm doing ok, but I've just run into 2 problems. I hope someone can help me on them.

The first problem is on a query. I have a Column called "Type" in my query and the choices under Type could be RECEIPT, REJECT, BOTH

(BTW: BOTH should show all records RECEIPT and REJECT)

By using case statements as shown below, i can easily return records for RECEIPT and REJECT, however, i cannot get BOTH to work

The criteria i have set for TYPE in my query is,

[forms]![frm_Vendor]![opta] OR [forms]![frm_Vendor]![opta] Is Null

Case 1
opta.Value = "RECEIPT"
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview

Case 2
opta.Value = "REJECT"
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview
Case 3
opta.Value = " "
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview


Case 3 does not work. with " " . So what do i do here to show Both records?

My second problem is with the reports opened with the above Case code. The search routine (apart from Both) work fine. It finds the records i ask of it and it opens a report to show them, however, there are instances where no records are found, and the report still opens!!. In my search form, what code can i use to test my query for "null" data before
it even gets a chance to open the report?

Thanks in advance for any help.

View 5 Replies View Related

Help Needed On Up-date Query!

Apr 11, 2007

i am trying to update a field within a table. But i dont want to update the whole of the data within the field just one part of it.

The field is the Tutor Group for students in the format 7B, 7D, 7F, 7H, 7M, 7S, 8B, 8D, 8F,... etc where the number represents the year group! i want to use an expression that would add(+) 1 to the year group but leave the letter the same as this doesn change. By doing this the above list would change to 8B, 8D, 8F, 8H, 8M, 8S, 9B, 9D, 9F, ...etc

I attempted to use the following expression: ((*)+1)(*) but it didn't work.

I should have had the year groups separate but i am given an Excel flat file which has details of the students and doesnt separate the the two.

any help would be much appreciated! hope someone can help!

View 5 Replies View Related

Query Averaging Help Needed

Jun 7, 2007

Ok, here's the scoop. I have the following record setup for tracking shipments:

[Shipment_ID_#], [Date_Loaded],[primary_silo], [secondary_silo], [2_silos]

I need to retrieve average values from another table for certain chemistry values based on the [Date_Loaded] and whether or not one or two silos were used when loading the shipment (as indicated by the [2_silos] checkbox).

Scenario #1 - one silo

I need average values of the last four samples based on the [date_Loaded] and the appropriate silo.

Scenario #2 - two silos

I need average values of the last two samples from each silo based on the [date_loaded], [primary_silo], and [secondary_silo].

Would anyone have any thoughts on this? I've tried crosstab and select queries, but I can't get anything to work.

My ultimate goal is to have a form where users select the shipment and the appropriate chemistry averages automatically populate the data fields.

Any help is greatly appreciated.

Thanks,

Greg

View 1 Replies View Related

What Kind Of Query Is Needed For This?

Aug 13, 2007

Hello all,
I need help with making a query this will check the OrdQty for all items in a specific order against the Sup1inv field, then if supplier 1 can not fill the order have it check against Sup2inv field.
Here is an example of the data im working with

Order# Item# OrdQty Sup1Inv Sup2inv
555 widget1 2 1 3
555 widget2 1 1 4
556 widget3 1 3 0
557 widget1 7 1 3

The part i am having trouble with is making sure the query "understands" that both items on order 555 are the same order so the end result would tell me that order 555 needs to be placed at supplier 2, not 1.

Thanks in advance for the help,
Eddie

View 6 Replies View Related

Query Needed Please Help Urgent

Feb 25, 2008

Original Table
ozip dzip shipdate wgt Transport cost

A B 12/1/07 20 450
C D 4/1/08 15 890
E F 9/1/08 78 750
A B 12/1/07 42 789
X Y 1/1/08 17 900
C D 4/1/08 34 90

Needed Output

ozip dzip shipdate wgt Transport cost

A B 12/1/07 20 450
A B 12/1/07 42 789
E F 9/1/08 78 750

I need to create a query which extracts rows with wgt >60 and also WHEN the fields ozip, dzip and shipdate are same, the query should add the wgts and then sum of wgts should be >60- for example the first and fifth rows should be extracted since sum is 62.

I have attached the output that is required for the above example. please help

View 1 Replies View Related

Report And Query Help Needed....

Aug 20, 2004

I work for a company that maps schools and school information and data for a state project. I have put together a DB to enter and track all of this information. Currently, I am trying to produce a report that will group by County, then show School Districts w/in the County, and then show Schools w/in the District. On the County Group header, I would like to show the Count of Unique/Distinct School Districts w/in the County and show the Count of Schools w/in the County.

The problem I am having is that I have not been able to get the count of distinct Districts w/in the County. I am able to get the correct count for the number of Schools w/in the County and Districts. I would like the District count to show in the RED boxes on the form.


Attached is a sample of what I am working with.

Thanks,
Lodai

View 4 Replies View Related

TRIM() Function In Query Help Needed

Mar 14, 2005

Hello, need help with the Trim() function. In a query I am using this SQL:

SELECT TRIM(tblAllMembers.First_Name), tblAllMembers.Middle_Name, TRIM(tblAllMembers.Last_Name), tblAllMembers.Address1, tblAllMembers.Address2, tblAllMembers.City, tblAllMembers.StateOrProvince, Trim(tblAllMembers.Postal_Code)
FROM tblAllMembers
WHERE (((tblAllMembers.City) Is Not Null) AND ((tblAllMembers.StateOrProvince) Is Not Null) AND ((tblAllMembers.Country)="USA"));

However when I run the query, the First_Name, Last_Name and Postal_Code doesn't trim leading and following blank spaces.

Help please

View 13 Replies View Related

Left Join Query - Help Needed

Nov 7, 2005

I am trying to build a query that shows me the activity of my customers. I have a database that amongst other things holds two tables which i am trying to query.

tbl 1 - Customers
tbl 2 - Enquiries

What i eventually want to do is create a report that shows which customers have made enquiries and which ones haven't on a weekly and monthly basis.

the fields brought out of tbl1 are just the companies name, and sales person responsible for the account. Now to get all the customers to show i have learnt that you can do a left join on the query which will show all the records in the left table (tbl1) which is great and i can then limit the customer accounts that show by adding query criteria to the sales rep field from tbl1. The problem that i am having is when i try to limit the time period i.e weekly, monthly. when i enter criteria in the EnquiryDate field from tbl2 the query stops showing all the customers and only shows those that match the date criteria. Can anyone tell me how to prevent this from happening.

Thanks

View 5 Replies View Related

Report/Query Needed- PLEASE HELP A Begginer!

May 23, 2006

I am new to Access and have to create a report that finds a discrepancy in an one of the tables.
there are 2 fields- field A and field B

the report needs to list all the intances the same string appears in field A, but had different values in field B

for example

A_____________B

apple_____________cow
pear_____________cow
banana_____________cow
pear _____________cow
apple_____________pig
banana_____________pig

the output should be:

banana
cow__________ pig



how can i go about creating this report?
Any help would be appreciated, this is a work assignment that needs to be completed ASAP.

Thank you!

View 1 Replies View Related

Query Is Excluding Needed Information

Mar 1, 2007

I have created a query showing locations of a company's facilities, and product groups associated with that facility. Unfortunately the the products groups are located in one column and, if the facility produces more than one product, the product group is divided by a |, as shown below:
CABLE TRAY SECTION [05CT]|STEEL CONDUIT AND ELECTRICAL METALLIC TUBING SECTION [05RN]
In my query, I only want to show the product code, which I resolved by creating another table and associating the code with the product group. However, this query will exclude those plants that produce multiple products. How can I show just the product codes for all the facilities, including those with multiple product groups?

In otherwords, I would like the above example to look like this: 05CT|05RN (or something similar to this).

View 4 Replies View Related

Unusual Query – Advice Needed Please

Jan 15, 2008

I have a list of events and what the out come was on a particular day or date.

To give you some idea, the event is basically number given to a day and the outcome is all possible things that “could happen” and that did happen (so not all 160 possible Outcomes are used all the time).

So it is quite possible to have:

Event 1 (This could be “Monday”)
Outcome 12 (This could be “Rain”)

Therefore as you collect your data over time the “Outcome Diff” would = 12

The problem comes when I try to create a report to automatically calculate the “Outcome Diff” this in a Query as the “counter” that should have been added at the time this was created was not done at the time. I also do not mind if this has to involve creating to a table, as long as I get the “outcome diff” calculated in MS Access, I do not mind how this is done. I also cannot use date diff as event mean more than just an index for the day.

Event, Outcome, Date, Outcome Diff
12, 1, 14-Feb-95, 12 (0 to 12 = 12) i.e this is the 1st event outcome of 1
22, 1, 19-Apr-95, 10 (12 to 22 = 10) i.e this is the 2nd event outcome of 1
29, 4, 07-Jun-95, 29 (0 to 29 = 29) i.e this is the 1st event outcome of 29
34, Ect...
*This is just a representation with test data.

Just for further clarification: the reason this is need this is to find out difference in "event" days as opposed to just an average.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved