I'm trying to clean up a database and make sure that I don't make an error in future entrys.
Is it possible to make a "key" that highlights the rows with matching keys. The thing is that a the "key" is Switchname + Switchport, data placed in 2 different columns in the same row.
How do I do this? Is it possible?
I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.
OTHCPT2OTHCPT3OTHCPT4OTHCPT5OTHCPT6OTHCPT7
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 #NULL!#NULL!
2318095978C176788307J0131 J2270
For instance if 23180 and J2270 are in the same record I want to identify them for all of the records.
:confused: I have a database of historical baseball data.
It contains 5 tables:
Players: (Full Name, Last Name, First Name, Primary position)
Year: (Single field, w/autonumber)
Data: (many fields of specific data)
Position: (C thru DH)
Team: (1-30)
Each table has a unique key, (auto-generated)
The database contains 4 years worth of specific data, (2003-2006)
The main data table has lookups to the other tables built in to the current table
First run through shows the DB to operating pretty efficiently, (although more time will tell more, NOTE: I am a level 2 rookie).
PROBLEM:
I now need to import/update/append the tables with 2007 projected data, and then at the end of this year coming up, I will need to do the same with actual data, and so on.......
The data that is coming into the DB does not contain MATCHING PRIMARY KEY:
The Team Name, (with age as an added field)
Player Names, (all full,last,first,primary position)
The data, (all fields match name and data type)
Year, (2007, + I will be adding a field that denotes actual or projected).
HOW do I update the tables and assign the primary keys to all the table's matching names while doing so and assign the correct/matching Pirmary Key to the updated records.
HOW do I verify that the update was correct without scrolling through 2500 data rows and 937 players?
Any and all help would be greatly appreciated
Thanks
Doug
Create form to search multiple fields in table
Return records that match search
Open the record that you want in Form View for editing
I have a main form users enter info into then submit, which adds it to the table being displayed in the subform...
One of the options is a "Urgent" tick box, if they tick this box, once it has submitted to the table, i need that row to highlight red...
Hello All,
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1
Any help would be greatly appreciated.
Hi guys
I just built a tracking database (I can't post it because it contains PHI, and I do hope I don't have to) and it's been giving this issue starting today.
When I am in a field the cursor automatically goes to the end and highlights everything between the end and where I click. So if I mistype the third character I need to delete everything from the end to the third character to edit it.
It just started doing this when I loaded it up. Does anyone have any idea?
Thanks
So i have two fields I need to tie together for conditional formatting
If the "status" field says "RETURN" and its over "30" days then I need to highlight it RED
If the "status" field says "AVAILABLE" and its over "90" days then I need to highlight it RED
Is this possible?
I wish to highlight some fields on a form if their value differs from the previous record.
I'm OK with the code to determine this, but unsure as to where to put the code to set the fields initially?
I have two strings txtPrevPayment_Method and txtPrevProduct.
Where can I set them 'once' to be the same as the first record loaded in the form. Then in the Current event I check if they have changed and set font colour accordingly.
I made a database that was working fine. It was only after I had finished the database, and data was being entered, that I was informed that the field I designated as the primary key would not work. Origanally, I used 'OffCit' as the single primary key. Now they tell me that one 'OffCit' can be entered several times for different reasons. To solve this, I thought about using autonumbers but have been told, and seen, that this may not be a good idea. So, I am trying to use multiple primary keys. So far I have had no luck. I have attached a screen shot of the tables and relationships. I have 'OffCit', 'AreaofLaw', and 'EffectiveDate' as the primary keys. The same 'OffCit' and 'AreaofLaw' will be used several times in different records. That is the reason for the third primary key. I am wondering if I have set this up right or if there is something I overlooked. Perhaps someone has an idea that may work better. Thanks for any input.
View 5 Replies View RelatedI have the following table:
YieldID |ProductID | Year | Month| Yield
I want to make sure that it is not possible to insert the same date (so, year and month together) twice for each productID. But at the other hand, it should be possible to insert the same date but then for different productIDs. How can I make sure that Access does this? Can I use (multiple) primary keys to enforce this?
Thank you,
Stacey
Hi
I have tried searching the forums, but couldn't locate any relevant information. I have an assignment at the moment and am meant to have 8 tables in my data base, two of which are 'User' and 'Hardware' which have the following fields:
USER:
UserCode (Primary Key)
UserFirstName
UserLastName
DepartmentCode
HARDWARE:
HardwareCode (Primary Key)
HardwareName
VendorCode
PurchaseDate
Now I am meant to have another table called 'Hardware Config' consisting of the Primary Key fields from the two tables above, and they are both meant to be Primary Keys in this table.
I have been able to make the 'Hardware Config' table, but I don't know how to make more than one Primary Key... any ideas?
Thanks in advance - dragonfly :o
I have a table that has the following fields: Symbol, DeliveryMonth, DDate, Close. This table has information for a large number of different commodities. I am hoping to run a query against the table and find breakouts. I am working on the actual query but my question is this: If I have only one table do I need to have a primary key? There will be no relationships established so what is the need for a primary key unless you plan to link tables together? If it is always a good idea to have a primary key then I will have to have the Symbol, Delievery Month and the DDate together be the key since without all three there would be duplicates. Each symbol has many different deliveryMonths, each DeliveryMonth has many symbols, and each date has many Symbols. SO it takes all three together to get a unique value. Will having a primary key in this scenario help me with writing queries? I appreciate all of your wisdom in explaining how this table should be organized. Thanks:)
View 1 Replies View RelatedLooking at a database that someone wlse has created I see that numerous tables have mulitple primary keys. How is this possible? If i try and allocate a primary key it will only let me do it for one field.
I think what I actually want to do is create composite keys but if I set the key then put it to duplicates allowed it says that the primary key is not allowed. Am I mixing up indexes and keys???
How do you assign a composite key
I have a table where each record is unique if at least one of three variables is different. The three variables in my case are each integers and are: YearRouteSegment.
Now each of these individual variables allows (and has) duplicates, but if the thing is working right, no pair of records in this table should have all three of these variables with identical values. Is this table a candidate for using multiple primary keys?
I can create multiple primary keys for a table MANUALLY (by highlighting several field then pressing the key icon)
BUT I cant recreate it by code.
HELP.
Set NewFld = tdfnew.CreateField("TaskID", dbLong)
Set idxNew = tdfnew.CreateIndex("TaskIDIndex")
idxNew.Fields.Append idxNew.CreateField("TaskID")
idxNew.Primary = True
tdfnew.Indexes.Append idxNew
tdfnew.Fields.Append NewFld
tdfnew.Fields.Refresh
Set NewFld = tdfnew.CreateField("SubzoneID", dbLong)
Set idxNew = tdfnew.CreateIndex("SubzoneIDAIndex")
idxNew.Fields.Append idxNew.CreateField("SubzoneID")
idxNew.Primary = True
tdfnew.Indexes.Append idxNew
tdfnew.Fields.Append NewFld
tdfnew.Fields.Refresh
I am getting an error, there is already a primary key.
Hi All,
Wondering if you can help I have a table called "example" which has field "a" "b" "c" "d"...
I then create another table called "importtable" with field "a" (imported in from Excel)...
I would then like to create a query which matches any and shows all the records which I imported in from Excel to my current table called "example" I looked at joining via "relationships" using a Select Query but it doesn't quite show what I'm after...
Should add I would like Field "a" in both tables to show but only if the number exists in the "importtable" if not then don't show..
Any tips :confused:
In a database I have two tables, one is linked to an excel sheet (our customers order) and the other is created via a "make-table query" from our business system.
In both tables I have the customers part numbers and neither contain a primary key.
What I need to do is compare the part numbers in both tables to find if a part number is present in the order but not in our business system.
I just cannot figure out how to do this.
Any ideas are greatly appreciated
/twallstr
Right now, I have 4 related tables. There's a table with companies, one with people involved with companies, a table linking the two by having foreign keys of the company number and people names, and a table that indicates directors and their alternates.
Since there's a one-to-many relationship for companies/people to company-peopleID (A person can run multiple companies, a company has multiple directors, it's easier this way), a person's name can appear multiple times, as can a company, within that table.
In a company, a director may or may not have 1 and only 1 alternate director to him/herself. So, I thought the easiest way was to put an autonumber in the Company-personID table and have a table (alternates) that had two fields, "alternate" and "director", both using that autonumber to link them. However, it appears as though I can't link the same primary key twice to two foreign keys of the same table.
[URL]
I need some help creating a simple datebase to store my documents and other knowledge objects. There are two types of
knowledge objects I need to store: Documents and Questions/Answers. For this reason I have created three tables: Index,
Question, and Document.
The problems I am encountering is that the ID field in the Index Table is the Primary and relates to the ID field in the
other two tables. So basically when I am entering information it can either be a question or document. I want to create a
for so that when its a question that I am entering it records the information in the question table and when its a document
I am entering it records the information in the document table.
I'm a beginner at using access and don't know VB or SQL. Any help would be appreciated.
Table Structure
Index Table
ID (Primary Key)
Link
Type
Source
Reference
Question Table
ID (Foreign Key)
Question
Answer
Document Table
ID (Foreign Key)
Title
Summary
Notes
I'm developing a simple sporting records db. I have a 'Competitor' table listing competitors as follows: CompetitorPK, Name, Division, Club. I have a 'Contest' table listing contests as follows: ContestPK, Competitor1_FK, Competitor2_FK, Winner_FK, Score etc. My question is have I modelled tables correctly i.e. CompetitorPK will appear in three columns of Contest table. How do I define this relationship? What alternative is there to what I have done.I intend to use forms to populate both tables (independantly obviously).
View 4 Replies View RelatedHello,
I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).
Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).
If that makes any sense at all, I will be surprised...thank you!
For my query I have 3 tables.
TEAMS. This contains TeamID / TeamName and TeamManagerName.
TeamAPPLinks. This is a many to many relationship and contains TeamAppID / AppID and TeamID (TeamID links to Teams.TeamID and AppID links to Applications/AppIS)
Applications. This contains AppID / AppName / Pass/Fail.
So teams can have many applications and applications can have many teams.
I only want to show teams that have all applications that have the Pass/Fail column as PASS. I have managed to do this for FAIL runing a query in a query but obviously its different for PASS as all associated applications have to be a PASS, not just one FAIL for a FAIL.
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click()
If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then
MsgBox "You welcome"
Else
MsgBox "Wrong username or password"
End If
End Sub