I'm trying to execute a query that will delete values in a certain column, but I'm getting no positive result.
Quote:
--- Table1 ---
Original:
col1 col2
-----------
text1 Stext1
[Code]...
In access I queried my results and now I have to do this last step. I basically have an ID column and a tax fee column. I am trying to remove the duplicated ID numbers but if they are duplicated I need it to add up the tax fee column so I can have one ID with all the tax fees added up together instead of several different ones.
I tried doing the equivalent to a pivot table (works perfectly in excel) but when I tried doing it in access, it did not work.
Hello! I'm in the process of creating a report, but am running into a problem. I have a number of clients that include fields such as ID, last name, first name, address, etc. and then their account numbers. Each client has a number of different accounts. The problem is that each client's last name, etc is listed again for each different account they have, so the query results looks like this:
ID Last Name Account #
1 Smith 123-56
1 Smith 4458-66
2 Jones 523-63
2 Jones 4525
2 Jones 5236
3 Roberts 5225-56
4 Johnson 523-55
I want the names and client contact info to appear only once in the report and all of the account #s to show also. Any suggestions would be GREATLY appreciated!
I'm trying to execute a query which is looking at data from 2 different tables and most of the data fields in the tables are the same. For instance table 1 has Dept, Res, Period_date, & Available Hrs and table 2 has Dept, Res, Period_date, Project & Forecast Hrs. The results I'm looking for are as follows:
Dept Res Period_date Project Forecast Hrs Available Hrs
11 A 11-02-07 123 XXXXX
11 A 11-02-07 456 XXXXX
11 A 11-02-07 789 XXXXX
11 A 11-02-07 012 XXXXX
11 A 11-02-07 XXXXX
But the "Available Hours" are duplicated in the preceeding 4 rows.
Thanks for any advice on this.
How to combine two tables in one table such that values are neither duplicated nor missed?i wanted to join Table5 and table6 such that the values of Field1 are all populated in the resulted table but neither duplicated nor overlapped!
View 4 Replies View RelatedI am using Access 2010 32-bit on a Windows 7 64-bit platform.I have a database that I have imported existing data into. For the most part, I have eliminated duplicate entries in my Item Number field.
Obviously I have missed at least one (or possibly more) duplicates. When I try to create a NO DUPLICATES index, I am informed that there are duplicate entries.I am looking for a quick way to generate a list of values that are duplicated so I can address those and correct them.Item number field is a number field of type DOUBLE with 2 decimal places.
I have a list of employees and sort criteria. for example
empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F
The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0
E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0
I can get it to return:
empID......Count
1234...........2
1236...........3
using Count and the criteria Where Criteria="T" but not returning zeros.
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
How do I delete duplicated records without having to make a new table? I would like the records deleted from the original table.
View 13 Replies View RelatedI have two tables (Table 1, Table 2). Table 1 is blank and has the fields "Field1, Field2, and Field3." Table 2 has data and 10 different fields ("Field1, Field2", etc). I want to copy all data from the matching fields in Table 2 to the corresponding Table 1. I've tried an Update Query in design view. Did not join any records, and just a simple:
Field: Field 1
Table: Table 1
Update To: [Table2]![Field5]
This returns 0 records to update. I thought about a Make Table to override the entire Table, but that isn't working either.
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
hello,
I have two columns Column A and Column B. Both column has either a numeric values or blank.
I am trying to sum both columns Column A and Column B, but unable to get values in Sum Column C where either Column A or Column B has blank values.
Can anyone suggest what I should do?
([ColumnA]+[ColumnB])
I have two issues with the report generation from a query:
(i) Sometimes blank values appear in the report in the serial number column generated dynamically (setting the control source property =1 and running sum = overall), but when clicked inside the particular box, the correct serial number appears
(ii) When grouped on a field, the particular field value appears sometimes more than once in the report inside the same group.
How to get rid of this?
Hi,
Each client has either an entry with a correspondence number or a notes number. So, the table would look somewhat like this:
Client: 333, Correspondence: 1, Notes: Blank
Client: 333, Correspondence: 2, Notes: Blank
Client: 333, Correspondence: Blank, Notes: 3
Client: 333, Correspondence: Blank, Notes: 4
How would I make a report that will make it look like this
Client: 333, Correspondence: 1, Notes: 3
Client: 333, Correspondence: 2, Notes: 4
Thanks! Hope that was clear.
G
I have one combo box bounded with two fields, Id and Description through a query.
I entered a new id in bonded table but for some reason I don't want to enter description now.
And if I delete the entered id then I will loss that unique id which is using for description, which I don't want.Now the problem is one blank space showing in my combo box.
So what criteria I should use in query criteria to avoid blank spaces?
After creating several combo boxes in my new forms, I noticed that there were blank values being displayed. Is there anyway of creating of creating a listing of unique values and not displaying any blank values for the user to select from?
View 1 Replies View RelatedI've got a database with a field PKGNum, that was typically a three digit number, that used to be the primary tracking number for each project. You could have the same package number for multiple customers. About two years ago they began to use what they now call a PMIS number which is a unique number assigned for each project. So I have a seperate field in my database to track that.
I use a query to select information from my database to generate labels for my files. What I want to be able to do is have the system understand that if the field PMIS is blank, for a given record, to give me the PKGNum instead, so that the correct color coded value will print out on the labels. What I've been doing is having folks doing the data entry put the package number into the PMIS field if it's an old file, essentially repeating the value, so that I can pull the report using just the PMIS field for the color coded.
However, I would prefer to not have them have to enter duplicate information. Is there a way to tell the system when it's running the query, if PMIS is blank temporarily fill in the field with the value from PKGNum?
Any and all assistance is greatly appreciated.
I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.Now I want to realize the following options:
1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015
2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.
How can I check if a table contains blank fields/values.
If there are blank fields I want to replace them with 0.
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code:
SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact
FROM tbl_Contacts
WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company]))
ORDER BY tbl_Contacts.ContactForename;
Why some contacts work and some don't ??
I am a bit of a novice when it comes to Access, but have managed to create a form with a subform embedded and various filters to show different data within the subform, including a date range filter. The code I have used for these filters is as follows:
Private Sub Command40_Click()
Dim strCriteria As String
strCriteria = createCriteria("[Introductions].Town", "List78")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Ownership", "List52")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Company", "List54")
[Code] ....
This all works fine, but I'm wondering what I need to add to this code to make it so that if the date boxes are left blank, records from all dates are displayed. At the moment I have to enter dates in order for it to work properly.
Ok, folks. I'm confused.
I have a database I've created called 'EngLog'. This database is located on the server and everyone in my department accesses it to add their latest entries.
However, every now and then, for some unknown reason, a second database appears, titled 'EngLog1'. I don't know what creates this second database.
Any ideas?
This is not a FE,BE database, and also, i'm utilizing a .dll file called MouseHook.dll which I have previously heard about in this forum.
Thanks to all in advance. This problem poses a bit of a threat in that some folks here might accidentally be updating the wrong database.
My table doesn't show duplicate records but when I run a query off theat table every single record has a duplicate record. What did I do wrong or how can I fix this?
View 1 Replies View RelatedHi, I have a table contains 5 fields are shown as below, how I can write a query to find out the duplicate of the records? I have created a couple of queries by using Find Duplicates Query Wizard to do it. But the results are not accurate enough.
Field 1: ID
Field 2: Trading Name
Field 3: Location No
Field 4: Location Street
Field 5: Location Suburb
IDTrading Name Location NoLocation StreetLocation Suburb
41350Arabica Café Grey St
263Arabica Cafe` 1GREY STREET
41294Arashi Japanese Resturant41Courtenay PL
150Arashi Kushi Yaki Bar41COURTENARY PLACE
From the data sample, I could like to find out the duplicated records. One of the queries is shown as below. But they cannot pick up these records. Any better way to find the duplicated records? Please help.
SELECT DISTINCTROW [C&B-will].[trading name], [C&B-will].[location street], [C&B-will].Field1, [C&B-will].[location no]
FROM [C&B-will]
WHERE ((([C&B-will].[trading name]) In (SELECT [trading name] FROM [C&B-will] As Tmp GROUP BY [trading name],[location street] HAVING Count(*)>1 And [location street] = [C&B-will].[location street])))
ORDER BY [C&B-will].[trading name], [C&B-will].[location street];
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
Hello,
I want to make a query that retrieves me all the duplicated records from a table. It´s possible?
Thankx