I want to select unique country names from a recordset and now my code is
strSELECT = "select distinct country"
The problem is that ASIA and Asia are different, but it only selects one.How can I include both of them?
I am trying to get a combo box to only show unique values, but I can't get it to work properly - it still shows duplicates in the list.
The Row Source is set to
SELECT DISTINCT Areas.ID, Areas.Area FROM Areas ORDER BY Areas.Area;
And the combo is bound to column 2
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
I have a query which displays two fields: client ID and order type. I want to be able to display the client only once per order type. I tried to use GROUP BY order type but that gives me a missing expression message. I also thought of using UNIQUE as YES in qry properties but I'm not sure as to which row that property applies (all of them?). What is the best way to display a unique client in one row and order type in the other? So for example for order type "CASH" I wil then have a list of unique individualIDs.
thanks,
M
Hi,
I'm not sure where to post this, maybe VBA or Forms would be better, but I think it's fundamentally an index problem, so I'm posting here. I've searched both the net and here, but most of the questions are about preventing duplicate records.
I want to prevent duplicate records but save changes to existing records.
I have a form based on a table that has a 3-field-unique-index (but not the primary index, which is autonumber). I've written a function to check for existing records with the given combination of the three fields, as long as all have data (it bails if any are null). I'm calling the function from BeforeUpdate for each of the textbox controls. I was also calling it from the form's BeforeUpdate, but commented it out as it seemed redundant. I have other code there verifying that the user wants to keep the changes.
I'm trying to save the users from getting all the way to the end before discovering there's a problem. To test it, I added then deleted one character from one of the three relevant fields in an existing record, and bing, up pops my alert that I'm violating the index. Then I can't leave the field, I'm stuck there because I can't save the record.
I want it set up so that other fields in the record can be changed/updated AND so that a new record for the same person can't be entered (a dupe).
Oh, and I'm running into all kinds of problems with acCmdSaveRecord not being available. I feel like an idiot that I haven't been able to make sense of the posts about that. I can just comment it out and let Access save the record when it closes the form. But my bosses really want the users prompted about saving changes.
Any thoughts or suggestions or insights greatly appreciated. I really tried to search this out, so any tips for better searching are also appreciated.
Here's the code for the function:
Public Function CheckForClientDupes()
Dim response As Variant
Dim strFamID, strLName, strFName As String
Dim strFilter As String
If IsNull(Me!FamilyIDNo) Or IsNull(Me!strLastName) Or IsNull(Me!strFirstName) Then
Exit Function
End If
strFilter = "[strFamilyIDNo] = """ & Me!FamilyIDNo & """ And " & "[strLastName] = """ & Me!strLastName & """ And " & "[strFirstName] = """ & Me!strFirstName & """"
If DCount("*", "tblClients", strFilter) > 0 Then
response = MsgBox("There is already a client with this combination of FamilyID, Last and First names in this database. Would you like to Continue Anyway (Yes) or cancel data entry and Erase Your Changes (No)?", vbYesNo, "Duplicate Client Alert")
If response = vbYes Then
' DoCmd.RunCommand acCmdSaveRecord
Else
response = MsgBox("You have chosen to cancel your changes. Your changes will be erased.", vbOKCancel + vbQuestion, "Data Entry Cancelled")
If response = vbOK Then
Me.Undo
Else
Exit Function
End If
End If
End If
' Me.FamilyIDNo.SetFocus
End Function
Is it possible to create a combo box to list all record ID, when selected that record appears on the form?Ifso how would i go about this?
Thanks in advance?
Andrew
This is probably dead simple, but I am brain dead today.
I have two tables:
Requests with fields (ID, Cust, Amount, Ref, Date)
and
Actuals with fiels (ID, Cust, Amount, Ref, Date)
Now,
if Requests.Ref is null, then update Requests.Ref = Actuals.Ref and Requests.Date = Actuals.Date if and only if
there is only one record in Requests and only one record in Actuals where Requests.Cust = Actuals.Cust and Requests.Amount = Actuals.Amount.
Currently, I am just doing an inner join between the two tables, but if there are two requests with a given cust/Amount, but only one Actual, then both Requests will get the same Actual Ref and Date.
So how do I structure this SQL?
Thanks,
David
I have a table like so:
Code:
PatientID VisitDate Complaint TestPos
1 4/5/2003 Coughing 1
1 1/2/2007 Sneezing
1 5/1/2008 Unknown 1
2 2/1/1988 Unknown
2 4/2/1988 Unknown 1
I'd like to extract just one TestPos record (TestPos = 1) per Patient ID. And I always want to select the record with the earliest date. So the result would be:
Code:
PatientID VisitDate Complaint TestPos
1 4/5/2003 Coughing 1
2 4/2/1988 Unknown 1
I can do this but it requires 3 queries: a SELECT query where TestPos = 1, a GROUP BY PatientID query and MIN(VisitDate) to get the desired record per patient, and then another query that links the two so I can get the additional variables (e.g., Complaint).
Is there a way to do this without using 3 queries? Seems inefficient.
Hi I have a very simple table, with say 5 fields (all text). There is only 1 table. The first field is name, and I want to enforce its uniqness across all other table.name values. I am completely new to Access database and am not sure how to do this.
Any help would be greatly appreciated.
Thanks, Edin
I am using a software drawing program that intelligently link information from a drawing to a database and vice versa. I have many tables and queries that I have created over time that enable auto populating of tables, creation of tables and so on. All of which enable me with tabular documents for ordering purposes.
The problem I currently have is: - I want to create a table that looks at an existing table, analysis various fields for duplicate information and then adds one record to a new table. This is to enable me to create a spares list based on the entire list.
I have a full valve list with various items, some duplicates some not. I am required to produce a spares list that considers only one of each type.
I am trying to assign a unique two letter code to a set of record. From AA..AB..BA....all the way to ...ZZ, how do i go about doing this ?
View 14 Replies View RelatedExample 1:
2011-1
.......
2011-3893 etc.
Currently I have an Access form which produces a new unique number to identify each new record created. To do this I use the unique ID autonumber from a table to identify the new records. I would like to change from this simple number to the the above format per example 1. The four digits to the left of the hyphen would always be the current year and digits to the right of the hyphen would be the unique auto incrementing numbers such as from my table. I need the year to auto increment by 1 each September 30th (new business year) and I need the numbers to the right to auto reset to 1 to start uniquely identifying records again for the new incremented year. As each record is closed I need the number to be written as a single entity in the new format to my database.
Example 2: After September 30th.
2012-1
.......
2012-447 etc.
I have a database in Excel that contains farmers who can be identified by a Unique identifier: MZ-01-0001. The registration details for these farmers are not always up to date. Now when I try to append the data for the farmer with MZ-01-0001; with the primary key set on the Unique identifier, Access does not want to do it since there is already a record in the system. When I remove the primary key it doubles the record, which is also not what I want. So I am wondering what I need to do to append the record while keeping the primary key intact.
View 4 Replies View RelatedI have a MS Access 2000 database with 136 data tables in it. What I would like to do is execute a piece of VBA code which will list for me in another table, (for example: Field1: TABLE NAME Field2: FIELD NAME 1 Field2: FIELD NAME2 etc), starting left to right, how many fields would have to be combined in each table to represent a unique record.
For instance:
TYPE ID TEXT
1 1 "Cats"
1 2 "Dogs"
2 1 "Rabbits"
In this example a combination of the fields TYPE and ID give a unique record.
Any help most appreciated.
Dalien51
I have a table with data like this
Field_1,Field_2,Field_3
A,B,5
A,B,3
A,C,7
A,C,6
X,Y,4
X,Y,3
I need a report where I can only see
A,B,5
A,C,7
X,Y,4
That is Group by Field_1 and only show the records which has Field_2 with the max. value of Field_3
As mentioned before on a different topic I am building a database to sort through publication authors. I currently have the table sorted in a way that only the main author is listed for the 3000 records. Now I need a way to remove duplicate entries on a new table. Each entry is a different publication. Each record has a DOI code that is unique and an author which might be a duplicate. I want a new table listed by author once and a new field that list all of the DOI numbers for that author.
I know how to do a query to show only the unique names, but how to I also get the database to combine the records that share the same author.?
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
View 8 Replies View RelatedI am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:
It suggests that I create multiple primary keys in the table
When I do it, it comes back with an error: Index or primary key cannot contain a null value.
Need to generate a unique reference number each record wise while data entering by a form. How to do this by using a command button ? After entering all fields related to the record, when click on the command button, unique reference number for that record need to be generated and save with all other data of the records. in a later event, need to recall the record by using this reference number and also need to make relation ship with some other table by using the reference number.
View 6 Replies View RelatedI want the lookup to be performed with criteria based off of the same row that record being selected is on.
I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.
You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521| KNNRLABR
The lookup displays products available where the indexed products match the same location. Here is what the query returns:
| Location |Products Available|
| KNNRLABR| Product 21 |
| KNNRLABR| Product 9|
| KNNRLABR| Product 7 |
| KNNRLABR| Product 3 |
This is the affect that I want to achieve but, if I add another location like so:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521 | KNNRLABR |
| Store 2 | 3609061624 | VANCWA01|
The lookup returns all products available for all the records for each location entered rather than based off of the selected record.
| Location |Products Available|
| KNNRLABR | Product 21 |
| KNNRLABR | Product 9 |
| KNNRLABR | Product 7 |
| KNNRLABR | Product 3 |
| VANCWA 01 | Product 31 |
| VANCWA 01 | Product 8 |
| VANCWA 01 | Product 4 |
| VANCWA 01 | Product 3 |
| VANCWA 01 | Product 1 |
Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
P.S. Here is a copy of the SQL statement I'm using right now.
SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI
FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet]
WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
hi guys,
some background on my data:
i have a table that holds Electronic Gift Card details (those plastic store gift voucher cards). it holds all details of every transaction for every card, so the card number, activations, redemptions, dates, times, transaction values etc. are all recorded in my table.
i have attached a small extraction in a spreadsheet.
a single gift card can have multiple transactions against it throughout its lifespan. there are sometimes error transactions, and i need to write a query that finds these error transactions (they are all flagged with a "1" in the [reversal flag] field, so that bit is easy) but then also then the next transaction that occurs on that card. this subsequent transaction will not be flagged with a "1", but is a reversal of the error and will have an "802" flag in the [transaction type] field.
each transaction is date stamped hh:mm:ss, but i don't know how to write a query that will
1) find gift card number that has a reversal code of "1"
2) find next transaction made on that gift card based on transaction time
3) check that subsequent transaction type is "802"
4) select both transactions
5) repeat for entire table
i can't seem to get the desired result just using a straightforward query, and i don't know much (or any!) VBA in access.
does that make any sense at all???
cheers
bazzason
Hi There,
I have a form 'Players' which has a field called Contact. When adding a new record to the Players form, the user has to select an existing contact or add a new one (they cant just type in a name).
(There is a contacts table)
So, i want a button next to the contacts field.I want it to:
1. If there is already a contact selected on the form, the contacts form open at this contact, so can be edited.
2. If there is no contact selected, the button will take the user to the contacts form, but will display the first record in the contacts table. From here the user can select the contact they want.
This is what i have (code below). With this code, if there is a contact already entered, the contacts form loads with that contact select, which is correct. However, if no contact has been selected, the contacts form opens with a blank record; whereas i want it to open at first record!
Private Sub Command90_Click()
On Error GoTo Err_Command90_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmContacts"
If Me.Contact = " " Then
DoCmd.OpenForm stDocName
Else
stLinkCriteria = "[Name]=" & "'" & Me![Contact] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Exit_Command90_Click:
Exit Sub
Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click
End Sub
I have a form with a sub form
what I want to be able to do is use the main form and search on postcode surname and display the records that match in the subform and then allow the user to select which record to add additional data to
ahhhhhhhhhhhhhhhhhhhhh
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
I have designed an UserForm in Excel, the aim is to copy selected data from Access (tblIndex) to Excel. The form of the Access database is as following:
Country Type Date Index
....... ...... ...... ......
The UserForm contains a ListBox "ListCountry" and a ComboBox "ListType" to select country and type, and a ListBox named "ListCT" with two columns for selected country and type, and two TextBox (TextDateSta and TextDateEnd) for the user to Type in start date and end date. What I want to do is to copy the records of the selected Country, Type, and Index from the start date to end date. I suppose I should use SQL like:
strSELECT = "SELECT tblIndex.*"
strFROM = "SELECT tblIndex"
strWHERE, I do not know how to define the condition here. The clause below is what I mean, but it looks weird and it does not work.
Num = LiqForm.ListCT.ListCount
For i = 0 To Num - 1
strWHERE = "WHERE tblIndex.Type = LiqForm.ListCT.List(i,0) AND _
tblIndex.Country = LiqForm.ListCT.List(i,1) AND _
tblIndex.Date > LiqForm.TextBoxSta.Text AND tblIndex.Date < LiqForm.TextBoxEnd.Text"
Next i
strSQL = strSELECT & strFROM & strWHERE
Could anyone give me some clue?
many thanks