I have a couple of tables which only have a single field. Is it worth adding a second field in order to hold an ID number (is this commonly termed an index number?) for each entry.
My other tables with multiple fields include a field for an ID number and these tables are linked via these fields.
At this stage I cannot see any reason why, in the future, I would want to add additional fields to the tables which I am currently planning to make single field.
Add and Index named Location to the tblGuest table that consists of the StateProv and City fields and uses the default property settings, Delete the PostalCode Index, save then close the table.
I have looked up all info in the text reffering to Indexing and cant seem to come to a conclusion of the way to complete the underlined area area. For the class they provide you with the files and in this particular file the primekey is Guest ID with a field value of GuestID. There are two other Indexes PostalCode with field value PostalCode and GuestID with field value of GuestID.
I am trying to create a one-to-many relationship between these two tables. I want to be able to access the 3 fields on the [Processors] table within reports based on [AllItems]. [AllItems] is a listing of account activity where the [AccountNumber] repeats. I have every field set as the "Primary Key" on [AllItems] as that is the only way to avoid importing duplicate data. I am getting the error: "no unique index found for the referenced field of the primary table"
I've inherited a data base which has the address details of our members spread across multi fields i.e. Add 1, Add2, Add 3, Post town, Post code etc.
Not all the fields contain information which means when I do a mail merge for address labels there are blank lines.
I would like to either be able to create a single field in the table (like a memo field for example) which contains all the address detail, or create a mail merge without blank lines.
I have tbl_orders which records orders. I have a linked table called tbl_order_lines which details the items against each order.
In a nutshell, an item can only appear once in an order. However, I do not know how to create a relationship or code the frm_order in order to reflect this in my DB.
To prevent duplicate records, I use multiple fields indexes, which worked fine until now. I learned that each "empty" fields are consider unique by Access, so not the best in an index to prevent duplicate records. I managed to have it worked using the default value property to give each "empty" records the same value.
Now my problem is that I have a date field which is optional, but I need to use it as a unique identifier in an index. I could again use a default value, but since it has to be a date, I find it confusing. I would rather have a default value of "No date specified" or "-" or even better, "". Unfortunately none of those works with date fields.
I have a situation where I have a report which I will generate to print our companies pricebook. I need an index/table of contents for customers to go to the page where a product is to look up prices. The problem is that we add and subtract products on a consistent basis.
So a new product will get added, but of course, to run alphabetically, there's no way to insert it, then renumber the pages for it to be easily found.to use a maketable query, which sorts all of the products alphabetically. Each time it is run, it will delete the previous table. I then need to have an autonumber field created to create my "page numbers". Yes, each time the pricebook is run, products can and will oftentimes get new page numbers.
So my thought is to have a macro run which creates the autonumber field to the table. This will then be the basis for the form, which will in turn utilize the "page number"/autonumber field to both serve as page numbers in the report. And of course, the table which is created each time will be the "table of contents".
I am trying a to build a slot booking database in which users will be able to book slots (ranging from 1-30) on a particular day for a specific site (location).
When trying to build the relationship between slot in tbl_available and slot in tbl_appointment i get the following error "No unique index found for the referenced field of the primary table" the same error pops up when trying to build a relationship between site in tbl_available and site in tbl_appointment.
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee Field: Name Form: frmUpdateEmployees Queries: 16 that depend on the table and field above.
I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?
I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.
I have to redesign the database as things has changed. I've already built a table holding records of Clients along with their address information and some attributes that relates to Clients.Now, I need to create a table for our Business contacts. This will also need to store address info and then some attributes relating to businesses. The problem is I'm not sure if I want to take address out of Client table and have Client and Business table link to Address table to follow the rule of not having any repeating data or simply allow Business table to store address.What would be the best thing to do here?Thanks.
How to get this one to display in a single column.
I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.
Customer Table with PK Customer_ID.
There are two tables with FK Customer_ID.
1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.
2. Table Lease2 - Has 1 field with 0 to Many records.
Goal:
The Type shows up in a single column.
Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)
Challenge:
Lease1 table has 3 fields that need to be transformed into a single column.
Lease2 table has 1 field to be appended to the single table.
Then, there is the column that identifies where the data came from based on the column name.
Hi all. I'm working on a temporary database (Access 2003) for work until we get our system validated. Is it okay or acceptable to have a single-field table or do I need to throw an autonumber on there for the key? I really just need this table to populate a combobox/listbox in the form. I don't want to to use the "OnNotinList" with the combobox so I need to store them somewhere. Thanks!
I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:
one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?
I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table.
1) Create a table with a single text field 2) Enter some text values, including one which begins either "aa", "AA" or "Aa" (but not "aA"!) 3) Sort the field, and note where it puts the string(s) created in (2)
For me, they appear at the wrong end of the sorted list! I have tested this in Access 2007 and 2010 and it only happens in 2010. I'm using 32 bit Access on 64 bit Win 7 Pro.
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?
I am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:
It suggests that I create multiple primary keys in the table
When I do it, it comes back with an error: Index or primary key cannot contain a null value.
How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.
Say I have a tables of words. tblWords numWordID txtWord
Then I have some entries, all more or less synonyms of each others fresh new clean
Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".
Kind of a many to many relationship junction table but only with one table!
I hope my explanation have not been to confusing, but let me know if you need a clarification.
I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.
How can I export three tables into a single excel workbook.
I am pullling data from a query using an unbound form and a query that that uses linked tables. I can not edit the index of the tables, so is there a way i can create a new index for sorting data in a form?
