Do fields in Access Tables have a 'Number' property that you can use to reference them?
For example, if you have the following fields:
EmpID
FName
LName
Department
Hire_Date
Is there a way to query an employee's Hire_Date by telling Access to return the value that is in the 5th field [Hire_Date]?
[URL] ....
I have a form with two tables referenced. I am using the form only to update one of the tables. I am using the other table to pull a reference field. When I add my second table using the query builder, it makes it to where I can't edit/add in my form. I assume its because of the SQL insert statement, but I don't see this statement and can't find where it is to edit it. How to have the form only update one of the tables, while just using the second table as a reference for a field?
View 1 Replies View Relateduse the same referenced ID more than once in the same table? I have a database for my stamps. The exact same type of stamp may be kept in more than one location (Album). So, I have TBL_Albums which has two fields "AlbumID" and "AlbumName". Can I use "AlbumID" in my main RecordSource "TBL_Main" more than once to reflect the different Albums this stamp may be found in without causing any major problems to the database?
If this is okay, I could then assign different alias names to each "AlbumID" column in "TBL_Main" such as Location1, Location2 etc to be able to tell them apart on forms etc.
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"
View 3 Replies View RelatedI am using Access 2013 and in table design view. Whenever I add a new 'Number' or 'Currency' field the 'Default Value' property is (by default) always 0.This is fine except when i want to refer to a foreign key or amount which could be unknown. In either of those cases the resulting field will either be invalid (because no foreign key has ID = 0) or inaccurate (because 0 is not the same as Unknown).
I would prefer if the 'Default Value' property for new fields was always nothing, then I can add my own if and when required. I want to change the default setting on a property for new fields, not the value of the field itself.
I'm guessing this isn't actually possible, but at least this post has served to vent my frustrations! What bugs me even more is that i'm sure it wasn't like this in Access '10, is this another "upgrade".
I am having a table for invoicing and vouchers, and I need to have two fields that will have auto incremental numbers. How I can have two fields in the same table with autonumbers and how can I get one of them to start from a different value.
Eg. Record 1 - [inv No] 1 [Vno] 0005
Record 2 - [Inv No] 2 [Vno] 0006
I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.
I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.
This database is for quality control checking purposes.
First off,
Database 1:
Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.
Database 2:
This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.
What basically ends up happening is that the data in Database1 become Table fields in database2.
Example:
Database1:
Radio Check Audit Standard.
Needs to check following:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Another Random Check Audit Standard
Needs to check the following
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Did something specific for this Audit
Didn't do something else, also specific for this Audit.
Etc.
Continue on with other Audits.
Database2: Will keep track of all the quality checks that have been preformed.
Today we will check Radio Protocol
Did they:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Yes, No as necessary.
2 Days later, we need to do another QC on the Radio Protocol
Need to check if they did:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Etc.
But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.
Right now, i see 2 options:
Option1
Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc.
The fields in Database1 are automatically put into this specific instance of Quality Check, etc.
Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.
Option2:
When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.
Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.
I'm seen the first option as the only viable one.
Am I missing this, is there some other way to do this that would be better / wiser? Etc?
Kelemit
I have an old Table with Movies, Actress, Actor, & Director Fields & I have converted it to .accdb. I want to turn those short text fields to an ID number. I have built Tables for those fields(indexed no dups) with an ID field. I have over 5000 records in the original table and dont want to input those numbers by hand. I am using Access 2013.
View 5 Replies View RelatedI have a unique database that I am building. I am new to Access and am trying to learn what the best practices are.
We are a manufacture and I am trying to build a database that captures all the unique features that define an individual product.
From these unique variables I can generate a BOMs, Production times, Box content labels, job tickets, etc. All this information is entered using a form, I want one location to input all the pertinent information to avoid having to enter anything twice.
The trouble I am having is that I have hit the 255 fields. I know that this typically means that there is a poor structure, however this is the amount of data required to define one product. Is it possible to split the database into two or more databases? I built a second database, but how do I sync the "Part Number" fields so that they always match each other?
I have to submit a file to a government agency that requires more than 200 columns. Is there a number of column limit an Access table can have?
View 1 Replies View RelatedI 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 an excel table called Parameter with a column called "Test" -- The column contains integer numbers only. So all the numbers in the column are like 5,10,15,20 etc..I have an access macro which imports the entire excel table into a access table called dbo_Parameters
I have created an access macro to run "Saved Import" for 'dbo_Parameters"..After uploading, all the data in the column Test is formatted to mm/ dd/ yyyy. The Field Size is Integer, but the format is view format is converted to a Date..I have to change the properties of the column to "General Number" and get rid of the date format.how to change the import format!
a) The format of the column in Excel - It is number
b) The import procedure and saved import. I am unable to change the format of the import during upload. I make sure that the import format column is Integer
Access 2013 stops working when I open any table in design mode, and select any field with the type "Number". I can edit Text, Date or Yes/No fields, but not Number ones.
View 7 Replies View RelatedDate of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
Hi
I am currently creating a report form which only calculates
fields/columns in my main table. However I am currently struggling
counting text fields and checked fields as well as data fields in this
table.
I want it to count all the "yes" checked fields in my main table targeting
another column's data e.g I've got column with Category listed as 1, 2, 3, 4 and 5.
Along with this there is a check box to say that a job was completed under cat. 1,
2, 3, 4 or 5.
I have managed to get the formula to cound all checked boxes in the databases,
but what about targeting only say category 1's checked boxes or category 2's checked
boxes. I need to work out %'s from these individual totals as well.
Any suggestions on this would be great!
Elza
Hi,
I have a database where by users can input images. The database then copies the selected image into the current working directoryimages (c:dbimages as an exmaple) the it keeps a reference to the path in a table. When users then browse the records on the form I have an ImageFrame which looks up that path and displays the picture. This all works fine.
The problem I have is that sometimes the working directory changes. I then have problems with the previous images inputted as the tbale where the reference is stored is still pointing to c:dbimages. How can I can it so that rather than have a fixed path to c:dbimages the database will just looking in the current application directoryimages for the pictures?
Many Thanks,
How to properly normalize dates in access. I have read many things and have actually normalized dates before in the past but feel that there is a specific correct way to accomplish this without having to jump through many hoops on coding and query design.
View 8 Replies View RelatedOK, Here is my issue:
CustomerTable:
PK:CustomerName
AgreementTable
PK:[Lookup]CustomerName from Customer Table:Agreement#
AddendumTable
PK:[Lookup] Customer Name:Agreement# from Agreement Table
So, Each customer may have more than one agreement.
Each agreement may have more than one addendum.
So when I create a table to create a new addendum, naturally I would like to link this to an agreement (which has already been linked to a customer). When I do the lookup wizard for the agreement field however, instead of being returned a list of customers, i get the PK of the customer and not the customer name.
How do I get the lookup to show the customer name and not the PK.
I have at least 3 relates tables in my access database.the first(sessions) table stores session detailes like id,date,time
The second one (tblemployees) contains our employees details like name, idp ,the third (attreq) relates the first table to second it stores ids of sessions and ids of personals that which determine which personals have attended in special sessions.
Now,my problem is that I want when a personnel log in and opens "confirmed session"form ,the access check and open records that this person have attended. How can I do it?
My database is built using Access 97.
I have the following table and 2 queries:
Table: “Materials Master Sheet”
field: “Material”
field: “Re-Order Level”
field: “Re-Order Quantity”
Query (crosstab): “Most Recent Count Numbers”
field: “Material Name” (joined to table’s “Material”)
field: “Count Quantity”
Query (crosstab): “Orders Pending Delivery”
field: “Material” (joined to table’s “Material”)
field: “Total Orders”
I am attempting to create a query called "Count vs ReOrder" with the following fields:
1. “Material Name” - comes from “Most Recent Count Numbers” query
2. “Order” - if the inventory count has reached the reorder level point, then 1, else 0; criteria: 1
expression: IIf([Most Recent Count Numbers]![Count Quantity]<=[Materials Master Sheet]![Re-Order Level],1,0)
3. "Suggested Order" - reorder quantity - count of any orders pending delivery; criteria: > 0
expression: [Materials Master Sheet]![Re-Order Quantity]-[Orders Pending Delivery]![Total Order]
This works great if all materials have orders pending delivery. However, if there are no orders pending (material not displayed in crosstab query), the material is not displayed (even though, in reality, the suggested order should be >0)
Currently does this:
material 1 - count = 1, re-order level = 2, re-order quantity = 8, orders pending = 4: shows material and suggests ordering 4 (correct)
material 2 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 4: material not shown (correct, as suggested order would be 0)
material 3 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 0: material not shown (incorrect! should be shows material and suggests ordering 4)
How can I go about addressing the missing “Orders Pending Delivery” numbers?
I have a main table which records employee,date,record employee is related to tble employee. i want to be able to delete an employee however keep the referenced records with the name, date, record, is this possible?
View 3 Replies View RelatedI'm creating an amortization schedule database using Access 2010.
I designed it such that I'll input actual principal and interest amounts in tbl_LoanActivity when I get statements in. I'd like to be able to press a button to update forecasted principal and interest amounts, then later I'll add code to make a report pop-up.
I initially designed this with only one loan to see if I could make the thing work, and it did. I've since added another loan and made adjustments, and this is where I'm having an issue.
tbl_LoanActivity references tbl_Loans, which has interest rates and monthly payment amounts. Needless to say tbl_Loans is the RecordSource for frm_Loans, and as such has a field LoanID. I have two queries that need to reference [Forms]![frm_Loans]![LoanID]:
1) qry_LoanActivity_MaxDate,
2) qry_LoanActivity_withPrincipalBalance
I can run these 2 queries (individually) with a reference to frm_Loans.LoanID just fine. (Without that reference, forecasted principal and interest numbers are highly inaccurate.) I have a qdf append query (vba) to calculate forecasted principal and interest amounts based on the most recent principal balance (referencing queries above), and relevant data in tbl_Loans (InterestRate, and RecurringAmount).
So here's the problem: without the above queries referencing [Forms]![frm_Loans]![LoanID] the qdf runs, yet produces inaccurate numbers. With the above queries referencing [Forms]![frm_Loans]![LoanID], Access gives me a message "Too few parameters. Expected 1." If I take the qdf and put it into a standard query it runs just fine. I need this append query to be a qdf so it will loop until RecordsAffected = 0.
I've sterilized the data and attached the database. I was thinking this adjustment of adding the reference to [Forms]![frm_Loans]![LoanID] in the 2 above queries would work, but it seems like the qdf is not inheriting the parameter.
I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.
The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)
We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.
I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.
The check number.
I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.
By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.
I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
View 8 Replies View RelatedHi,
My database has 4 tables. Table 1 is assembly line 1 data. Table 2 is the sizes for assembly line 1. Table 3 is assembly line 3 data. Table 4 is the sizes for assembly line 2.
The table data comes from another program - they are reimported regularly and so I cannot change, add or delete fields from these 4 tables.
I have to change tables 2 and 4 to crosstab queries and then create 2 queries that join 1+2, 3+4. That's easy enough, no problem.
But then I have to UNION queries 1+2. While 95% of the fields are the same, the most crucial are not - the sizes. Assembly line 1 has 5 less sizes than assembly line 2. I need ALL of the sizes to show in the combined query.
I need to add these 5 fields without adding them to the tables. I did manage to create a crosstab query that included the missing sizes, but now I have a 1 in those fields where I need a NULL or Zero value.
Does that make sense? Can anybody help me?
Thanks in advance.
Rhet
I am really new to Access 2010 as I normally use SQL so im sure this is a very basic question. I have a form where I have linked some sql tables to a table in access so the data gets stored in SQL. The form consists of many fields the basic fields are Policy ID, Name, Office, Month, Dept etc. At the moment users have to fill this in using comboboxes and text boxes but I want to change this so when the user puts in the Policy ID all the other fields are populated ie in the office field it would have a sql query of or something that looksup the office from the policy id that is entered:
SELECT [Office] FROM DBO.DQ
WHERE [Policy_ID]='what is entered into the Policy ID field' and so on.
Also not sure if this works but as I have now linked the tables to SQL does this mean that when a user fills in the information into the form it will then update the SQL table or is this another issue?