Access Novice - Table Relationships Help
Apr 26, 2005
Hi, I am currently stuck on making a working one to many table relationship, I will post what I have so far, what I am stuck on is forming a proper working relationship.
tblCustomers
CustomerID
Name
Address(street)
City
Region
PostCode
Telephone
Discount %
tblOrders
OrderID
ProductID
Description
Qty
UnitPrice
tblProduct
ProductID
Description
Room
Finish
UnitPrice
Stock Qty
The desired output is a working order form, that when, for example you enter the product ID into the appropriate field automatically brings the rest of the product fileds such as description and unit price. And when the customer ID is put into the appropriate field all of the relative customer info is shown.
I am new to access and I apologise if my post is unclear or incomplete, any input is appreciated.
Thanks in advance.
View Replies
ADVERTISEMENT
Jul 12, 2005
I'm using Access 2003. When I open the relationship window, one table linked to my main data table appears six times - the name is the same but with the addition of a number 1 -5. I can delete the five spare ones and drag a relationship to the main table but it keeps dissapearing.When I try to change the primary key in this table, it tells me that there is a relationship established which I must break - unfortunately, I can't see any relationship between the two tables in the relationship window. Any ideas?
John
View 1 Replies
View Related
Nov 14, 2007
I hope (boy, do I!) that someone can help me with Access 2007 amd SQL.
I'm a graduate student taking a course in databases. I'm NOT techie, but I figured my husband or kids would be able to help me - they can't!
I am absolutely stumped and am hoping someone will be patient enough to chat with me about SQL and Access.
(You CAN query in SQL with Access, right?)
Thanks,
Bev
View 1 Replies
View Related
Oct 12, 2005
have a table i am creating for a subform to calculate a price for a line total.
[item] [amount] [weight] [yeild] [linetotal] *[need coverted price from a query]
the converted price is in a query and i would like to load it with its selected item in a table (or form?) so i can multiply for a line total in the subform.
is this easy?
View 1 Replies
View Related
Feb 13, 2008
Greetings Access Geniuses,
I am currently in the midst of conversation over at utteraccess.com about a large database project I am working on.
I will post the most recent updates here, but if you want to download the database for your own review, you'll have to go over to the other forum. By the way, this is NOT an advertisement. I am a real person with a real, significant, and immediate need of as much assistance as possible for this project.
Before I go on, a few things about me. Though I am in the legal profession, my technological background is very strong, including a high level of proficiency with MS Office apps with the exception of Access. I know my way around the program (the result of its homogeneity with the rest of Office), but have not made a real database in the past. I also do not know any programming languages.
I have recently indulged in an Access crash course of sorts, including some book and online study on things like planning, design, and normalization, but I am still having difficulty wrapping my head around making it work.
That said, the link to the other discussion is: [W W W DOT]utteraccess.[ADD DOT COM]/forums/showflat.php?Cat=&Number=1590364&page=0&view=collapsed&sb=5&o=&fpart=all&vc=1
Feel free to gloss over it to see how things have progressed. Below is a paste of my most recent substantive post. Any help anyone can give me is of great value to me and I really, really appreciate it.
_____________________
Hi Everyone,
Attached is the most updated version of my database project for your review. I have also attached a sample of the output we would like to have for each product. This sample is not based on actual data, but it clearly shows what we are trying to achieve via a form of some sort. More on this in a minute.
The following changes have been made to the DB:
-Changed tblTrustProspectusVersion to include the appropriate data, based on our business model.
-Added descriptions to all non-PK fields.
-Created relationships to illustrate how things fit together. Note that these are NOT the actual relationships, but are for illustrative purposes to help everyone here (including myself) further understand how things fit together.
Our Business:
I am part of my company's legal department. Our team handles a number of different things primarily associated with Securities and Exchange Commission (SEC). filings. We have two major product lines. Everything that happens with one in this DB happens with the other as well.
Each product within a product line has certain features and other necessary information we need to see when doing our filings (see the attached sample output). These features and necessities include, but are not limited to, various statuses, various numbers associated with the SEC, various important dates, etc.
In addition, each product has a certain "fund lineup" associated with it. These funds are made up of two components: the name of the fund (aka "portfolio") and the name of the subadvisor to that fund.
Futher, each of these funds is associated with a certain Trust. The SEC requires us to send prospectuses to clients based on these Trusts, which, as I mentioned, are comprised of the said funds.
Basically, we need to be able to select a product from a drop-down list and have all of the aforementioned information populate instantly.
Before I close, one question with respect to my "tblProductFeatures". Like I mentioned, each product has a certain set of features associated with it. Each feature has a certain fee associated with it. These features come in four basic categories: Living Benefits + fees, Death Benefits + fees; Maintenance fees (just short list of the possible fees); and 12b-1 fees (another short list of fees).
Since the features can be so easily broken down, should I add them to their own tables?
Please consider this as you give your advice on how to acheive my desired goal.
I hope this makes sense.
And again, thank you all so very much for your help thus far.
Best,
Gilbert
View 12 Replies
View Related
Sep 13, 2007
I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
Emily
View 14 Replies
View Related
May 17, 2005
I have created database but I now have 3 questions. I havce changed a subform to view as a pivot chart but I am unable to split and group in a similiar waay as in excel, is this just something I have to accept because otherwise my "lets have a works database" theory is shot.
2nd I would like to create a switchboard how do I do this.
and lastly some of the data I would like to utilise for mail merge document ( name and address stuff) are these easy to set up??
thanks very much
Andy
View 1 Replies
View Related
Mar 14, 2006
I am completely new to Microsoft Access but otherwise consider myself to be a Tech Geek. I have made a very simple and small database. It contains the name of my customers and the orders they have placed with my company over the past three years.
Now i want to generate report which when given the name of my customer would give me all the orders placed by that specific customer. I know how it can be done query but i want to do it through some kind of access page so that my subordinate who is very technically challenged can manage that.
Though i know it is a very basic question but i tried everything and could not do it....can somebody please please please help me.!!!!
View 1 Replies
View Related
Nov 24, 2005
Hi. I have a problem. I want to make a search form that will collect info from several tables and show the results in a list box, like a list(for example with the same name but with different other attributes ). I know it is possible and it is not hard but i'm novice to access. Thanks in advance :)
View 1 Replies
View Related
May 16, 2005
I'm creating a Members Area on a Trade Organisation website; I have an Access Database with all the members’ details including the name of the nominated representative for that member company. I am able to create a login for the nominated representative together with his/her chosen password but some of these member companies employ over 100 staff and what I would like to do is create a separate login for each member of staff, which is where I think that I need a table related to the Main table.
Just supposing one member has 100 staff and the member decides not to renew his membership, by deleting his entry in the Main Table all members of his staff would automatically have their access to the member’s area denied.
I just can't get my head around this relationship bit; I do have a cut down version (336kb) of the database if somebody would like to give it a go.
View 2 Replies
View Related
Oct 14, 2005
I am trying to put together a sales/purchase/stock control/accounting program using Access. I have put together a number of tables that I feel will be required and now I'm trying to set the relationships between them. The primary end document to be printed will be a Sales Order and a Purchase Order, likely generated from the reports module, so I have a table called Purchase Order and another called Sales Orders, related to Purchase Orders will be a table called Suppliers and another called Customers will be related to Sales Orders. Another table is called products.
For the Purchase Orders table, do I only add fields that are the primary key from the Suppliers table in order to list the full customer name and address stored only in Suppliers table. Would the supplier table be the parent and the Purchase Order the child ?
View 3 Replies
View Related
Feb 15, 2006
I have setup a number of tables for a booking system. The tables are as follow:
tblCustomers
tblBookings
tblRegularCustomers
tblRegularBookings
When a booking is made for just any time, it is in the tblBookings table. Regular bookings for example, every week, are put in the tblRegularBookings. In each of these tables, a booking cannot be made for the same date and time period. I am wanting to link the tables, so that when a normal booking is made in the one table, it cannot be the same date and time period as a regular booking as well.
The fields in each are as follow:
tblBookings:
Booking ID
Customer ID (linked to tblCustomers)
Date for Booking *
Time Period *
Weddings/Birthdays (yes/no)
Extension (yes/no)
Cost
tblRegularBookings
Regular Booking ID
RegCust ID
Date For *
Time Period *
Extension
Cost
What should i link to allow me to not create a normal booking on the same date as a regular?
Thanks
View 6 Replies
View Related
Nov 25, 2004
Here are my relationships for my 5 tables I created. My database is something that a turn key automotive performance shop would use. Tell me if my relationships are correct and what I need to do. I think I got a good start but I am not sure on a few things. Also what do I need to do for my validation and look up tables. Any help on that would be great. Thanks.
http://www.mustangmods.com/data/1030/relationships.jpg
View 14 Replies
View Related
Feb 9, 2005
This is a simple video library database, 1 member can have many loans, 1 video can have many loans and 1 loan can have many videos, so with that in mind woud anyone be able to make my relationships right? because at the moment you cant take out more than one video on each loan?
thanks in advance
http://www.the22nd.com/forum/uploads/post-12-1107976078.jpg
View 14 Replies
View Related
Apr 1, 2005
I have restructed my original tables into 3NF and added relationships. But I think I am missing a few more items to go on to next step. Help me, please! I am putting together an automated Time Card Entry Database based on a timesheet and other reports already existed in Excel.
I have the following fields on this form as follows:
1. Week Ending: automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date.
2. Employee Number: combo box that will populate next fields (1. Employee Last Name; 2. Employee First Name) after user select correct Employee Number.
3. Employee Last Name: automatically populates when Employee Number is selected.
4. Employee First Name: automatically populates when Employee Number is selected.
5. Acct Id: combo box containing a list of labor description that has associated fields as follows.
6. Description: Text Box will populate after selecting Acct Id.
7. Cost Center: Text Box will populate after selecting Acct Id.
8. Acct: Text Box will populate after selecting Acct Id.
9. Category: Text Box will populate after selecting Acct Id.
10 Pay Type Id: combo box that will populate Pay Type.
10. Pay Type: automatically populates when Pay Type Id is selected.
11. Allocation: Free form, to type comments.
12. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri
13. Total Wk Hrs: total hours for the week for per line of Acct Id.
Here are my tables:
1. tblEmployees:
1) pkeyEmployeeId = AutoNumber
2) strLastName = Text
3) intStaffNumber = Number
4) fkeyEmployeeTypeId = Number
2. tblEmployeeType:
1) pkeyEmployeeTypeId = AutoNumber
2) strEmployeeClass = Text
3) strEmployeeType = Text
3. tblAccounts:
1) pkeyAcctId = AutoNumber
2) intCostCenter = Number
3) intAcctNo = Number
4) intCategory = Text
5) strAcctName = Text
6) strAcctDescription = Text
3. tblPayType:
1) pkeyPayTypeId = AutoNumber
2) strPayType = Text
3) strDescription = Text
4. tblPayroll Schedule:
1) pkeyPayrollScheduleId = AutoNumber
2) intPayPeriodId = Number
3) dtmPayStartDate = Date/Time
4) dtmPayEndDate = Date/Time
5) dtmCheckDate = Date/Time
5. tblTimecard:
1) pkeyTimecardId = AutoNumber
2) intStaffNumber = Number
3) fkeyPayPeriodId = Number
6. tblTimecardHours:
1) pkeyTimecardDetailId = AutoNumber
2) fkeyTimecardId = Number
3) fkeyAcctId = Number
4) fkeyPayTypeId = Number
5) strAllocation = Text
6) intSat = Number
7) intSun = Number
8) intMon = Number
9) intTue = Number
10) intWed = Number
11) intThu = Number
12) intFri = Number
Here is the layout of my form in this order:
1. Pay Period Id
2. Week Ending
3. Employee Number
4. Employee Last Name
5. Employee First Name
6. Acct Id
7. Description
8. Cost Center
9. Acct
10. Category
11. Pay Type Id
12. Pay Type
13. Allocation
14. SAT
15. SUN
16. MON
17. TUE
18. WED
19. THU
20. FRI
18. Wk Hrs
Question 1: Please review table relationships to see if I overlooked any tables that can be broken down or named its column differently?
Question 2: I have restructured these tables into 3NF, please see my previous posts under AccessRookie (although before 3/2005, someone used this ID).
Question 3: what code do I need to automatically populate "Week Ending" field with the correct "CheckDate" that will compare against today's date and insert into "Week Ending" field? Currently, user selects from Combo Box(Pay Period Id) then it populates Text Box(Week Ending).
Question 4: how come "tblTimecardHours.fkeyTimecardId" field is not populating? I think it is my table relationship: tblTimecard & tblTimecardHours.
Question 5: what code do I need for "Wk Hrs" on subfrmTimeEntry, this column needs to sum these columns: SAT, SUN, MON, TUE, WED, THU, FRI).
No need to store total since it is only need to display in data entry form (subform) and printing report.
I need your assistance since sometimes, it just takes another pair of eyes to review another peers' work. It's been ages since I've done any development from scratch. Help!!! Is there any way, I can attach my zipped database? It is 217KB zipped but this site only allow 100KB attachments.
Sincerely,
AccessRookie =)
View 5 Replies
View Related
Oct 5, 2005
I Have These Tables:
Case, Agents, Forwarders, Orders.
1 Case Has Each Time 1 Agent, Many Forwarders And Each Forwarder Many Orders
1 Agent Many Forwarders
Can Someone Suggest The Best Way To Link Them?
View 1 Replies
View Related
Jul 11, 2006
Can anyone look at this pdf and let me know if my relations are correct, i know i haven't marked on if its one to many etc but i just need to know if it'll work?
View 3 Replies
View Related
Jul 15, 2006
Ok im extremely new to this whole Access thing so I have several questions. First, let me give you an idea of what I am attempting: I am a member of a committee and we are involved in multiple projects. Each person is either inactive (working on no projects) or active (working on 1 or more projects). I want to set up a table that lists the member names, contact info, and which project(s) they are currently working on. I set up the basic table (not sure if its set up correctly or not though) but since some people are on more than one project, how do i make it so i can enter multiple values for the "Current Project" field? Also, I would like to make another table that lists Project Names, members (the members that are working on each specific project), and project description. How do I set it up so that under members (on the project table), it will gather the info from Member table (the one with contact info and such) and automatically list which members are working on each project? Also, There is one Leader per project and the rest working on it are just normal members. Can anyone suggest a good way to display who is leader on each project?
if this is confusing, just ask and i'll try to clarify for you
thanks, TRC
View 4 Replies
View Related
Sep 22, 2006
Hello all,
I have tables related by key fields. Is there a way to enter data into one table and have the key fields of the related table automatically enter into the key field of the related table?
Also, can you make custom drop down menus in a field? For instance, I have field that I would like to have a text in, there are only three values that are needed but spelling is important so I would just like to pull a list down to enter the data.
Thanks for any help!
View 8 Replies
View Related
Mar 6, 2008
im using access for the first time in years as iv not used it since college and im having problems with table relationships. i have 3 tables "cusomer details", "product details" and "purchases".
i have created a relationship between customer_id (primary key) in customer details and customer_id in purchases and product_id (primary key) in product details to product_id in purchases.
the idea is every time a customer makes a purchase i enter their customer_id into purchases and it will automaticaly bring up their name and address and enter the product_id and it will automaticaly bring up the product name in that table (purchases)
this does not appear to happen???????
i know its been a few years since i last used access but im sure iv done it right, what am i doing wrong?
TABLES BELOW:
Customer Details
customer_id
surname
forname
address
telephone
email
reg_date
Product details
product_id
product_name
product_category
price
Purchases
purchase_id
customer_id
surname
forname
address
product_id
product_name
quantity
View 8 Replies
View Related
Aug 9, 2006
I am new at Access. I am doing a database tracking the test results of eight different grades of students. The primary key is the name. When I try to add the 3rd test score table, I lose all information of all of the tables. I have tried changing the relationships, and cannot make this work. Any suggestions, please help.
View 1 Replies
View Related
Mar 29, 2007
I have 2 tables. One is JOB TRACKING and has all the information about a certain job and all the trades involved. The other is a table of email address for the managers of each of the trades. I have them connected (with relationships) so the trades match up.
There is a form that I fill out each time a new job comes in and I check off the trades involved on that form. Is there a way for that form to pick up the email addresses of the managers of the trades checked off? So then I can create a button some how to send an email to them containing that particular form...
thanks!!
View 1 Replies
View Related
Mar 28, 2006
I'm trying to set up a database from scratch and have never used Access before. It's being set up to hold information gathered from a survey of roughly 2000 people on their fave Manchester songs and artists.
It's going to include their name, email, city and 1,2,3,4,5 choices of fave songs. The aim is to have everything in a simple datebase where I can work out who has voted for what in each section, how many votes a song got a position 1 and so on. But I also want to be able to work out the total number of votes over the five catagories.
Is there a way of doing this? I can bring up information by using query but can I be specific as to what information the query brings up?
This is probably a stupid question but any help would be very useful. I'm using the Idiots Guide to Access and as I said this is the first time I've ever used the program only installed it yesterday!!
Ta
Nick
www.PrideOfManchester.com
View 2 Replies
View Related
Aug 23, 2006
Hello, this is my first post. I am very much a novice and have come across a problem that I'm sure is simple to solve but so far the solution has eluded me. All I want to do is subtract a field in one record from the same field in the next record in a query or report but I have no idea how to do it. Can anyone help please ?Thanks in advance.
View 1 Replies
View Related
Oct 17, 2004
HI all,
I am a beginner at access, please help me.
I need to create a customer id filed that has letter and number together, i.e. ZH72773. And the id must change incrementally, but no number 00000. Also it should change the letters after the number reaches 9999, i.e. ZH9999 --> ZI0001.
Would anyone tell me how to do this, please.....thanks in advance
Songrit
View 2 Replies
View Related
Jun 7, 2006
Hi everyone, heres a question I hope is easy to answer:
I have two tables, one containing about 1500 entires another with about 43,000.
I want to find out which entries in table1 are also in table2.
I really appriciate the help, thanks so much!!
View 1 Replies
View Related
