I would like to compare 2 tables by looking only at the names of the fields, data types and their properties (e.g. required yes/no). I am searching for a method to export this information to another table and then make comparison. Maybe sth. similar to “documenter” or another tool. Some ideas would very much appreciated. Thanks Bartek.
View RepliesI have a table with a field with names set to text data type and i want to change it to number data type but when i do it in design view the data get lost. I want to know if there is a way to convert the data in the field as number type and keep the data in the field.
View 7 Replies View RelatedI inherited a database that has two tables that are structured identical; one called tblcurrentdata and the other called tblpriordata. The user wants a report or export query that only shows records that has differences between prior weeks data and the current weeks data. The tables have 12 fields of data in a record per quote number. The user wants a query or report to identify the QuoteNum and any changes between the prior and current records. The table structures are as follows:
Code:
tblCurrentData/tblPriorData:
QuoteNum
CoName
State
ZipcodeNo
priorityColor
[code]...
QuoteNum 12345 field 5 (prioritycolor) changed from red in the prior week to green in the current week and field 7(POC) changed from Scott in the prior week to Jonson in the current week.QuoteNum 23451 did not have any changes therefore does not need to be listed in the query/report Unmatched query doesnt work because it does not compare multiple fields. I tried to structure a union query and use <> in each field but got too tedious and didnt give the expected results.
Been trying to crack this one for a while hoping someone on here might be able to help me. ;)
I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.
This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...
Any help would be greatly appreciated.
Graeme
Hi
I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below
Table 1
xyz - A1 - Do not use
ABC - A4 - Use
Table 2
123 - A1
The result of the query should show me
A4 (as this does not appear in table 2
I am not too familiar with Null values as there will be many null values that i want to ignore.
Any help will be appreciated.
Okay, I apologize, I'm not extremely Access knowledgeable so bear with me.
I have an Access database I created which I thought was working okay, then I realized it was missing one vendor because we haven't started writing checks to that vendor yet and probably won't for a while.
I am using a make-table-query to create a table that combines data from two linked tables. The two linked tables have a matching field (VendorID) one linked table is "History" and the other table is "Master" (these tables are SQL data from MS Great Plains if you need to know that).
Now my problem ... not every VendorID in Master table is present in History table.
I first created the join statement to say use all records in History and only those records in Master that match. This works without any error statements, however, I discovered there is a significant field of detail that is being omitted because a VendorID is in Master, but since no checks or invoices have been created for that VendorID it does not exist in the History table yet.
I decided to change the join properties to say "include all records in Master and only those records in History that agree". When I try to run the query I receive the error message "data type mismatch". I feel certain it is because several of the fields have no information to draw from on the vendors that don't exist in the History table.
What I need the query to do is go ahead and use the information from the Master table and when there is no matching VendorID in the History table use a zero for those fields.
I used to program and know a little C+. I understand "if" statements and feel like a sql if statement could work around this (for example, if History.VendorID does not exist zero, else History.[fieldname], but I don't know how to go about doing it. Any help for a novice would be much appreciated. Thanks in advance
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:
Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:
1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.
2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" .
3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn"
4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete"
5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .
I have a field in a table that is comprised of mostly numerical data but some records are text.
I want to convert this field to numerical only and make a new field to put the textual data in.
However converting the field will delete the textual data. What is the easiest way to convert the field but save the textual data AND append the textual data to the SAME record that they were in originally in the new field?
I would like to work with a field "record number" like: "ABC01-01-2013A1".Standard data types do not allow for this possibility.I would also like the date updated itself automatically and the number incremented A1, A2 until A9 then B1
View 6 Replies View RelatedIn my database I have the following:
1)Projects table:
- Project ID - Number field (Primary Key)
- Project Name
- Location
- (other fields)
2)Working details: (this table has a form based on it and it's used to enter data by users)
- Project ID (this field has a lookup for the same field in the first table)
- Project Name (this field has a lookup for the same field in the first table)
- (other fields)
My problem is : in the second table, Project Name field has "Number" data type because the relationship between the table is on the Project ID which is number field. I want the Project Name to be "text" data type in the second table.
How can i do that??
I have a database consisting of three linked tables, with ID as unique identifier (primary key).
I would have expected the data type for my unique identifier, especially in the main table, to be Autonumber. However, I discovered with some surprise that the primary key (ID) in
Table 1 (Main table) is data type: number, field size: double
whereas in table 2 and table 3 the ID is data type: autonumber (field size: long integer)
It must have happend "SOMEHOW" when I divided the original single table.
Is this as it should be? Or could it lead to trouble later on?
If not as should be, is there anything I can do right now to rectify the situation with risking upsetting the relationships and all?
What precautions should I take, apart from taking a backup?
Thanks for your help.
Adrian
I am creating a table that is a master list of all of my company's product. Each "customer" that we have will always be ordering the same items, but not all of the items that we have available. I need a way to go through the master list and click a yes or no and have that item added to the "customer's list of items on a new table.
I need to create a sublist for each "customer" like individual shopping cats for each customer. These individual lists need to link back to the master list in case of product changes, description changes, and cost changes.
I would like to create a form where the end user can type in a product number, description, or manufacturer number and have that item added to the "customer's" list.
I have a date/time field. I would like to:
Enter time this way and have it show in the form as:
Enter 5 - show 5 PM
Enter 515 - show 5:15 PM
etc.
I would like it to default to PM and not have to select or enter the PM. How do I enter this format in the table?
I created several tables that contain the look-up data I want to post to database which I will use as the repository for a SharePoint form.Users visiting SharePoint site will enter the data to be kept in an Access database so we can create reports (not seen by the user)
Problem: When I created the database I linked fields to tables to create the look-up lists using the wizard.
When I saved the database, the first field that I linked returned a value of "number" instead of whatever the default value should be for a look-up text field.
I have two tables(see below). I want to set up a query, link these 2 tables together. I set a one-to-one relationship between Client ID in two table. But got error message :"Type mismatch in expression".
I tried to change Client ID data type from "Text" to "Number", then Access deleted some data under Client ID in Order table.How can I make this work, but not having to re-type in all data?
Client Table:
Client ID(Autonumber)
Client Name (Text)
Client Address (Text)
Order Table:
Order ID(Autonumber)
Client ID(Short Text)
Unit Order(Number)
Unit Price
So I have decided that I want my ID's to be AutoNumbers, but at the moment they are currently set as Numbers. I have already inserted data, to test, which has been deleted, however I am now unable to change the ID field back to AutoNumber.
How can I duplicate the tables so that this field can be changed again?
I have like 10 tables with heaps of feild, so remaking them will take long, but I know there is a way using queries, I am just not sure how...
I have a table with about 300,000 records. About ten fairly small fields per record. I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.
I have never seen this message before. I have about 64 Gig of free disk space. What can I do?
I have encountered an issue when I was inserting a string (with newlines about 176 characters) into access table. This field in access has the data type TEXT and it was truncated after the insertion. It is strange because I have three other fields with the same format and no truncation at all. May I know what could be the reason and how/what is the recommend data type for storing long text?
View 3 Replies View RelatedWhen I run the analyzer on all object types it recommends to change the data type for field "zip" (zip code) to "long integer to:
"benefit that table and potentially other objects in my database"
The field type is currently set to text, And I have the same setting for the same field in a separate table, yet it does not come up with a recommendation for that table.
Additionally, I don't seem to have the option "long integer" for the field data type???
jeds - using Access 2010
From a table I want a text field which has a path to a file to be copied automatically into a another field of the same table with a hyperlink text type...
View 5 Replies View RelatedHi, I would like to change the properties of either the column heads in a combo-box or the column names for tables. I don't think there's any way to adjust the column head properties, and I'm doubtful there's a way to change the column caption properties.
Some of my column titles are long, and I'd like to be able to word wrap them essentially. Anyone know if this is possible? Thanks.
Situation:
Have 2 Tables that contain the same information, one table was used during registration and had the Data changed
Question:
How do I pull a comparison query to match the registration table to the Main table and pull only the data that is different so I can update the main table with the new Information? :confused:
hello, i have checked out the forum for pevious Q's to this problem and found the below.
http://www.access-programmers.co.uk/forums/showthread.php?t=80692&highlight=compare+tables
only problem is thats its writen at a bit too technicle a level from where i am. i've done some investigation into the problem though.
found out i need to use the docmd.transferdatabase function to import the tables i need to compare into my current db.
my question is this. how does it import them, does it create new tables within the current db or is it put somewhere in temp memory for use only while db is open, not sure how it would work. ideally i would like it to import tables, do the table comparison then create a report of the differences or make a new table showing the differences, then it reverts back to how it was before i imported it. i.e. he current databse is unchanged except for maybe a new report or new table (the results).
thank you for any advice.
Neil
Hi,
I need to compare 2 tables with Same Structure in a Database and pull out records that are not matching in both the tables. As anyone done similar task in Access 2002, if so please let me know how this can be accomplised.
I have two tables that have the exact same structure. They both started with the same data, but each were changed separately. (Two copies of the same DB, editted separately). I need to review the data in each record and determine what has changed or if records have been added. Is there an easy way for me to do this? (Note: Each record does have a primary key)
View 1 Replies View Related