Another Quick (i Hope) Query Question

Jan 15, 2006

hi there

could somebody help in the query design in order to query table 1 (image attached), by showing the maximum weight for each component_id, but also include other details such as the date when the maximum occurred and the event_id, the result as query 2 (image attached)

if i just select component_id and weight, i can do this by selecting max weight, but when i add the date and / or the event_id columns then i get duplicates in the component_id column

any help would be greatly appreciated

View Replies


ADVERTISEMENT

Quick Query Question

Jan 13, 2006

Just a quick question.

is better to use a query for a form or just link straight to table.

e.g you have tblColour, this table has ColourID & Colour.should you build a query including all fields then build a form from that query?

Thanks

View 2 Replies View Related

Quick Query Question

Nov 9, 2005

I have a table with 3 fields... ID, Name, Hours

ID is NOT unique in the table
IDs Can have different names
Hours is what I will use to find the total hours

I want my query to group by ID only, and show the SumofHours
BUT... I want to show the name as well(without Grouping by it), I want my query to Show me the name that is MOST Recurring.

So lets say that ID '1' occurs four times, with the Names...
'Mark' 'John' 'David' 'John'...
Running the Query I would want to see 'John' under the name field Right Next to the now Unique ID '1'

At first I thought I could just use the Max function from the drop down menu, but that only uses the one that is alphabetically a maximum... I need it to be a maximum by count..

Any help would appreciated,

Ayyad

View 2 Replies View Related

Quick Addition To A Query Statement

Jul 29, 2005

I have a query statement:

Final Tax Free Amount: IIf(Left([Final Tax Code],1)="k",Right([Final Tax Code],Len([Final Tax Code])-1)*-10-9,Val([Final Tax Code])*10+9)

If I wanted to include: If final tax code = BR or NT then = 0. How can I add this to this statement?

thanks

View 1 Replies View Related

I Hope That Someone Can Help

Dec 12, 2007

I have a database the keeps tracks of printers by serial number. There is a form for doing this, the form has a serial number field, and a status field "example move, add, remove" , effective date and a location field. Here is what I want to do, lets say that the current location for serial#ABC is in location C and then the user opens the form and tries to move serial #ABC to locaiton C, how can i display a message saying that Serial#ABC is already at location C, maybe I can do this by a DLookup or by a record set, but the only thing that i'm struggling with is how do I make it look at the latest record by the effective date. Thanks.

View 6 Replies View Related

Query Criteria Question, Quick Answer Needed

Aug 23, 2006

Thanks for helping me out with this question. Im sure this is pretty easy to do. I have a query with a few paramaters set up... one of them runs a custom function all i want to do is this

query one
Average water flow:Avg([WaterFlow])

when run this just shows me the average water flow for the database... now if i have another criteria i want to base off the average how do i get the query to realize that. for example it would look something like this

[Average water flow]/[currentFlow]

how do i reference another criteria? hopefully this isnt to hard to understand.

View 1 Replies View Related

Simple Problem I Hope, Please Help

May 10, 2006

:eek:

--------------------------------------------------------------------------------

I have made a reasonably simple database, for some reason I am struggling to send of reports by Emails.

The piece of code I am using is:

Private Sub cmdEMailRptPastNonCon_Click()
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmSwitchboard", acNormal

On Error GoTo Err_cmdEMailRptPastNonCon_Click
Dim stDocName As String

stDocName = "rptPastNonCon"
DoCmd.SendObject acReport, stDocName

Exit_cmdEMailRptPastNonCon_Click:
Exit Sub

Err_cmdEMailRptPastNonCon_Click:
MsgBox Err.Description
Resume Exit_cmdEMailRptPastNonCon_Click
End Sub


A box appears with the options [rich text,html,snapshot etc] I choose an option and a message box appears saying: "The database engine could not lock "tblMain" because it is already in use by another person or process., I know this is probably a simple stupid error ive made but being a complete beginner I am stuck, any ideas of where I should look to find any errors?

Thanks for any help recieved.

Mat

View 3 Replies View Related

Easy Question, I Hope

Dec 15, 2005

Could someone tell me how to change the border colors on a form. There are lots of posts on changing txt border colors and such but i want to change the form border its self.

View 5 Replies View Related

Easy IFF Question....I Hope

Sep 6, 2006

I have a form that I has a password field that matches the employee name. I want the employee to enter in a data field their password. If it matches I want another field (verified) (yes/no field) be marked as yes if it matches and no if does not.

