Picking Biggest Default

Mar 21, 2006

I have a table with ID1, ID2, and AMT.

IF ID2 is NULL, I want to update it with the non-null ID2 where the ID1 matches and has the largest sum of AMT.

For instance

ID1 ID2 SUM(AMT)
1 333 1000
1 444 3000
1 <null> 5000

In this case, the third ID2 would get the value of 444 since it had the highest amt.

I'm guessing this involves sub-queries, and I have a real blind spot where they are concerned.

Thanks,
David

View Replies

Queries :: Max Not Showing Only Biggest Date

May 13, 2015

I have 3 Tables with relationships between Pupils to Read and Books to Read:

Pupils ID, Forename, Surname
Books - BookID, Book, Author, Level, Genre
Read - ID, BookID, DateOut

On another thread in the forum I got to the position of the code below. However this code does not select only the read book with the biggest(latest) date, the Max, it displays all Read books.

SELECT Pupils.ID, Max (Read.DateOUT) AS MaxOfDateOut Books.Book
FROM (Books) RIGHT JOIN (Read) ON Books.BookID = Read.BookID) RIGHT JOIN Pupils ON Read.ID = Pupils.Id
GROUP BY Pupils.Id, Books.Book
HAVING (((Books.Book)<>"Blank"));

View 14 Replies View Related

Finding Field With Biggest Number Of Characters

Feb 26, 2008

I have a table with about 20.000 records.
From this table I would like to "search" in a column the field that holds the text with the most characters.

Is there a way to find this with a formula or expression?

View 3 Replies View Related

Not Picking Up Table Value

Dec 10, 2007

Hi everybody,

I have the following table:

ID ThicknessFieldRange1 ThicknessFieldRange2 Factor
16 0.0641 0.068 0.97
17 0.0681 0.072 0.98
18 0.0721 0.076 0.99

Now, I have a form that has a 'Thicknesstxtfield' that when calculated in the form its value is 0.072 in this case . Based on the range this value falls into, I want to get the correspondant 'Factor' which in this case would be '0.98'.
Guess what, ACCESS is telling me that it cannot find the value '0.072' in the table and gives me an error. Any ideas on this?

Notice that all the fields except "ID" are 'Doubles' with 4 decimal places, and I find the values using the 'DLookUp' function. I know that's where the error is because all the other ranges work fine.

Thanks everyone

View 4 Replies View Related

Picking Orders For Invoices

Oct 31, 2006

Hi everyone,

I'm sort of new to Access, but over the past few months I've been able to develop an inventory database for my work which turned out quite nicely and does everything I need it to, except for one thing.

I want to also create invoices for my customers, and after looking at examples I understand that most people link the "order" with the invoice, so that there is one invoice per order. However, I need the ability to combine many orders into one invoice. That's where I'm stuck.

I don't know how to arrange my relationships and forms so that I can "pick" the orders I want on my invoice, and not at the time of the Order, but at some point after. Basically my orders will be established, but their allocation to invoices won't, until I come along and put them into their rightful bill.

Does anyone have any thoughts on this? I'm pretty stumped.

Thanks a lot for your help.

View 2 Replies View Related

Query Not Picking Up The <> Operator

Jul 3, 2006

I have two tables in the first table there is an outline number and the second table there is a task id (both are text strings, i.e. outline number and task id would be something like 8.15.3.1)

I want a query to return all the results in the first table that are not equal to the second table

I have tried the following but it returns all 54 records
Code:SELECT DISTINCT RawData.TeamLeadNumber, RawData.Category, RawData.OutlineNumber, RawData.OutlineDescription, RawData.Start, RawData.Finish, RawData.Milestone, RawData.PercentComplete, RawData.DeliverableDesc, RawData.ReleasePeriod, RawData.OutlineNumber2, RawData.OutlineDescription2FROM RawData, SharePointDataWHERE (((RawData.OutlineNumber)<>SharePointData.TaskID));

I have tried changing the where clause to have it as
Code:WHERE ((RawData.OutlineNumber)<SharePointData.TaskID) AND ((RawData.OutlineNumber)>SharePointData.TaskID);

but then no data is returned, and I know that of the 54 records 21 of them are the same so I should be seeing 33 records

View 1 Replies View Related

Query Not Picking Up Current Record

Sep 9, 2005

Hello,

Can anybody see what I'm doing wrong here. I have the following query

