Help With Relationships And Table Design:
Hi all! Here is what I want to do, and what I have done so far:
I have one table called "AUTOS" This is a table imported from pc file, and tweaked to make it work well in Access. I learned alot doing this, and now have workable forms, queries, and reports being generated on this table. I learned on here not to let my users "work" with the table, only through forms. So that all works very nicely!
Here is the next step. First a simple background: We are a Used car dealer. Table "AUTOS" contains rows, each one consisting of many Fields. The main one being "Stock NO". Then all the remaining fields are vehicle info (many fields, but all relating to the vehicle type etc), cost, sales price, etc.
What I want to do is create a new table, called RECONDITIONING. In this table I want to store everything done to a vehicle after purchase. For example, if we purchase a vehicle, then change the oil, put on new tires, and detail it, I want these items in the reconditioning table. But I want them to remain linked to the "correct" stock number in the main table.
I believe I have done this, by doing this: I created a second table called reconditioning. Added the fields I wanted, the first one being "Stock NO". I made "Stock NO" in the main table autos the primary key, and then opened relationships, and linked the reconditioning table to the main table via that field.
It appears to have done what I wanted too, because if I open the reconditioning table, and type in a vehicles stock no, then the reconditioning info, then go back to the main table, there is now a + next to the row. If I click this plus, a box drops down with each item I added to the reconditioning table. So far so good!
Here is the main question: Now that I have this info in what seems like the proper fashion, how do I work with it?
I need to create a form, containing elements from EACH table. The design I have in mind is like this:
Across the top, I want vehicle info:
Stock No Year Make Model Date In Cost
5922 1994 Ford Focus 1/1/05 1000.00
Then under that, I want a "table" in a form, which pulls from my "reconditioning" table, listing each item that has a stock No that matches the one on that record:
5922 Replaced Windsheild Wipers 1/8/05 20.00
5922 Tuned Up 1/10/05 75.00
Ok, now, I want to allow my users to add to that on this form, to type in new entrys. I think I got the idea how to do that, but suggestions are welcome. Now, on this form, I want at the bottom, the following:
Vehicle Notes: (Just a text field for "notes" on a particular vehicle)
Total Reconditioning cost: (this is a field on the FIRST table, AUTOS, that I want to calculate by adding the total from the list above, IE in this case, 95.00)
Total_ACV: This is a field from the first table as well, which I want to calculate using (Vehicle cost: Which is in the first table)+(Total Reconditioning Cost: which is above)
Like I said, I think I have the table layout and design correct, I am just not sure of how to work with values in linked tables like I have done, and I am not sure get the right info in my list on the form, then add the totals properly.
Any suggestions are appreciated!
Bill
View Complete Forum Thread with Replies
Related Forum Messages:
Table Design & Relationships
Hi Guys, thought i would pick your brains to try and help me figure out how to build this database. Any help would be very much appreciated as this one is making my head spin. Brief:: I need to create a database that will hold information about our suppliers and the products that they stock and make. One supplier can have a number of contacts that we speak to. The supplier produces a number of different products. Each of the suppliers stock certain items in certain sizes and weights and make the remainder of their stock to order, so each supplier has a number of different supply options. So in addition to the multiple supply options their are multiple sizes & weights, as well as multiple qualities. dont know if this helps people to understand buit the products we are talking about is paper (sheets or paper & reels of paper) example. Joe BLoggs (Supplier) has 3 sales members (Louise, Maggie, Sue) this supplier holds some sheets and some reels in stock (Stock Sheets, Stock Reels) but they also make sheets of any size and weight between a certain range as well as reels (Bespoke Reels, Bespoke Sheets). These sheets and reels can be of different qualities (gloss, silk, offset). These different qualities can have a number of different grades (Manhattan, Triumph, Charter) and will be available in a number of different weights (70gsm, 115gsm, 150gsm). The idea behind creating the database is to allow the sales team to query the database to find a supplier that can match the customers criteria. I have created the following database tables tblMainDetails AccNo (PK) SupplierName Address1 Address2 Address3 Address4 PostCode Tel Fax Web Email tblContacts ContactID (PK) FirstName LastName Tel Email tblStockingOptions OptionID (PK) BespokeSheets BespokeReels StockSheets StockReels TblQualities QualityID (PK) PaperQuality tblWeights WeightID (PK) Gsm tblPaperInfo PaperID (PK) SupplierPaperName OurName PaperInfo Do these tables look aabout right???? Now comes the bit thats making my head spin Relations, can someone please give me some advice on this.
View Replies !
Table Design And Relationships...
hey.. I’m a complete newbie at designing tables and relationships, so pls bear with me.. Im trying to construct a db that records all the details for an importing company. Background: the company imports goods (shipments conducted by Freight Company), from a Supplier, in Containers, and then arranges Delivery of the goods (delivery completed by Haulier Company). Customs requirements must also be met. so far these are the tables I have: tblShipping tblContainer tblDelivery tblCustoms tblSupplier tblFreightComp tblHaulierComp Here are the relationships: 1)Each shipment (tblShipping) can have many Containers, but each container can only have one shipment (tblShipping) : 1:M 2)Each shipment (tblShipping) can only have one Supplier (tblSupplier), but each Supplier can have many shipments (tblShipping): 1:M 3)Each shipment (tblShipping) can only have one Freight Company (tblFreightComp), but each Freight Company can have many shipments (tblShipping): 1:M 4)Each shipment (tblShipping) can only have one Haulier (tblHaulier), but each Haulier can have many shipments (tblShipping): 1:M 5)Each Container can only have one Delivery (tblDelivery), but each delivery can have many containers: 1:M 6)Each Container can only have one Customs Check (tblCustoms), but each Customs Check can have many containers: 1:M 7)Each Delivery can only have one Haulier, but each Haulier can have many deliveries: 1: M These are the most important relationships (not all of them). Where I’m getting confused is whether or not I should (or need to) create additional junction tables? If someone could have a look at the attached screenshot (of my tables with fields and pk's - but no relationships defined yet) and advise, I’d really appreciate it! Also, im having some major probs with my variables and forms… the P/C No in the Shipping Table is the most important value here. This number is used repeatedly throughout the process. Problem I’m having is that this number needs to be alphanumeric, and also get passed to each other table (hence P/C No as a fk in each other table). How can I do this? Ive been playing around with loads of diff relationships but with no luck. I ideally need this alphanumeric number, when entered in the first form, to be ‘sent’ to other forms, as I chose. How can I do this? Any help is really appreciated! Thanks snowman ps - sorry for such a long post!
View Replies !
Got Problem With Relationships - Design
Hi, I'm trying to design a database for a childrens group but am having trouble entering data now I've created the tables and many to many relationships. I normalised the child registration form which had the usual data but also contained things such as allergies, doctor details and emergency contact details. Now, when I normalised these I came out with a table for each of these 3 things linked to the child table with a many to many relationship. Looks good....but when I want to actually enter data, Access says I need the data in these tables before I can put them in the 'child' table, as these are the primary key tables. OK, most databases would probably require the admin person to select the doctor and an allergy from a listbox linked to these linked tables, but what about 'emergency contact'? There can't be a table populated with emergency contacts, before the registration form is entered. What should I do? Keep the emergency contact and their details fields in the child table and ignore the rules of normalisation?
View Replies !
Form Design Strategy On Schema With Many M-M Relationships
Hello, I have a couple of elementar level questions on Form creation process and standards invloving a schema with several M-M relationships. I have a highly normalized design (thereby producing several junction tables). I have attached a picture of my schema to give an ideal of the schema I am talking about. 1. What is typcially the process of creating Forms on such a schema for the purpose of data-entry. Meaning, should there be several Forms (on the order of # of tables) or should there be a few that enables simulataneous data entry to many tables through one Form. What is typically the practise? 2. If there are 3 tables (A, B, C) so that A and C have a M-M relationship and B is a junction table that makes A &B and A & C have a 1 - M relationship, then how does one enter data for A, B, and C so that all the three get populated? 3. Can someone provide a copy or link to a sample database that has a similar normalized schema and Forms built into it that takes care of the data entry to the various tables? Thank you very much for some light on this path.
View Replies !
Table Relationships
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 Replies !
Table Relationships
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 Replies !
Need Help With Relationships In Table
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 Replies !
Table Relationships
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 Replies !
3NF & Table Relationships...
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 Replies !
Table Relationships
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 Replies !
Table Relationships
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 Replies !
Help With Table Relationships
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 Replies !
Table Relationships
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 Replies !
Table Relationships
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 Replies !
Table Relationships
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 Replies !
Table Relationships...
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 Replies !
Access Table Relationships
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 Replies !
Relationships Table Not Saving.
Hi. I've been trying to create a link between two different tables which i've put on the Relationships screen. Any type of link made between RepairScheduleID on the one table and RepairScheduleID on the other table disappear when I reopen the database after closing access. This happens if I save, if i don't save it requests me to save then doesn't store the changes anyway. Ideas? What on earth is wrong?
View Replies !
Problems With Table Relationships
Hey I was wondering that is it possible that is in one field I select a certain name, say a type of product and that in another field will automatically display the price of that product based on another table. Thanks
View Replies !
Table Relationships Broken
I have a database which has been running well for four years. There are four tables that are linked from one to another in a one-to-many relationship, i.e., Table1 to Table2 to Table3 to Table4. We recently had a power failure and our network went down. Now the relationship between Table2 and Table3 is broken. When I try to rejoin them using the Relationship View, Access tells me that the relationship between the tables is "Indeterminate". I have run queries testing for unmatched records between all of the tables and the data appears to be fine. My only guess at this point is that the structure of one of the tables is corrupted. The problem is that each of the tables has an autonumber field. If I build a new table structure, and append my old data, isn't that going to renumber all of the records? I have run a compact and repair on the database and that didn't help. I have made copies of the tables and tried to use them in the relationships. The copies don't work either. Other than not being able to link the two tables, everything is working fine. Does anyone have any ideas? Thanks, Cherry
View Replies !
Table Is Off The Relationships Page!?
Hi all, I've just gone into the Relationships diagram and discovered that one of my tables is off the page! (see picture). Dont know how this happened although I have noticed that Access has a habit of moving the table around the diagram after being in Design mode. Anyone else had this problem? How do I get the table back onto the page? Many thanks in advance...
View Replies !
Relationships: Can't Lock A Table...
I am trying to define a relationship between tables. The relationship is one-to-many with forcing referential integrity. When I try to define it, I get the dabaase engine error: "The database engine couldn't lock the table, because it is already in use by another person or process" I am sure that the database is not opened by anyone else, it is 100% local, no other instance of Access is running, and there was no *.ldb file... What can be the problem?
View Replies !
Table Structure And Relationships
Hi, Having trouble with relationships and Table Structure for a Database. Please help as am about to give up. The database is to monitor trades. Each trade has a product type for which it has different characteristics (requires different fields) So in terms of fields: Tbl Main: Trade Number (AutoNum) (Prim Key) Trade Date (Date) Trader (Txt) Counterparty (Txt) Allocation (Num) Product Type (Num) Now if Product Type is type 1 then I need: Product Type (Num) (Prim Key) Underlying (Num) Price (Num) Notional (Num) Level (Txt) Legal (Txt) But if product type is 2 then I need Product Type (Num) (Prim Key) Underlying (num) Maturity Strike Opt Type I am thinking of using a table for each product type with all the details but cant figure out what relationships to use and how to structure the tables. I would rather not use just one large table for product and have it contain empty/redundant fields - but if this is the best way to do it then I guess I will have to! I guess because each trade will have a unique trade type and unique details in for the product that a one to one relationship is used anbd maybe using primary key of trade number? All the other relationships I have sorted - just this one. Please can you offer some advice? thanks,
View Replies !
Problem With Table Structure And Relationships
I am trying to create a database that will allow the user to Create new projects and assign them with a contact and organisation. In other words, there should be Organisations that can have multiple contacts linked to it and each contact can have multiple conversations attached to it. An organisation can also have multiple Projects attached to it whioch are in turn attached to a contact. Can anyone help me with the table setup and relationships? Right now I have four tables: tblNotes tblContact tblOrganisation tblProject tblNotes with a foreign key (ContactID) is linked to primary key (ContactID)in tblContact. tblContact with the foreign key (OrgID) is linked to primary key (OrgID) in the table tblOrganisation. tblOrganisation with the foreign key (ProjectID) is linked to primary key (ProjectID) in the table tblProject I can't seem to get the tables to link together for example I cannot get my tblOrganisation to see many projects asrequired. Can anyone help wuth a suggested table layout and relationships? Many Thanks
View Replies !
Access Novice - Table Relationships Help
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 !
Relationships And Table Data Cascading
Hi All I've worked with Access in the past but haven't touched it for 6 years now so I've forgot a lot of the things i've learnt. Bare with me while I try and explain it as clearly as possible I'm creating a Database for work which will register all our Commerical Clients, which projects the clients are related to and which students worked on them projects. So I have 3 main Tables: 1.Commercial Clients Client ID Company Name Contact Title 2. Commercial Projects Project ID Nature of Project (Graphic Design, Multimedia, Media) Project Name Project Information Staff Member Responsible Start Date End Date 3. Students/Graduates Student ID Student Name Area of Expertise Contact Details Last Project worked on Right here we go..... Each commercial Client can have more then one project to their name. Each Project can have more than one student working on it and each student can be working on more then one project. My relationships are set up like this: Commercial Clients <One to Many> Projects Projects <Many to Many>Students What I have done is create forms with sub forms in to enter to extra data. I dont know if this is the right way to do it but it seems to easiest way. Please show me a way that will work better if you know it. Anyway... the way the tables are now I can on cascade the information down through the tables.... it will not cascade upwards. I have ticked everything you can possibly tick for all relationships. So when I can enter projects in the Subform on the Commerical Clients Form and I can enter students on the subform in the Projects form. But on the Student form the projects the students at on does not show up in that subform and entering the day only tries duplicating it (which i dont want to do). If I had a form that showed the projects and the related commercial clients in the subform it wont find the commerical clients. So i.e. It shows fields when the cascaded down like: Commercial Clients V V Projects V V Students/Graduates But it wont show if its: Commercial Clients ^ ^ Projects ^ ^ Students/Graduates Anyone have any ideas how I get get them to cascade upwards? Thanks Pete
View Replies !
Software Inventory Table & Relationships
I am trying to build an Access 2007 Database to record the software licenses held by our company. I am very new to Databases so need your advise. All the database need to do is record the department, the contact for that department, the type and number held, and an automated field that calulates the value based on qualitiy. The structure I have built so far is set out in the attached JPEG and I was wondering if anyone could advise if I am heading in the right direction. Once I have got this right, I will look to build a form to record the Department audit with a subform to record the product found. Hope you can get me going in the right direction
View Replies !
Database Issues, Table Relationships
Hi All, My name is Paul and I am new to this Forum, so I firstly wanted to say hello to all and glad to be here. I need some assistance with a work related database. I am currently developing it as a project but have run into a wall with the design being my first time. I have to create a customer service and invoicing database. I currently have the following tables: Client (Customer No, User Profile, Password, Customer Name, Acc No, Policyholder, 100% Indemnity) Contact (Acc No, Date Signed On, Company Name, Contact Name, Address 1, Address 2, Address 3, Address 4, Address 5, Post Code, Telephone No, Fax No, Email) Product (Product ID, Product Name, Service Level, Product Code, Price, Subscription Details) Client / Price (Customer No, Product ID) Usage (Order Number, Day, Month, Year, Subject Name, Client Reference, Product Code, Company Name, Customer No) I have managed to link Client and contact no problems at all. I have also managed to link Client to Product using the table Client / Price. I have tested this with queries and can get all of the data I need extracted out. However the problem occurrs when I try and link the Usage table to something or other. The Usage table is data that I need to import on a monthly basis from Excel. Once I get it, I need to load it into Access and then generate usage statements for each of the clients to show what products they have ordered and how much each report has cost them. To make matters worse, each client has 14 different products to choose from, and all clients have different prices for each of the different products. Getting this to connect properly is an absolute nightmare. Can anyone assist me in how I might link the Usage (imported table) to the rest of the database? I have attached a copy of the relationships on a word document and wonder if anyone can put a finger on what I might be doing wrong. Hope someone can help.
View Replies !
|
TRACKER