I did IFF([Text5]=[Password],[Verified],= -1, [Verified]=0]

But it didn't work. Any suggestions.

Thanks for the help

View 1 Replies View Related

Very Easy Question I Hope

Aug 17, 2006

I know how to do this and I think it is simple I just cant remember right now. I have a field in a query called LastActivation. I also have another field called DateEntered. In the query I want to have LastActivation=DateEntered. How do I do this?

View 6 Replies View Related

Newbie - Hope I Don't Have To Start Again.

Jan 24, 2008

Hi there. I created some forms using the wizard. But I set up the properties on my form so that the navigation bars don't appear. I only want them to be able to input new records. I also added code on the form_open event to default to adding a new record. But I noticed that if i use my scroll wheel on my mouse, I can still view previous records. How do i prevent any previously saved records from loading?

Also, where do I add a message box to say that the data has been saved? i noticed that when the form is closed by using the X button or when they navigate to another form, data is automatically saved. would this be the after_update method?

Thanks.

View 6 Replies View Related

Baffled: Gosh! I Hope Someone Will Understand And Be Able To Help Me!

Nov 28, 2007

Action:
1) Update the blank fields in a table + add newly imported records but...make sure that there are no duplicates.
I need to Append the records from a table called: XLS_Imp_11_27_07 to my main Table " Invoice Tracking for A/P 10_30".
The fields found in the XLS _ table: Release Dt, Entry Dt, Liquidation Dt may have been populated by a live report found on Internet. Since it's a live report, when Appended, to the other table, it will create numerous duplicates.
The fields found after the 7 first fields in the Invoice Tracking Table may have been updated by users

Is there a way to avoid duplicates and get the data from one table to the other without wiping out whatever is already filled-in?
:(

View 8 Replies View Related

Pulling My Hair Out! Hope I Can Explain

Jul 16, 2005

Let see if I can explain my goal. I've been fighting with this for some time with no resolve. In the included DB has a table that represents the fields that will be queried in the real DB. It shows records of one of many employees that were "tested" (shopped) on a date and whether it was a successfull shop or not. My goal is in three parts.

1)A query that goes through each employee and returns the records with the check box in SuccessYes checked (Yes) back to the last unchecked box, not including the unchecked box. In the example given the last (most recent) 5 records would be returned. If the shop of date 1/18/2005 was not checked, it would only return the last 2 records. Basically, I'm looking for a way to have a report that shows everyone's current successes in a row. It could be any number.

2)A query very similar but it returns only those employees that have the current successes in a row totalling 5 and where the field "Award5_Paid" is NOT checked (they have not been paid their reward). From there it should be easy to do one for the 10 in a row.

3)If I've got a form open (or...?) where I can place a command button, that when clicked, it appends the selected records with checks in the appropriate boxes for being paid. Example: If a set of employees come up in the query with 5 successfull shops in a row because the "Award5_Paid" field is unchecked, then I need to pay them. Click the button and the Award5_Paid field gets checked in THOSE RECORDS ONLY so that the next time I run the query, they won't show up and get paid twice. Issue? What's the coding or SQL or ??? that's run when clicked.


I hope that makes sense. I've been running around in circle trying to figure this one out. Maybe it can't be done, but if it can, I know some of you know how.
As always, thanks for the assistance.

-Jim

View 3 Replies View Related

Hi And A Hope Of Help Over Listing File Names In Access

Nov 14, 2006

Hi, newbie here to this forum, been looking about for ages to try and find an access based forum.

I am not a great coder, have not really done it, nor remotely understand it, but I have still managed to write a database in access for the office.

We run a residential care home, where we had 5 databases (MS Works) which held different information of the residents within the home but duplicated name, dob, etc. I have successfully writen a access database which works well. But now I want to start pushing it a degree further if I can.

We took delivery of a HP officejet all in one printer, and it has a scanner with document feeder which we scan letters in so we can share them with head office, but what I would really like to do, is scan letters in relating to a specific client, and be able to save the file name as a link, so an other user on an other PC can open the document on their PC. Basically, I want to avoid us going to the filing cabinet to refer to a letter which was written to us.

I am using XP Home, with Office 2003 Pro, and Acrobat 8 Pro.

Any ideas where to even start looking?? I did do a search, but have no idea how to start.

Thanks in advance.

View 3 Replies View Related

Simple I Hope - Converting Feet/inches To Decimal

Nov 11, 2005