SELECT Contacts.Cust_FK, Contacts.ContactNr, Contacts.FirstLastName
FROM Contacts
WHERE (Forms.ComLogDetail.Cust_FK=Contacts.Cust_FK)
ORDER BY Contacts.ContactNr;

The "Forms.ComLogDetail.Cust_FK" part should render the current record in the form ComLogDetail but its not. Any ideas?? I'm an amateur programmer so my apolgies if the solution is all too simple.

View 3 Replies View Related

Query Only Picking Up 1 Didgit From Table

Mar 15, 2007

Hi

I'm a bit new to SQL, and have what is probably a rather simple question:

I have a query, which i want to pick up a figure from a table.

SELECT *
FROM Feb_Closed
WHERE Time_to_Close>'6' AND Team_Owner='Comp& Ben';

This give me a load of rows with Time to close between 6 and 9 but nothing else. The problem it is not counting everything over 9 ie i think that it is just looking at 1 digit in the table and so ignoring 10, 11, 12, 13... etc etc.

Any help gratefully received.

Thanks

View 3 Replies View Related

Picking Of Single Data Out Of A String Of It..

Jul 31, 2007

hi,

I am trying to pick out the ERAP no.s out of a whole string of data. ERAP no.s are like invoice no.s. Do I Append or Make table? How do i go about doing this? I want the ERAP no. in a row next to the the description field all next to its respective string of data too....

for an example..

1)ERAP43463 STAFF TEAM LUNCH MID-YEAR REVIEW

2) 0507 SUBMITTED ITEMIZED ACCR-ERAP43159

See.. the part where the ERAP no. appears is inconsistant.. if not i could simply use excel and use text to columns. We have thousands of lines like this every week... There has to be a way to pick out just this detail and fill in the column next to the respective datastring...

Can someone please advice me on how i can go about doing this?

View 2 Replies View Related

Forms :: Picking Up Wrong Record

Dec 6, 2013

I have a table called "Cities" which has only two fields; City & Province.I have connected it in a relationship with another table as "Include ALL records from 'Cities' and only those records from 'tblInstallations' where the joined fields are equal."I have put an "AfterUpdate" event which works and everything.

It is working fine and updates the province correctly.The problem is that is putting another city from same province in the "City" field even though I picked up different name.

View 6 Replies View Related

Queries :: IIF Statement Only Picking Up First Value From Table

Jun 10, 2013

I have a table of rates based on ages and I am doing a query that will calculate your rate based on your age. I am using the IIf statement like this:

