Hi,
I want to have an index such that the combination of "line no" and "calving date" are unique.
I have tried:
CREATE UNIQUE INDEX index1
ON [main table] ([Line no], [Calving Date])
;
And
CREATE INDEX index1
ON [main table] ([Line no], [Calving Date])
WITH PRIMARY;
but I get a unique index on Line no
Any suggestions?
Regards
lactaman
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
hi
when creating indexes for databases what needs to be indexed? is there an article online that explains it??
i
i do a sum on a field..... if i put an index on this field
does query will be faster?
thank
I created a help file for a using a db I created, though I am thinking it would be nice to have it "look more professional" something similar to the Contexts and Index help option found in IE.
I am think a form would be the easiest to do with a subform for each element in the contents, so when the user clicks on an element on left form (which is the contents) the detail of that page appears in the right form the subform (perhaps just using the visible property)
There are tab options for a form so I can use that to make the contents, Index, search, favorites
some questions come to mind though...
0) how would I create collapsible menu (like a tree menu) see the contexts and Index help option found in IE.
1) how to create a search field to look for anything in the db except the code (i.e. part of a record, a description of the record, etc.) ?
2) how would I create a favorites section (a place to save help topics viewed) ?
3) how do i create a navigation system (back and forward through the subforms) - it seems I would need to track which forms are displayed and when then use that to navigate back and forward ?
4) how create various options such as stop or refresh search queries, and perhaps turning on/off select highlight
Finally is there something similar to this already out there that I can just customize various options off of, perhaps even a tutorial.
thanks for any direction you might be able to provide.
I have ID field data type is auto number
I need to initilize it to zero after deleting all the records.....
tell me a way to do this
I have 2 fealds ( Id and date ) they are primery keys in the table , I want to prevent duplacte id to the same date . ( MSGBOX after update the date )
View 7 Replies View RelatedWhen I load a spreadsheet I can't add an index because it is a linked table, so I want to save it in MDB format so that I can add an index and then export it to SQL. However, when I save the file as an MDB file the data is all still linked as a spreadsheet and I still can't add an index. (I would add the index at the last step in SQL, but Pervasive SQL is very temperamental.)
Basically I just want to export an Excel spreadsheet into SQL and add an index.
Can anyone tell me why access puts the primary key field in the index list twice for the same table? For example i have a primary key called CustID, when i go into design view and click on the index button on the toolbar, i see it listed twice, once as primary key and then again as its own field?!
I dont see the reason for this but often times with many of my databases i see this happen to many of my tables. :confused:
I have a beginner Question....How can I set an unique Index on a field in a table if all the four fields are using duplicate values?
View 3 Replies View RelatedHave the situation where a database backend is on a number of sites and if changes have to be made to the design structure of the backend database I would like to run a VB generated report to check that all indexes are correct. While this is not an issue with single field indexes, I have issues with multi field indexes.
Has any one got some ideas on reporting on this?
Thanks
Don’t Understand why , tab index change’s
All 55 fields are in the same order as the tables.
The field is custname the frist fiels.
Go to my Customer form
Thery are 55 fields,
The frist field is ( custname )
I go to the properties
Then I go to tab index
And I type in 1
Then I exit.
Save form
Then I open the form and the cursor is not on the
frist field ( custname )
I go back to the properties
And back to tab index
Now it is 44
I have don e this procedure several times.
And the tab index changes sometimes it is 44 , sometimes it is 49
And other times it is 36.
How can I keep the fields in the same order that I set up the tables..???
What can i do about this.
John527
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
Hi,
I don't know why, but my Microsoft Access 2000 Help's Answer Wizard and Index Tabs are completely empty! These pages are completely blank, with no dialog boxes or anything. How can I fix this?
Thanks,
Jean-Guy
Hello, I keep getting this error when opening up a database. What can I check to find and fix the problem? TIA
View 3 Replies View RelatedCan't figure out why I can't create a one to many relationship between two tables:
TableA uses a composite key as its primery key (field1, field2).
The table has a unique index comprised of these keys. The index even has a name. The table also doesn't contain any duplicate information, so the fields comprising my Primary Key are unique. The table I'm joining tableA is unpopulated at this time.
TableA
FIELD1 FIELD2
99999 ABCDEFG
99999 HIJKLMNO
Any help will be greatly appreciated.
I am trying to set a multiple field index on a table. I would like to use 3 fields. One is a text field, the other two are date fields. The text field and one date field will always have a value. The other date field can either have a value or no value. The index will work if I only use the text field and date field that always have values. If I try to add the third field, it will not find a duplicate record. Can I not include a field that may have a null value?
View 5 Replies View RelatedHi,
To make a long and tedious story short:
Does anyone know why Access (2003) creates an index called PrimaryKey and an index called User_ID when I'm in the table design view and ask it to make the User_ID field the primary key?
After years of database development I've only just noticed this. Not normally not a problem but for this:
Use ADOX to find the field name of the primay key for a table. I took the code right out of a Access 2000 developers handbook. It creates a collection of the table indexes and steps through them one at a time checking if the PrimaryKey property is true. It hits the index called PrimaryKey and says 'yes it's true, this is the PK', I then go on to use the index.name property in an SQL Select to create an ADO recordset. The only thing is that the index called PrimaryKey is not a field name and the field name User_ID is not a PK :eek:
I go into the indexes list in table design and delete and re-create the indexes and PKs as I want and the code works fine.
So, again, why does Access do this? I'm so confused! :confused:
Hi all,
I have a problem with indexes in tables. I made an inventory DB where we can store items on several locations. Their are several difference items, each kind of item has his own table because they all have different fields that the user has to fill in.
The only thing they have in common is the place, they're all stored in the same room, which has numbered racks and each rack has numbered spaces (for example: Sample X is in Room 1, Rack A, location B)
The combination of Room-Rack-Location has to be unique, this I can do in one table by using indexes, but how can I combine the indexes of several tables?
Do you think it is better to keep the track of place inside another table but how can I do this?
Thanks for your assistance!
Tom
Hi Guys,
Is it possible to find out if there is an Index on a table or not? I am using VB6 and Microsoft Access database? please don't ask y!!!
TIA
STA
Hi guys
thank you in advance for any idea/info!!
i have 2 tables, P.O and Invoice. In PO tbl i have an Invoice field with Index set to Yes (No duplicate)
On a form based on PO table, I couldnt enter a new record due to this so i set the Index back to NO
Now..I have a combo bound to PO table with a query taking a list serial number from another table. I want to filter away whatever serial numbers from this list that have been added to PO so i write this SQL
"SELECT DISTINCTROW tblComputer_Cabinet.[CC_Serial #]
FROM tblComputer_Cabinet, tblPO
WHERE (((tblComputer_Cabinet.[CC_Serial #])<>[tblPO].[CC_Serial #]))
ORDER BY tblComputer_Cabinet.[CC_Serial #];"
which worked fine before i removed the index
Why was it?
Hello:
I have a form that has tabs on it. I have a list box on one of the tabs linked to a query, trying to show only those records that go with the person's name on the header of the form.
Example in query:
=[forms]![frmContacts]![Page]![4].[id] -- [id] is the person number.
This is were I get confused on how to identify the correct tab and make the data only show up for that person.
thx
i have this form in microsoft Access which contains the fields refno name cpr and o combo box to choose the payment and add button , refno is the primary key....
when i open the form the ref no automatically appears and when i fill in the fields and choose one of the chosses from the combo box i get this message error Index or primary key cannot contain a null value
where is the eroor exactly?
any help plz
thankx
Every time I change me Tab Index, for my work order, when I save and restart the Tab Index is in a different order. It seem it changes back to A DEFAULT ORDER.I set the labels to "0" and then set the fields in the Tab order I want.
View 1 Replies View Related