Hi - I am writing a small form that has a calculated field. I want to convert feet and inches in length to metres, and vice versa - so user inputs 10.45 metres, and I will have two fields giving the answer (I suppose) - one with the whole numbers before the decimal place (the feet); the other taking just the numbers after the decimal and converting them from /10 to /12 for inches. Think I can work out the reverse if anyone has a nifty way of doing this - thanks very much; I'm new to this site.

J

View 4 Replies View Related

Copy From Mainframe" Hope To Get Help"

Sep 10, 2005

hi guys I hope to get help on this, I have an excel sheet which is like a letter with fields that I need to copy from mainframe. Does anyone know how to do it. Please help if you can, this is important for me. I need a macro in excel to copy fields from the mainframe system to the excel defined fields. I just need to get started. Thanks a lot.

View 7 Replies View Related

Quick One SQL YES?NO?

Nov 4, 2006

hey guys quick one

when creating a table in SQL how do you set a variable to yes/no i tried the BIT command but didnt like that so is there any other way?

cheers guys

View 2 Replies View Related

2 Quick Questions

Oct 16, 2005

Hey guys, I'm just doing this database and have suddenly realised I dont know as much about MS Access as I thought!

Q.1 - I need to run a query that displays the top 5 records in a table, how would I go about doing this?

Q.2 - In my tables, I have of numerical data like the following...
http://img276.imageshack.us/img276/2695/11ch1.png
is it possible to make a Totals column which automatcily adds up the data in the Fraud, Competition, Loss of data, etc rows? (similar to MS Excel if possible)

View 6 Replies View Related

Quick And Easy One

Jun 20, 2006

HI

I am creating this bloody db and I have created a form and when I open it, it shows, record no one, what I want it to do is open blank ready for a new bit of info to be put in. I know this is an easy one but im sick of the sight of access this week

With thanks

View 5 Replies View Related

Quick Question

Jan 17, 2007

Hello

Great website. Does anyone have any information on the leafs (http://a-cholesterol-diet.com/index.htm). I have been looking all over the place but no one seems to have any information or listings of any websites, any help would be greatly appreciated.

Thx

View 1 Replies View Related

Quick Question

May 30, 2007

I have done a little database for someone so they can keep track of available dates for their apartments in Turkey. They want me to add it to their website so people can look for themselves. I know I should join a beginners web design forum, but can anyone tell me how i actually log into a website? I have the wesite address and the password. Thank you in advance Ben

View 5 Replies View Related

Quick Question

Jun 5, 2007

Hi all,
I am just wondering why Modules have the name of bas at the start of the name. frmForm and rptReport makes sense but why bas for Modules ?

Thanks in advance,
Mitch.....:confused:

View 3 Replies View Related

Just A Quick Question

Oct 29, 2007

I just joined and wanted to say hi to everyone. Also, I noticed that the forum runs off my screen.

Anyone else notice that?

melanie

View 2 Replies View Related

Quick Question Regarding 0

Jan 9, 2006

Hello,

one of tables requires me to store sickness details, the sickness codes are all unique, they do however start with at least one 0 for example code - 000003 Description Stress. When you enter a code access automatically trims of the 0's to leave a 3.

Is there a way to override this im unaware of?

View 14 Replies View Related

Quick ? About Tables

Apr 2, 2007

i am using the following query to make a table. i was wondering if anyone knew a way to have the table be named from data entered into a text box in a different form. so when different people type in different names a new different table will be created. thanks

CREATE TABLE tbalename (First_Name TEXT(20), Last_Name TEXT(25), dob DATETIME, Constraint Employees_PK Primary Key (First_Name, Last_Name, dob));

View 2 Replies View Related

Quick Question

May 4, 2006

Bit of a newbie so help appreciated.

The system I have developed lists jobs (as in jobs that staff members need to go and do) in different towns and cities. These jobs are set as active or inactive depending if completed.

I need to run a report that allows the user to find active jobs in a particular town. I have made a query and by setting the status field to a criteria of "active" this ensures that all results in the query are only active ones.

I cannot find a way to allow the person running the search to enter the name of the town that they want. This would then allow the query and then report to list all "active" jobs in a particular town.


I have a table containing all the towns so hope I can offer the user running the search a means to either type the name of the town into a box or even better (to reduce typo's) be able to choose the town from a combo box.

Once the town has been selected, the report will list all active jobs in that particular town.

Thanks

Can anyone

View 1 Replies View Related







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