In access 2003 i have an external Oracle table named EXT-COM_EXTRA_DATA_FIELDS
in this table I have the following fields: COM_DATA_KEY, COM_DATA_FIELD_NO, COM_DATA_VALUE
From this I have created a query named: LOOKUP-BARCODES-10 this looks at the above table by filtering the
the records on the field: COM_DATA_FIELD_NO where this =10.
Also I have created a second query named: LOOKUP-BARCODES-20 this looks at the above table by filtering the records on the
field: COM_DATA_FIELD_NO where this =20
The problem is i would like to combine the numbers in the COM_DATA_VALUE field for the same product (ie the same COM_DATA_KEY
record) on the same rows without any duplicate records as shown below:
As you can see from above some records only have data in one COM_DATA_VALUE (like 011C/x) where COM_DATA_FIELD_NO = 10 but no
data for the same product where the COM_DATA_VALUE = 20.
Im thinking maybe i can create a new join query that looks at the above two query’s and combines the values in the COM_DATA_VALUE for
the same product onto the same record row but im not sure how to go about this without creating duplicates & its been some time since ive done joined queries. Any help would be great?
ive attached a cut down version of the database with the table imported from oracle rather than being linked to oracle if this helps.
I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.
i.e.
I have a
Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation..... Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation..... Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation..... Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....
-A person can be within one of these tables more than once but with all the same information. -A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.
Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers) Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?
I have a product table that includes the name of the product, the quantity ordered, and the unit price. This is a summary table for the whole year showing all of the orders. The Primary Key is [ID] from the [tblOrders] table. I attached a screen shot of my query. I don't know if you can see it. I want to generate a report where each product is listed once, the unit price for that product is listed, the total number of that item that was ordered throughout the year, and finally, the total amount spent for each item during the year.
I have a table called Packages, in this table I have multiple fields with just short dates example of fields (ID), (Home_1), (Home_2), (Home_3) and (Home_4). What I was wondering is there a way to comine all of the Home fields into one for the purpose of counting the number of dates for all the records. Total number of dates of all the Home fields? I am using Access 2010
I have a table "MansComps" which includes two fields, the "Index" field which contains a number, e.g "5" and the "Tabletype" field which contains a letter e.g "T". i have created a query based on this table, which combines these two fields and displays the result to a third field which I call "Components".
field: Index 1, 2, 3,....
field: Tabletype T
field: "Component" has to be like this: T00001, T00002,...
I have managed to combine the fields in my query using the expression:
Component: [Tabletype] & " " & [Index]
but the results appear without the leading zeros, like T1, T2..I have set my Index content to be a number and as a primary key and the Tabletype as a text. I also tried to change the format of Index to "00000" which works for the Index field separately but not for the Component field.Should I do something in the format of the Component field?
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
I have been given the task of cleaning up an old db. One thing I'd like to do is combine 3 text fields into 1 new field. In the old db there was a form that included 3 memo text boxes, when the first one was full the user then started using the second and then the third. The relating Table fields are message, overflow message & overflow message extended.
I'd like to combine the fields into 1 and then design a new form to suit so that the memo text in the new db goes into 1 field only.
I have made a form to run reports on a database, however, I need to add a field where the end user can enter an account number and the report will find the relavent fields. The problem is that the account numbers are stored as four different fields within the database (add_char1, add_char5, add_char9, add_char13).
At the moment I am trying to use a combo box on the form to display all of the account numbers, but I have no idea how to do this, I can only get one field of account numbers (eg only entries from add_char1 displayed in the combo box). I have read several of the posts in these forums, but not really found a solution. If anyone knows of a way to display multiple fields as a single column (so that the combo box produces a list including add_char1 to add_char13), or have any other ideas then please let me know!
My table has three fields pickup_carrier delivery_carrier rail_carrier all the three fields share the same lookup source table.(TblCarrier) Is there a way i can combine (uniquely) all the three fields to a common combo box with one field to select?
I have two reports, "Quick Glance Report" and "Note Report". The Quick Glance Report comes from a query called (ACE/ARB Table Query) and the Note Report comes from a query called (notequery).
I want to be able to have a calculated field from the "Note Report" in the "Quick Glance Report". Am I able to show a calculated field from one report into another report and how would I do this (If I can that is)?
The lookup has a two column dropdown: FirstName and LastName. This is for the field, Sponsor, however I want to concatenate the First and Last name and have those stored in Sponsor. How do I do that?
Note: I just did it in the Lookup query.
Now a related problem is when I make a change to the table (e.g. Lookup), the change does not show in the form. Is there a way to "refresh" the form? My work around was to delete and re-add the field...I'm sure that is not the correct way to do it.
And when I added the field, the caption comes along with it bound to the field. Is there some magic keystroke to break the bond?
I have an Access DB built that links to an excel spreadsheet. The spreadsheet changes so I cannot import and have to link.
Now the source of my spreadsheets is producing two spreadsheets with identical fields but different data. I need to combine the data from these two spreadsheets. Can I link them both and combine them in a query? How?
I wanted to combine 2 fields and a text in one text field on the access form.I have a field called "SentDateTime" and a field "Comment" I wanted to create it like this: MSD Message sent on 24/12/2014..I tried this formula in a unbound text field and it did not work:
I've spent about an hour searching this site for email issues but most I found we how to send. I want to take existing data and make an email address. I have a FName field and an LName field. In my email field I want FName.LName@email.com. the @email.com will always be the ending. Also where (on gotfocus) is the best place to put this code so it autopopulates after the FName and LName are entered?
Hey guys- I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')- xxx-xx-xxx Where x is an integer.
Table 2 has split the 3 sections of the code out to 3 individual fields, like this- Field1 Field2 Field3 xxx xx xxx
I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow... Thanks!
I want to combine six different memo fields into one. I found this code and it works to combine two fields so I edited to add a third and it does not do anything.
Is there a way to combine four fields into one field in the same record? I am trying to build Equipment Code field by using the abbreviated values of Category, Item, Type and Dimension fields.
Example: if Category=Storage System (SS), Item=Wire Deck (WD), Type=Flared (F) Dim=42x52 are the user selected values the corresponding equipment code field would be: SS-WD-F-42x51 This is done in order for all users of the database to build a proper code in the correct format.
What I have so far: I have the four fields and the equipment code field setup in a form, the equipment code text box has a control source of: =[Category] & "-" & [Item] & "-" & [Type] But when I select values of the four fields and the equipment code is populated I try to save the record and I get an error stating that the Equipment code field needs to be filled out. Three fields (Category, Item, and Type) are look-ups from other tables and Dimension is typed in.
We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT". The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
I have a few input tables and I am trying to combine the fields from 2 tables into one query. How do I go about doing it without having the project amount (highlighted in red) being duplicated?
I have created a database which holds architectural drawings for my company. The drawings have a number (dwgNumber), a name (dwgTitle) and a revision (dwgRevision). Each number should only have one title but cannot be unique as they can have several revisions. I am looking for a way to allow the users of the database to only enter a drawing title for a drawing number once. This is because if more than one person is accessing the front end at a time, they may use the same drawing number for different drawings.
Is there any way I can stop the same permutation of three field values being duplicated in a table. eg a - b - c is ok a - b - d is ok b - c - e is ok a - e - c is ok a - b - c ......duplicate
I have created a database which holds architectural drawings for my company. The drawings have a number (dwgNumber), a name (dwgTitle) and a revision (dwgRevision). Each number should only have one title but cannot be unique as they can have several revisions. I am looking for a way to allow the users of the database to only enter a drawing title for a drawing number once. This is because if more than one person is accessing the front end at a time, they may use the same drawing number for different drawings.
For Example:
----THIS IS FINE Number: A001 Title: Drawing 1 Revision: P1 Number: A001 Title: Drawing 1 Revision: P2
----THIS IS NOT OK Number: A001 Title: Drawing 1 Revision: P1 Number: A001 Title: Drawing 2 Revision: C1
Basically, each number can only have one title assigned to it.