EERate:IIF([EEAge]>25,.345,IIF([EEAGE]>30,.425,.3)

The query will only use the first rate even if age is above 30.

View 3 Replies View Related

File Picking To Fill A Form With Filepath

Jan 18, 2006

I'm trying to get a file picking window to open in order to fill a form in access with the files path...and honestly...I have no idea how!
Any advice?

Thanks!

PS: I work on Office XP

View 1 Replies View Related

Forms :: Form Not Picking Up Previous Entries

Sep 9, 2013

I'm fairly new to databases and have been attempting to build a multi-table database. It's not properly rationalised, but I wanted to test run it to ensure it was fit for purpose first.I've built my form, which is pushing data out to several tables, but I cannot seem to get the form to pick up previous records.

View 3 Replies View Related

Forms :: Form After Close And Reopening Not Picking Up Fields

Sep 11, 2013

I have cleared the data out of the database and started testing it to check its usability.I entered in a full record, and flicked back and forth between the records, and everything was looking good.

Then I closed the form, and re-opened it and my record was no longer populating the form fields.I've literally changed nothing bar one field that was changed from number to text.

View 5 Replies View Related

Queries :: After Importing Data Query Not Picking Up Newly Added Records

Apr 21, 2013

I created a database. exported some Excel data into Access tables, created a number of lookups, default values etc. and created a pretty simple query.

My problem is that after importing and tweaking the data, when I add new records, my queries do not pick them up!
I have tried:

1. saving, closing, opening and re-running the queries.
2. putting an Nz expression for each field in the query as I read that null values may cause a problem.
3. wrote the query again, field by field to see if all records were received.
4. Exported the table back to Excel and imported to a new Access table in my database

None of this works.

View 7 Replies View Related

Forms :: Merging Attendance In School Report Card And Also Picking Highest Score

Feb 7, 2014

I want to have a report card where I can have attendance embedded in for each students and also the highest score for each class each session.

View 4 Replies View Related

Default Combo Box Based On Default Table Value

Feb 13, 2006

Hi Everyone,

I hope someone can help.

I have a form with a combo boxes and a table with relevant list and additional field, fldDefaultDrive (Yes/No Field).

Currently in order to set the default value, I have used the following code for each default;

Private Sub Form_Load()
Forms!frmMediaLabeller!CboDriveName.DefaultValue = """D"""

End Sub

However, I want users to be able to go into the table and change the default value if thier CD player default Drive is anything but D: Drive. I have tried to replace the D above with an SQL statement but with no success.

Private Sub Form_Load()

Dim Drivename As String

Drivename = SELECT tblMediaDrive.fldDrivename FROM tblMediaDrive WHERE (((tblMediaDrive.fldDefaultDrive)=-1));

Forms!frmMediaLabeller!CboDriveName.DefaultValue = """Drivename"""

End Sub

This is definetly not working, can anybody help, I have a feeling it is syntax but not sure where? :confused:

Robert88

View 7 Replies View Related

Forms :: Picking Up Sub Form Totals From A Main Form?

Mar 22, 2013

I have converted an old Access 97 database to Access 2010. Mostly it works fine but I have a major issue with the invoicing forms. It was working in the old database but I cannot get it to work in this version.

I have the usual invoicing option where the lines of the invoice are displayed in a sub form for that customer and line totals calculated. This works fine. I have a sub form total text box in the footer of the sub form which I want to pick up from the main form so that I can add the delivery charge and VAT.

View 6 Replies View Related

Input Date By Picking Date From Calender

Jun 14, 2005

I am not a professional programer but would like to do a simple database for my group to track research progress. Is that possible to "pick up a date from drop down calendar" then the date will input to the cell (save in table) in a form, instead of typing it in? I saw that in lots of web sites, but can not figure it out how to do it in Access. Tried "Canendar Contol" and "LANDesk Data control" in the toolbox.

Thank in advance

PK

View 4 Replies View Related

Your Help In Default Value?Please

Jan 30, 2008

Dear Sir...:)

Please your help it is urgent for me.

There is to table:
First table name (UserName) with fields:
1- UserID
2- name

Second table (Website) with fields:
1- Id
2- UserID
3- WebSiteURL
And there is one-to-many relationship between them.

what I have to do if any new users register by default; it gives him URL (I have to let default more than one).:confused:

Please your help ASAP.
Regards;
FAIZ

View 14 Replies View Related

Set Default Value

Oct 29, 2004

I am trying to set up two date fields in a table, and I would like the second date field to default to the value of the first date field for each record - is this possible to do in the table design?

View 2 Replies View Related

Default Value

Dec 7, 2004

I want to set the default value on a table to 'Previous'. Basically I want to display the value in the field above it, so say if I have a company name "bobs boats", I want the next company name to default to "bobs boats". Can you do this??

View 9 Replies View Related

Default Value

Jul 5, 2007

Hey,

Ive got a table set up which is not calculating properly (or least i cant seem to get it to) Basically i have a row for quantity and a row for unit price. What i want to do in my table if possible is times the unit price by the quantity so it will come up in my reports which at the moment it isnt doing, as the default value is 0 in my table!
Might anyone be able to help me on this matter?

Thanx in advance

View 2 Replies View Related

Default Value

Oct 22, 2007

How can I set the previous record as data source for default value in MS Access table?

View 3 Replies View Related

Default Day

Jan 25, 2005

I have a billing date on my form.
Each billing date should be on the 15th of any month (1/15 or 2/15 or 3/15, etc�)
Is there a way to default a day to the 15th and then make the user enter month and year?
Or if there is no way to default the date to the 15th, may be there is somehow I can check to see if day that user entered = 15th?
Thanks.

View 4 Replies View Related

Default

Jun 2, 2005

How can I Default a Text Box value by the input of another two Text Boxes. This Expression on the Text Box Default Value does not work.

=IIf([Text0]=0 Or ([Text8]="<" And [Text0]=1),"White",IIf(([Text0]>=1 And [Text0]<=49),"Blue",IIf(([Text0]>=50 And [Text0]<=499),"Orange",IIf([Text0]>=500,"Yellow","No Input, left blank")))) :confused:

Other Text Boxes follow, I want to see the Default Value as soon as I leave Text0 and Text8.

View 7 Replies View Related