How can I add an additional field to a table based on the output of a query? For example, I have table A with 2 types of records and table B with only 1 type of record. Based on this, I can determine which records in table A are the same type as those in table B. I would however like to be able to flag within table A, those records in table A that are the same as those in table B.
View RepliesI have an access front-end package that works just fine but if there is an update, I just want the program to run scripts to update the SQL Server tables that it is connected to. I just don't know how to do this. I know how to do this on SQL Server Management studio and all, I just was hoping I could do it straight from my access front end so that a user could just click a button and update the tables or something.
View 9 Replies View RelatedI have an Access 2010 front end with linked tables via ODBC to a SQL Server back end. Is it possible to add, delete, and otherwise alter the tables on the SQL server from the Access front end? I've tried the following but received an error "cannot execute data definition statements on linked data sources"
DoCmd.RunSQL "alter table [dbo_tblAccountsMvOld] add column [cnt] byte;"
Is there code that will let me do this?
We have a database which has a table with 2 fields - here is an example of the data:
Order File Path
240971 cclapps1scannerWorks Orders 2008Works Orders 2008ITL1ITL1TMSPC11.GFD
We basically need to change the file path to read cclapps3 instead of cclapps1 in every record.
I have a form that runs off a query that displays further details of a record in a datasheet when you double click on a row.
The query itself has criteria that looks at the open form and selects the correct record.
My question is can you change the results of the query either using vba or a built in feature of access without having to use a separate query.
The selection criteria in the query is:
Forms]![Main]![Ordering-Supplier]![Ordering-Order List].[Form]![suppOrderID]
Basically I want to override the resulting data with another record when I run an event on the form.
I am attempting to update an oracle table using MS Access.
One of the fields to be updated is a primary key number field.
I'd like to use the autoincrement field to update that field.
My sql is:
Code:
Alter table testAutoNum Add column progid autoincrement (1001,2)
This sql nicely creates the new field in the table, but it doesn't start at 1001 and increment by 2; it starts and 1 and increments by 1.It is also creating an autonumber and I need it to be a number.
What am I doing wrong?
I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck.
View 13 Replies View RelatedI don’t understand how to structure tables and relationships.
I want to build a form that allows the user to search for a postal service from a group of carriers.
User must be able to input into a form:
Weight in g, Kg or lbs
Insurance level required
Whether signature is required (Yes/No from drop down list)
Collection/drop-off options (tick boxes)
Each service has a different insurance level, some need signature; some services collect, and all have different rates.
I just want to know how to structure the database.
The main part of my question is what would be my main table be and what fields would be contained in it, and what foreign keys would be introduced to pull in info.
Would you have a different table for each service?
If i make minor changes in the backend db structure (eg add a feild, change properties of a feild in a table,). These changes are not reflected in the frontend. how can thses changes be reflected in the frontend usig the simpest method.
View 2 Replies View RelatedI am creating a database where the user can import data from a number of spreadsheets (one spreadsheet for each company). Currently I have one table for each company with the year being the primary key. Each year when a rolled over spreadsheet needs to be generated for a certain company I have Access import the data to a blank template in excel which will display the prior 4 years of data (for informational purposes) and leave a column blank for the end user to fill in the current year data. When returned the current year data is imported into the database.
My problem is that I cannot see a way to do this without having a seperate table for each company. If I have one table with all company data I cannot use the year as the primary key. Any suggestions on how I can reduce the amount of tables I need. Setting up the rest of the tables wont be a problem, but future users of the database will have to go through the process of creating tables for new companies and creating all the queries and macros associated with that table.
I have a table where in 286 different fields have to be updated by the users. ( Nature of project And i have 10 fields which are coming as a input from another table, so i use Append and Update query to add the new datas and update the datas in case of any changes in the input from another table. ( to avoid manual corrections)
Now coming to my real problem, all the 286 fields have to be updated by the user's are check box type, now what is the solution since access will not accept more than 255 fields?
Firstly, I want to admit that my knowledge about data base construction is fairly basic.
I am trying to build a data base for humanitarian projects that do not at all resemple of the usual examples including customers, invoices, suppliers and what have you. My problem in constructing the DB is that most of the projects will address more than one subject and also include more than one target group and even operate in more than one country.
In my first attempt I filled the relevant fields (subjects, target groups and country) with more than one value using a form with multi select lists. The problem was that it was very difficult to query these multi-value fields and kind people in this forum strongly advised my to reconstruct the DB avoiding such fields.
What I need is to be able to view/print various selections based on precise criteria that include a region (or country), a subject and a target group in all kind of combinations.
The problem is to decide on what tables to establish and their interrelation. I guess that when entering a project record (using a form) I will have to store somewhere what subjects (could be more than five) and target groups the project include. It is, of course, not a problem to establish special tables or value lists for the subjects and target groups and countries, but where/how do I store the basic project information for a project and the multi-values that are related to the individual project?
I hope that this is not too confusing and I would appreciate any suggestion for a simple table structure and the interrelation between the tables.
Many thanks in advance.
Niels
I want to add 3 new fields to the structure of around 80 tables (all with identical structures). Is there a way to automate this process?
Paul.
I have been working on a a table design, but I am failing to create the correct relationships.My main table is the tblIncident with the three remaining tables being linked via a one to many relationship. I am using an auto number as my primary key, then linking that number to a FK in each table.
The end goal is to create one form with a varying sample of combo boxes, check boxes and data entry fields from each table. I did not plan to create a subform for each table, but maybe that is required?
I am starting out creating a training database to track training needs and expiring training etc.I'm just looking for some tips as to how to structure the tables and relationships..I have an employee table, department table and training type table.However im wondering how i can set up requirments and then match these to check if the person is trained up to date on all required skills?
View 2 Replies View RelatedI'm creating a timesheet/check in system (log in when the person starts work so it logs it and when they log out it logs that also for the manager or someone with high power to see.) for a fictitious scenario for an IT major project but I'm not sure where to start!
At the moment I have a few tables that are like this.
Table: Employees
Fields: EmployeeID, First Name, Last Name, DOB
Table: PayRates
Fields: RateID, EmployeeID, HourRate, TotalHours
Table:WorkTimes
Fields: EmployeeID, PayPeriod, WorkDate, StartTime, EndTime, TotalHours
I'm not entirely sure if this is all correctly structured, it just doesn't look/sound right to me.
I have a psychology project where we soon use a questionnaire on a daily basis (maybe 80 variables per day per subject). Currently, there is no data in the database.
I've managed to create a table called day1 (with 80 day1 variables) and a form that looks like the questionnaire we want to use (and linked apropriately to the variables in the table for day1).
What is an easy way of duplicating the table and form for successive days? That is, i want the structure of the day1 table copied to another table (call it day2). It has exactly the same variables except they are slightly different to differentiate them from day1. I would like the form for day2 to have exactly the same layout as the form for day1, but link to the variables in table 2.
I know i could just couple the day1 table, change the variable names slightly, and then copy the form for day1 and change the links to point to table2, but there are many assessments (18 days).
I haven't been on Access for a while so im still in the process of getting the cogs turning....... i am trying to design a database structure for controllers and heating.
each controller can have many heating units
each heating unit can use many controllers
rather than code everything, how could i set this up?
Example:
Controller01 can be used with Boiler01, Boiler04, Boiler12, Boiler 20
Controller02 can be used with Boiler01, Boiler05, Boiler15
Controller03 can be used with Boiler02, Boiler03, Boiler04, Boiler20
I have a table with a ControllerID and a table with a BoilerID. In the boiler table, i have the ControllerID and a one to many relationship.
How would I set this up?
I need a table structure that will allow me to have a repair log data entry form with 3 cascading combo boxes on the repair log data entry form. There can be many repairs for a specific job but most of the time there will be one repair per job and 1% of the time two or more repairs for a specific job.
I am pulling a report based on a query that will show the repaired location, facility type, repaired item, repaired component on that item and other details related to the repair.I uploaded an empty database with the structure.
Cascade levels
I. Facility Type
II. Item by [I]
III. Component by [II]
I need to modify my customer table to cater for multiple names at one address.
Should I move all the addresses into their own separate table or leave them as is (name-Address-postcode- email etc in one table) and put the additional addressees into a separate table or is there a better way I have not thought of.
Looking for efficient table structure for this. Let's say I have a list of contractors who will get paid a certain amount of money each week. The amount of money changes each week by measuring the amount of work done, giving it a price and calculating it. The fields are these:
[pk]WeeklyWorkID
WorkID
Quantity
UnitPrice
Total=Qty*UnitPrice (not stored in this table)
EmployeeID
WeekID
So far, it's all good, but we wish to pay the contractors in an unusual way. Let's say he makes $16,000 of work this week, and we want to pay him using cash AND/OR either one or multiple debit cards. For example:
Cash: $1,500
Debit card 45: $7,500
Debit card 71: $4,000
Debit card 13: $3,000
Total = $ 16,000
We can only deposit $7,500 max in each card. But it's even worse... two or more contractors can SHARE the card and it's not always the same card. Sounds pretty funky, but it's what the company wants to do. So, what would you recommend me in this case?
I'm guessing I'm gonna need these tables: tbWeeks to hold the week number, tbCards to store the cards' info, and an extra table to make a many to many relationship between the cards and the employees.
I have a table with below fields and data
Code:
DocNo TransmittalNo TransmittaltoCon
Code:
Doc-0001 tt-0002 con-0005
Doc-0005 tt-0002 con-0003
Doc-0001 tt-0002 con-0007
Now I like to make a treeview with 3 level of information of my table that level 1 is (Doc No) level 2 is (Transmittal No) and level 3 is (TransmittaltoCon) , after searching in google I read I have to make another table (query) with parent and position field for example
Code:
ID PARENT POSITION OPTION
Code:
1 0 0 DOC-0001
2 1 0 TT-0002
3 0 1 DOC-0005
4 1 1 CON-0007
5 3 0 TT-0002
6 5 0 CON-0003
I am developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.
I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
tblEvents
EventID
EventName
tblClaimEvents
ClaimEventID
fkClaimID
fkEventID
EventDate
With this table structure there is a many-to-many relationship between Claims and Events, but, there will only be one of each event per claim. Is there a better way to set up the tables to enforce a 1-to-1 relationship?
I was planning on creating a form for the Claim table with a subform for the Events joined on ClaimID and fkClaimID, but that would allow users to create more than one set of Events per claim, if that makes sense.
I have 5 tables with below design
Table 1 : Docid (pk),DocNo
Table 2: Transid (pk),Transmittal No
Table 3: Docid (fk to table1),TransID(fk to table2)
Table 4: TransmittatoConID(PK), TransmittaltoCon
Table 5: Docid(fk to table 3),transID(FK to table3), TransmittatoConID(PK)
�combine fields�
I have made a query from top tables that returns the below data
DocNo TransmittalNo TransmittaltoCon
Doc-0001 tt-0002 con-0005
Doc-0005 tt-0002 con-0003
Doc-0001 tt-0002 con-0007
Now I like to make a query with adding new fields (parent) , (position) that shows
hierarchical structure of the fields
level 1 is (Doc No) level 2 is (Transmittal No) and level 3 is (TransmittaltoCon) ,
ID PARENT POSITION OPTION
1 0 0 DOC-0001 2 1 0 TT-0002 3 0 1 DOC-0005 4 1 1 CON-0007 5 3 0 TT-0002 6 5 0 CON-0003
Hope the thread title wasn't too confusing.
I have a database that tracks emissions from painting. Bear with me since this is going to be a long post.
:o
Some background info.
- a paint can consists of many parts mixed in a specific ratio.
- a part cosists of many chemicals
- a part may be used is many different paints
Here is how I have the existing database structured now. I’ve simplified it somewhat.
tblPaint
PaintID (PK)
PaintName - String
PaintDensity - Double
PaintVOCContent - Double
tblPart
PartID (PK)
PartName - String
PartDensity - Double
PartVOCContent - Double
tblRatio
RatioID (PK)
PaintID (FK)
PartID (FK)
Ratio - Integer
tblChemicalWt
ChemicalWtID (PK)
PartID (FK)
ChemicalID (FK)
WeightPercent - Double (Percent)
tblChemical
ChemicalID (PK)
strChemicalNumber - Long
strChemicalName - String
tblUsage
UsageID (PK)
PaintID (FK)
UsageDate - Date
UsageAmount - Double
PK = Primary Key (Autonumber)
FK = Foreign Key (Autonumber)
The Density or VOC Content (VOC = Volatile Organic Compound) for a paint can either be given OR it can be calculated by the mix ratio of parts and their respective Density or VOC Content values. One or the other must be complete.
What I did not account for was that there may be changes due to the paint manufacturer revising their paint composition, such as;
the parts that make up a paint may change
chemical make-up of a part changes (can be a change in Weight Percentages or the addition or deletion of a chemical).
ratio in which parts are mixed for a paint changes
Density/VOC Content values may change for a Paint or Part
The problem is that I cannot simply change the existing records as the emissions are calculated using all the data from each table and emissions need to be calculated using the paint/part/ratio/chemical weight percent info that was valid at the time of usage.
Another thing is that the Paint Name will not change, it’ll always be something like “BrandX Acrylic Blue”.
The person entering usage data only knows how much of what paint was used for a given day.
The person who enters paint usage has nothing to with entering the chemical make-up for parts and information for the paints and vice versa.
At any rate, my new draft table design is as follows. Two of the tables (tblChemical & tblUsage) will remain the same.
tblPaint
PaintID (PK)
PaintName - String
tblPaintVersion
PaintVersionID (PK)
PaintID (FK)
PaintDensity - Double
PaintVOCContent - Double
PaintVersionDateIN - Date
PaintVersionDateOUT - Date
tblPart
PartID (PK)
PartName - String
tblPartVersion
PartVersionID (PK)
PartID (FK)
PartDensity - Double
PartVOCContent - Double
PartVersionDateIN - Date
PartVersionDateOUT - Date
tblChemicalWt
ChemicalWtID (PK)
PartVersionID (FK)
ChemicalID (FK)
WeightPercent - Double (Percent)
I might be able to do away with tblRatioVersion and just have one table to store the mix ratios. It should be the case that a change in mix ratios (either a change in mix ratios and/or what parts make up a paint) means a change in the Paint Density & VOC Content. But I am presenting both versions of the Ratio tables here for completeness.
Version 1
tblRatioVersion
RatioVersionID (PK)
PaintVersionID (FK)
RatioVersionDateIN - Date
RatioVersionDateOUT - Date
tblRatio
RatioID (PK)
RatioVersionID (FK)
PartVersionID (FK)
Ratio - Integer
Version 2
tblRatio
RatioID (PK)
PaintVersionID (FK)
PartVersionID (FK)
RatioVersionDateIN - Date
RatioVersionDateOUT - Date
Ratio - Integer
I plan on having the DateOUT fields be populated automatically to match the DateIN for the new version. That way I can use “BETWEEN DateIN and DateOUT” to select the appropriate info for calculating emissions. The idea came from an old thread I started (http://www.access-programmers.co.uk/forums/showthread.php?t=31677&highlight=historical+data). I think this is the way to go, but with all the relationships going on, I'm having a hard time wrapping my head around it all. Am hoping someone here can help me with this.
Anyone see any problems with the new table design?
Anyone know a better way?
:confused:
Some potential issues that I see
If only the Density/VOC Content changes for a Paint, then the old set of records in tblRatio must be duplicated.
If only the Density/VOC Content changes for a Part, then the old set of records in tblRatio & tblChemicalWt must be duplicated.
Thanks for reading this post all the way to the end!
:D
EDIT: Thought about it some more.
A new version of a Part, should trigger a new version of Mix Ratios which in turn should trigger a new version of a paint.
Part --> Ratio --> Paint
Ratio --> Paint
Also, a change in a Part must trigger a New Paint version for ALL Paints that currently use it!
:eek:
if i create 100 records with autonumbers as primary keys (record_id)
then deleted 30 at random
could i update the record ids so they become 1-70?