Only Show Lowest Cust ID Per Account
Nov 3, 2006
Hello,
I have searched through the forum, but cannot find a suitable answer to my question, so here I am.
I wonder if you could help me?
I have a list of Customers each of which has a Customer ID then I have a list of accounts which may have more than one customer ID.
For this senerio I would like to only show the record that has the lowest customer ID.
I.E. If one account has two customers take the lowest customer ID.
Does anyone know how to do this in SQL or VBA?
Thanks for all your help.
View Replies
ADVERTISEMENT
May 24, 2006
Hello there...
I have a data set as follows:
Product CompanyA CompanyB CompanyC .... CompanyZ
aaa 2.3 2.4 2.5
bbb 3.4 - 3.1
and so on...
The value represents the price of products...
I want to make a query that will show me results in following heads..
Product MinimumPrice CompanyName
aaa 2.3 CompanyA
bbb 3.1 CompanyC
How can I proceed??
Looking for some help
Regards,
View 1 Replies
View Related
Jan 16, 2008
hi,
I have a table that contains itemcodes and prices. An example of the records would be:
<Item Code> <Price>
Product001 £34.56
Product001 £49.23
Product001 £23.22
Product001 £98.43
Product002 £12.45
Product002 £54.34
Product002 £25.51
Product002 £76.84
Product002 £14.97
I would like to run a query that returns the lowest value price for each Item code. So in this example it would return:
<Item Code> <Price>
Product001 £23.22
Product002 £12.45
Does anyone know the criteria code in the query design view for this problem?
I know it sounds silly, but there are unique codes in another field, i just need to query against these fields.
Thank you for your help.
Dean
View 3 Replies
View Related
Oct 24, 2006
Let's say I have a table with the following data:
Customer:
----------
name | city | salary
john| Boston | 14000
billy | Boston | 32000
sam | Boston | 12000
jj | Dallas | 6000
greg | Dallas | 8000
josh | LA | 1200
ally | LA | 600000
bill | LA | 12000
bob | LA | 9800
how can i list the city with the lowest average salary?
I need to find the average salary of each city(should be 3), then choose the lowest from those 3 averages.
Can anyone help me?
View 6 Replies
View Related
Feb 2, 2008
I'm using a filter on the form load of one of my forms that potentially would have 3000 records. The idea is to speed the process up a little.
I'm using:
If IsNull(Forms!frmProjectMain!ProjectID) = False Then
DoCmd.GoToRecord , , acFirst
end if
Me.FilterOn = True
Which works fine. However, I'd rather it was the ID with the lowest numeric value that was displayed and filtered rather than the last record chosen. i.e. 1 instead of a random number.
Does anyone know how i'd go about writing code that would tell access to go to the lowest ID to filter?
thanks for any help I receive here
View 5 Replies
View Related
Feb 23, 2006
Hi all - I have a table that contains Client ID, Supplier Name, Order Number, Order Date, and Price.
Example -
Client ID -- Supplier Name -- Order Number -- Order Date -- Price
1111111 Supp A 222222 1/2/06 1,000
1111111 Supp B 222222 1/2/06 2,000
1111111 Supp C 222222 1/2/06 4,000
1111111 Supp D 222222 1/2/06 500
1111111 Supp E 222222 1/2/06 1,200
I need to determine the 2 lowest prices per order number. In this case that would be Supp D - 500, and Supp A - 1000.
I am using the Top function and sorting the price in ascending order to get the lowest two prices. The problem is that it selecting the lowest 2 prices of all the orders on the table, not each individual order. I tried grouping on the other fields, but still came up with incorrect results.
Any ideas? Thanks!
B
View 3 Replies
View Related
Mar 13, 2008
I have a table that has a list of order information, there are multipule records per order that have information about when the order was processed. There is a day tied to the process time of each record, I need to filter out the duplicate records and get one record for an order, but that one record must be the first process time entry for that order. Anyone know how to make this query? Let me know if this doesnt make sense.
View 4 Replies
View Related
Mar 17, 2014
I have a relatively complex update that I need to perform on a table from a form. I have a system that has "games" and "game copies". The game is simply a name of a particular game and the copy is something that has a stock number but a foreign key of the game catalogue number. This means I have several catalogue numbers that are the same in the GameCopy table.
The problem is that I have to reserve a game - not a game copy. When I have made a game available (it has been returned) I have to indicate this in the reservation table. I could have many different reservations for the same game so I need to only update the oldest reservation and indicate that a game copy is available now.
Summary:
One "Game"
Several "GameCopy" using "Game" as a foreign key
Reservation table with possibly several reservations for the same Game - not GameCopy(s)
In other words I have an "Available" field in the reservation table and a "date reserved". I need to create an "Available" (Date()) entry for the oldest DateReserved entry on that reservation table. I could have done it as a boolean but I decided to use a date instead for logging purposes.
Would I use some kind of "Once only" action to make sure that only one of the reservation entries are updated? I really do not know how to proceed with this.
Obviously if I simply:
UPDATE Reservation SET Reservation.Available = Date()
WHERE Reservation.CatalogueNo=Forms![Current Reservation].CatalogueNo;
...then it will update all of them. I believe there must be a bit more SQL I have to add or something else maybe.
View 5 Replies
View Related
Sep 4, 2006
I must admit I am a "newby" to Access but I have bought my Access 2000 bible, as I am running Access 2000, and attempted to create my desired database. I have linked to 2 tables that I download on a daily basis. This is my inventory from two different suppliers. I then have created two queries that filters each of these files to only show positive quantity items, filters out item specifics, etc.What I want to do is join the 2 queries with their data already filtered. Both of the files from 2 different suppliers contain some of the same data. Once joined if there is an item that is the same I want to delete the higher cost item and only show the lower cost item, I also want to show the rest of the items that do not match. It is easy to identify the same items as every item contains a 12 digit identifier called a upc.I can create a UNION ALL query that shows every item from the two queries. However, I am unsure as to how I go about deleting the higher cost item and only showing the lower cost item if the item is the same. On a side note after I get the results desired I will then be createing an append to query and append all this information to another file that is then uploaded to another system.I will try to attach some sample data that I am using from the two queries I want to join, this data is only a sample of a much bigger file.Thank you and any and all comments or suggestions is much appreciated.
View 3 Replies
View Related
Feb 28, 2008
Afternoon all,
I have another account on this forum - dickohead, but I am unable to post or reply to any topics with it.
Can someone please help me with this?
Dickohead.
View 3 Replies
View Related
Sep 9, 2014
I have attached a sample of a database.
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
View 8 Replies
View Related
Dec 15, 2005
I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name.
Example:
Starting file
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC
05512,3,2,4,2
ENDING table needed
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, NEWCOLUMNname
05512,3,2,4,2,2 ORIGIN2 ORIGIN4
Where the new column name contains the MIN number in the row and all of the associated duplicates column names.
View 1 Replies
View Related
Dec 4, 2006
Hi,
After I setup user-level security, and add the new users with Admins permission.
Why I cannot delete the Admin account?
Can we delete the Admin account forever after setup user-level security?
Please let me know about it.
Thanks.
View 1 Replies
View Related
Nov 10, 2005
Hey
I currently have this code which tells the user there is a an accout number already on the db:
Private Sub AccountNo_AfterUpdate()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * From Spreadsheet where AccountNo = '" & Me.AccountNo & "';")
If rs.RecordCount > 0 Then
MsgBox "This Account Number Is Currently Within The Database And Will Not Be Allowed.", vbInformation, "DD Cancellations"
Me.AccountNo = Null
End If
End Sub
I want a peice of code which instead allocates a letter to the duplicate account, so user enters a duplicate account, the account will then turn into 123456789A, upto 5 duplicates are allowed, so upto 123456789E. Once "E" has been reached accountnum box will turn null with msgbox. I tried out Case statements but that doesn't seem to work.
all help appreciated. Thanks.
View 8 Replies
View Related
Nov 27, 2006
Hello All,
I am trying to make an Update Query that will update a table that has the same account numbers and assign them a value....ie.1,2,3,4 and so on. Does anyone know how I can do that?
Example:
Accout Num Assigned Value
12345 1
12345 1
12345 1
12544 2
12544 2
12568 3
12569 4
View 3 Replies
View Related
Feb 3, 2006
I have a list of account numbers for members, they have distinct member numbers. I have it set up for users to search for the member, then the member number will auto-populate. I also need to account for members with no member number. I would like to have the user click on the check box "Non-Member" and somehow assign a Member number...starting with NM. So the first non-member would be NM00001, next NM00002. I thought I could create a table with the sequence of NM numbers. I am just unsure of how to tell my form to populate that field when the Nonmember box is checked.
Any ideas?
View 1 Replies
View Related
Jul 13, 2013
A client wants to e-mail newsletters using a non-default Outlook account. The code below does everything the client needs except setting the SendUsingAccount. In debug I can see that the correct account is assigned, yet all of my testing results in e-mails where the From line is the default account. The test setup uses my own isolated SMTP server, so when I look at the e-mails sent the sender is the default account. The default account's Sent folder shows the sent mail, which is not what we want.
Code:
Sub prepEmail()
Dim frm As Form, startDate As Date, endDate As Date
Dim rs As DAO.Recordset, strSQL As String, intNewsLetter As Integer
Dim rsEmail As DAO.Recordset, rsNewsletters As DAO.Recordset
Dim OlApp As Object, ol As Object
Dim olMail As Object, olAcct, olAcctTemp
[code]...
View 3 Replies
View Related
Apr 7, 2013
It seems I've overstepped in setting up Users and Permissions, in MS Access 2007, on an .mdb. When I enter any of the user names and passwords I've created, I'm told that it's not a valid account name or password. I'm wondering how this could be possible since I'm staring at them in the Snapshot file.
My question is this: I can access the .mdw file as "Admin", is there anyway to unwind this mess?
View 1 Replies
View Related
Jul 2, 2015
I have a several templates in outlook 2003 for my charity work.
All is OK if I send the odd one now and again, but the workload is increasing with more and more at any one time. However having seen various posts on here, I was thinking I could set something similar in Access as well as keeping track of the payments & deposits more easily.
I am fine with to, cc,bcc, body, subject etc, but what I need to do is SendUsingAccount, but 2003 does not have this property, it started with 2007.
As well as sending on that particular account I also need the signature linked to that account.
I could add that signature by code if I can get the correct account and for some reason the signature is not added.
I tried using SendOnBehalfOfName which works as far as the correct email address is used, but still uses my default a/c.
As an aside, my Outlook session would always be open, so I will use GetObject and not CreateObject method, but would like not to have the warning dialogue in Outlook pop up all the time.
View 14 Replies
View Related
Feb 22, 2007
Hey all,
I'm designing a query that must calculate a future date based on a recorded date. Unfortunately it's not as simple as just using adddate (unless I was using Excel!). Here's my requirements:
Future Date = [StartDate] + 45 business days
I am able to take weekends into account, but haven't been able to figure out how to bring holidays into the equation. I have an existing holidays table that I am using for another query within the database (calculates # of business days between 2 existing dates).
Hope this makes sense... I've been staring at it all day so I may not be thinking clearly anymore.
Any help is greatly appreciated.
View 1 Replies
View Related
Nov 6, 2007
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT
What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
View 5 Replies
View Related
Feb 23, 2006
During a promotion we are running the customers are only allowed 3 entries into the system per day but the users are occasionally putting some customers in more than that. I could use some kind of function that limits the number of times the account number can be put in on a given date. (Or daily)
I have the following text boxes: ID, Customer first name, customer last name, account number, date, time.
Any ideas please?
View 1 Replies
View Related
Feb 23, 2006
During a promotion we are running the customers are only allowed 3 entries into the system per day but the users are occasionally putting some customers in more than that. I could use some kind of function that limits the number of times the account number can be put in on a given date. (Or daily)
I have the following text boxes: ID, Customer first name, customer last name, account number, date, time.
Any ideas please?
View 2 Replies
View Related
Oct 9, 2006
I have a table called tblAccounts and a form called frmEnter_new_accounts. In my frmEnter_New_Accounts form is a Text Box for Account #. I want to have it so that when the user enters an account # it checks the tblAccounts table for a duplicate account # and then displays a message box (or Pop-Up form) to tell the user that the Account Number entered already exists. I would also like that box to offer the user the option to either Close that form without saving or return to the form to reenter a different account #.
I know I would probably do this in the LostFocus event or BeforeUpdate.
Does anyone have any ideas or samle code to assist me in this adventure?
View 4 Replies
View Related
Mar 22, 2006
does anyone know the code for command prompt to create an administrator account over a network???
View 2 Replies
View Related
Nov 6, 2007
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT
What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
View 6 Replies
View Related
