I'm not sure why I'm drawing a blank but I am. Could I please get a few suggestions on how you might organize these fields into tables?
These are not baseball stats fields. I'm keeping track of how often teams win over the years. I would like to do generate a team webpages showing all historical wins, losses and championships.
TeamID
TeamName
Year
Wins
Losses
AL_DivisionChampion (3 divisions in each league EAST,WEST, CENTRAL)
NL_DivisionChampion (3 divisions in each league EAST,WEST, CENTRAL)
AL_WildcardChampion
NL_WildcardChampion
ALCS_Champion
NLCS_Champion
WorldSeriesChampion
Initially I thought about YES/NO fields but there are so many variables. I need to know STL won the division but also that it was the NL Central.
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:
Categories Table: CategoryID (PK) Description
Users Table: UserID (PK) Username CategoryID (FK on Categories.CategoryID)
Jobs Table: JobID (PK) CategoryID (FK on Categories.CategoryID)
JobDetails Table: JobID (FK on Jobs.JobID) UserID (FK on Users.UserID)
Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:
SELECT Users.ID, Users.Username, Users.CategoryID FROM Users, Jobs WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));
All the tables are linked with relationships but my SQL isnt so hot! Any ideas as to how i would do this and get it working?
Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).
As a relative newcomer to Access, I am having trouble deciding on how to design the form flow for updating and creating related records. I'm looking for a variety of suggestions so that I can decide what's best for the user and what's best for screen flow.
Here's the structure: I have what's called "an Event". Each Event can have multiple "Trials". Each "Trial" can multiple "Classes".
(This is the structure for a dog obedience competition)
Currently, for "display" or "browse": I have the following form structure:
frmEvents - single form format with a subform called sfrmTrialInfo, no navigation control - entry to this frmEvents is from an unbound pop-up form that uses a combobox to select the Event for viewing.
sfrmTrialInfo - single form format with a subform called sfrmTrailClass, has navigation control so that the user can display all trials for this event
sfrmTrailClass - continuous form format with navigation control so that user can display all classes for this trial.
My dilemma is what is the best method for letting the user create a NEW Event-Trials-Class structure. Right now I have a button on that pop-up that is used to create a NEW Event. It brings up a blank frmEvent form with the blank imbedded subforms. This is still in the design stage.
I want to have control over the data (as the user is typing ... don't let the user move forward until all requisite data is entered), but I, also, want to make the process relatively pain-free for the user.
I have a table TO-det and another table DO-DET.The table DO-det will have details about all DO for each TOID record.Both have a common field name TOID The tables are related under ONE-MANY relationship.One TO-DET record can have many DO-DET record
Now I wanted to create a form where if i add a new record to TOID i must also be able to add data for DO-DET for that corresponding TOID.
One lists all the stock symbols and company names = SYMBOL The second table lists the purchase information for each stock = PURCHASE
I then created many queries, etc. using this data. Symbol is the key link between the various tables, queries, etc.
NOW that I understand the lookup wizard in the data type, I would like to change the symbol field in the purchase table to a Lookup field. I, of course, receive a message. I am told to delete the relationship with the other tables. If I remove the relationships and change the data type, can I then replace the relationship with out damaging all the queries and forms?
I created 2 table that are linked to a link table so to get a many to many relationship.I have a table for locations and a table for parts.One location can have many parts and One part can belong to many locations, that is way the many to many relationship.
how to get values from the parts table to the Location table using the linked Primary Key in the foreign key linked field.The values also have to be interactive, if I change the value in the part table it needs to update in the location table.
What can I put in the field of the location table to get a specific value from the parts table?
I tried setting the field to calculated field and put the Dlookup function but that didn't work. The lookup wizard seems to be the solution, but I can only create combobox or list box and I need to select the value manually. I just need a value to be put automatically by looking at the foreign key.
If I put the Dlookup function on the default value, those it update if a value is changed in the part table?
I'm developing a ms database for the a "repair service" .
I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ...
Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself.
I have two databases, one containing the tables and one containing the forms and queries. The forms/query database is built from linked tables from the tables database. I would like to expand one of the fields from 7 characters to 9 characters. I planned on doing the in the table database, then updating the linked table. However, the table I need to alter has relationships with about 10 other tables and I get the (you cannot make changes to a related table error message). Is there a way I can make this change without deleting all the relationships?
For future reference, should the relationships have been created in the form/query database?
I'm building a database which will generate work orders, and those have a opening date and closing date, besides that it has a work order number that is of the following format:
YYYY/ NNN (being the NNN the work order number in that year)
to create the number of the work order I'm trying to fill the WOYear field with the year of the WODate field...but I'm not being able to do it... How should I do it?
Hi :confused: I am trying to set up a query. i have to work out the cost of a phone call with the use of three fields-- i have: lenght of call, country call code-e.g england is +44(these are fields in my query) and then the cost per minute values for each country call code(but these are in a table) e.g.Country CodeRegion Cost per minute +1 United States, Canada, Caribbean£0.20 +20Egypt £0.30 +212Morocco £0.30 How would i set up a query to solve this problem?#Can someone please help me???
I have a very simple database with two tables. One stores Schools that I work for and the other holds people that work at that school.
I have a form that displays information from the schools table and I have a button that I want have open a form that displays the people that are related to that school.
THe problem is I can't figure out how to limit the recordset to just the people from the school record I was just viewing.
For example. I am looking at a record with School ID 1. I want to run a query on the People table that will return just the people that work for school id 1. I just can't figure out the criteria part.
Hi, I'm a total newbie at Access, and know nothing about scripts. I've been operating at the level of using the Wizards and drop-down menus. I am trying to create a report that does two things: 1. Displays the results of queries that sum data from a table (I think I have this figured) and 2. Displays those sums as a percentage of a number that is input each time the report is run. (This is only one number that comes from a totally different place and has no prior relation to the data.) Any help/advice that anyone could offer would be much appreciated! Thanks!
Hello, I have two related table: table_Company and table_contact. Now i have: Table_Contact Table_Company James Bolton -----------> MacDonalds Hellen Kidman ----------> MacDonalds Now, i wanna have one "MacDonalds" in my Company table, and delete the others "MacDonalds" and still have the relationship between my contacts and their company.
Hello I need to add daily records to a related table using a form, from a button or subform displayed on a form updating the master table. This would enter the related key to the new form ready for insertion etc. Can this be done?
tblAcq and tblAcqDetail In tblAcq I have information about the delivery and in tblAcqDetail delivered articles, price and quantity. tblAcq [AcqID] ; [Contractor]; [TransportCosts]; [CustomDuties]; [SumWithoutVAT]; [LimSUM](this total cost of shipment Goods+other cost)
I'm not sure why a couple tables are not being updated after entering data. I think my relationships are correct. I've attached the database.
steps: open Main form add addres then try to add an owner. it doesn't work the first time but it does work the second time. add test data and that works. now if you look at the related tables (OwnerInfo, WellLocation, TestData) they all have p_id which is the wellLocation Id
now open gernalinfo form enter in info (tests requested doesn't work right now but you can click on them in the tbl GeneralInfo) Now if you go to the table OwnerInfo and TestData the key p_id has not been updated. But it's there in WellLocation (ID).
Heres some example data: 1 M24308/2-2F 2 M24308/4-2F 3 ORD9F0000 4 ORD9M0000 5 D90000VLO 6 D90000JOO
How would I design the table to allow me to define alternatepart numbers and also related parts? For example:
Record 1 is the primary part and record 3 is the alternatepart. Record 2 is the primary part and record 4 is the alternatepart. Records 5 and 6 are related parts to records 1, 2, 3 and 4.
I have a form that allows the user to select a part and displaythe part data. But I also want the alternate parts and related parts displayedon the form, maybe in a subform with tabs. I havent gotten there yet because Icant determine the table design.
I have created a database for storing, query and deleting data. Now the user wants to delete a row from a table and create two new rows. In my opinion this will need lots of work in order to replace all related queries forms and reports in the form. Is there any whay to do my job more easy in modifing a table and all related items be modified by themself? I still do not have any data in the table but if I did will I loose those data? Thanks.
The fields are somehow related where values of B (unique) is taken from A(unique). I trying to do an SQL such that if I input a single value A=9 or 6 or 5 or 3 or 1, the following should be the output:
A B 9 6 6 5 5 3 3 1 1 Null
Or in another case where I input a single value A=8 or 4 or 2 the following should be the output:
Hi, I've been trying to get this for ages now - both in the design view and in sql:
I have 2 tables - one called DrawingsRegister and a related one called DrawingRevisions. Each drawing has one or more drawing revisions. I want a query that will show each drawing (just once) that has more than one revision:
I have a table (tblrecords) with fields, entered by, recieved by and date and time,entered by and recieved by are both referenced in tblemployee.i want a query that will retrieve
fname, lname (of entered by from tblemployee), fname, lname (of recieved by from tbl employee) and the date and time from tblrecords)however i cant think how to do it with both names coming frm the same table?
I am trying to export text to a 2010 word document from an access 2010 database with DAO. I have successfully been able to export text from the main table tblLandSales via variables (below) and then subsequently a document. I used the following code:
Dim objWord As Word.Application Dim docm As Word.Document Dim rst As New ADODB.Recordset Dim strSQL As String Dim strLandSalesID As String
[Code] ....
Now I want to export other data from a one to many related table where [fk_tblLandSalesID] is the foreign key in the related table and tblLandSalesID is the primary key in the main table.
DCount("*","[tblLeaveRequests]","Status='Approved' And #" & Format([vDate1],"mm/dd/yyyy") & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")
The above code counts all leave with the status of approved on the table [tblLeaveRequests]. Great, but i did forget a critera.
Of the leave it is counting, I want it to only count if the employee the leave is for is in a particular location. which exists on the related table, and not the one dcount is calling.
Is this possible? or will i have to resort to replicated data in [tblLeaveRequests]
