Products 1-M ORDER DETAILS M-1 ORDERS M-1 CUSTOMERS
I have ORDER DETAILS set as a junction table so that many products can be recorded within one order. All is good apart from when i go into ORDERS and create a new order. I click the subform which links to the ORDERDETAILS. I then pick a product number(look up from products table). The problem is this: In the ORDER DETAILS I want to display the unit price of this product simply by picking the product id.
Eventually this would form the basis of an order form where I can pick Product Id and have it display unit price.
Any ideas on this one, I'm sure its quite simple!!
Attached is my many to many relationship setup. I would like to prevent the possibility of entering the same person more than once for the same training event. I am assuming that I do this by setting an index setting for a field the junction table?
Note : 1) A class can contain one or more students (one-to-many between Class and Student tables)
The table "Score" is a junction table between three tables : Student , Course and Term because it contains three foreign keys ( I could use a combination of 3 foreign keys to make a primary key ! ) .
The tables ; "Class", "Student", " Course ", "Term " already contain data for each table I created a data entry form .
My biggest problem is how to create a form to enter students' grades or scores for each student that belong to his class.
I do not know how to do it especially since the idea is that :
On a form I would like to use ComboBox to select a class that displays student's list from class selected and a ComboBox in same form to select course and another ComboBox to select a term and then enter grades or scores for each student
This is my general idea to enter students' grades. The rules are :
A class contains one or more students Each student takes one or more subjects. Each student gets scores for each subject and each term(quarter)
So I have a form that has a sub form on it (based off of a junction table), indicating which category a particular project belongs to. It can belong to multiple categories:
ProjectID 100 Category Admin Finance
But I want to run a query to find all of the projects that are both admin and finance. When I do that now, it comes up as two separate records in the query, which is fine... but I have to know the projects before hand to find if it's in both, because it returns heaps of records, anything that says admin or finance... and that defeats the whole purpose. I want the query to show me JUST the projects that have both of those in their subform..
I have a front-end database with a linked (data) database. Problems arise when this goes onto other PC's on different directories and I get the error that it can't find the linked database. This is easily fixed by updating with the linked table manager, but as I am not sure which directories the database will be run from on other PC's, is there a solution that easily updates the linked tables? (the two database files will always be in the same folder). I realise that if I can identify a common directory that can be used on all potential PC's, then this problem can be avoided, but I can't guarantee this.
Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are
Can anyone help me understand why I can link to a table that I have permission to update but when I try to update I cannot. For example, I should be able to open the table and add a row but I cannot. I can append to it though. The interesting thing is that someone on a different pc can use my id to link to a table and they can open it and add a row. Are there some seetings that are messed up on my laptop? Please advise...
I'm using the "Linked table manager" to update one of my tables. I added an attachment field to the table, and in order to add attachments I need to refresh the link in my front end. However, when I refresh the table, it prompts me to choose a new location.
I updated several other tables,I'm not sure if I should save it to a new location or what? I don't want to have to create a new location, I just simply want it to refresh the link.I actually deleted a table that had a similar name in my back end, Well I never deleted that table from my front end. I had accidentally been clicking that table to update instead of the one that was actually requiring the update.
I am not sure where to start with this and any help in the right direction would be gratefully appreciated.
I have a linked table to Client's table in Access (accessed via a VPN Connection) for jobs carried out by me. I have to update this table with information from my system (not access) and the information in the fields will change daily as work progresses on these jobs. Rather than going into each record individually, I have the information exported from my other system (in excel format). I have a unique Order_Reference number that will not change but all other fields may change as and when.
At the moment I have simply been deleting all the records in the linked table and re importing them from an excel spreadsheet. Ideally, I would like to just update them rather than delete them first. What is the best way to go about this please?
I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?
If anyone can help, that would be greatly appreciated. Thank you in advance.
I have a form that users enter billing logs in. There is a button users can click to the right of their billing data that opens another form where they can enter details regarding the billing log. Beside this button is a check box that shows them if details for this particular billing log has been created.
I can not for the life of me work out how to get the check box to update with a check after I enter Billing details without opening and closing the form again. I felt I have tried requery and refresh in all ways I have seen here, yet it is not working.
The main form is frmDslEnter and the billing log is entered in a subform from here called tblSessions subform. The checkbox on this subform is called chkProgressEntered, and it's control source is =Not IsNull([DSLIDNumber]). I tried putting the refresh on the Close button on the Billing Details page, on the Got Focus of the subform and the main form and haven't had any luck.
I have created a junction table in my database.the tables being linked are tblplayerInfo to tblGuardianInfo. The junction table containd the PK of both these tables. A player can have more than one guardian (Mum,Dad, etc) and a Guardian can have more than one player (two sons, etc) On my form i display both sets of info. The player info in the main form and the guardian info in a subform. I want to be able to add a new guardian to a player. How do I update the junction table to reflect the new guardian addition, or does Access automatically create the record in the Junction Table (if so How)
I would like to create a database of organizations in my area that provide particular services. I had initially created an Excel spreadsheet, but decided that I'll probably need an Access database, so I converted the format.
I have one table that has the name of the organization, contact information, hours, and other info. In the original Excel spreadsheet, I used strings in two other columns for "languages" and "areas served". Some organizations offer services in more than one language and some operate in more than one geographical area. I've read posts, some books, and info on other sites and it looks like I'll need to use two many-to-many junction tables (one for each of the two remaining columns described above).
I have several questions, and I hope that I'm asking the right ones.
First, my junction tables, when I initially set them up have no information in them when I just open them (when I don't use the Design view) -- they just show the column headings. Is this right?
Also, when setting up the relationships, do I need to check the "Enforce Referential Integrity" box? And if so, it seems like I need to change the "Indexed" property for the PK's of all three of the main tables (not the junction tables) to "Yes (No Duplicates)" -- right?
My main problem is that I don't understand now how to assign multiple languages or multiple areas served to each record in the Resources table. Originally, as I explained above, I had these as strings in a particular cell in Excel (like, "English, Spanish, French"). Will I be deleting those two columns from the Resources table?
I can provide what I've got so far if that would help... and I would greatly appreciate any assistance (explanation, link to good tutorial, example databases, etc.). Thank you so much,
I'm at a place in my design where I don't want to go further without advice for fear of really making a mess.:o
I have Products, Customers, Customer Locations, and Customer Models that can have any number of combinations. I've joined what I think makes sense at the lowest level, customers with locations, and next model with customers/locations.
I think products is the outer most junction, because many customers can use the same product in any number of models and locations.
The attached diagram shows where I am now--multiple junction tables, and junctions of junction tables. Now each of these tables has other relationships to nomalize things, but those are not shown.
Is one more level, or nest if you will, the right way to do this?
I have an events table and a junction table that has a composite key with two fields; those are the only fields on the junction table and are FKs in my events table.
I have a form that fills in events to my events table and also fills in values to my junction table. The control source is a query of the two tables along with a couple of other reference tables for metadata associations. The form works fine... the first time. My problem is that, after I have entered an event that has my unique composite key for the first time (thereby entering a new record to my junction table), once I go to enter a second event that uses the same key, access tries to enter the record again and throwing a "3164: field cannot be updated" error instead of referencing the already created composite key.
I have the form set the composite values to the selected values on form load. Normally, I would just set the primary key to the primary key values and access would follow my logic, but in this case it always tries to make a new record.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
I am trying to set up a Real Estate database, and can't get the Relationships to work. I have three tables:
House Listings Farm Listings Clients
Clients can have properties in both House and Farm Listings Tables, so I need a Junction Table (or do I need more than one?)
The Primary Key fields of House Listings Table (HouseID) and Farm Listings Table (FarmID), become a composite Primary key in the Junction table. Is this correct?
Both the House Listings Table and the Farm Listings Tables have a field for Client ID. Is this causing me problems, as I can get the Farm and Clients Queries to work, but not any queries with the House Listings. As this is my first attempt at creating a database with many-to-many relationships, please explain in simple terms what I am doing wrong.
I'm working in the petro-chem and industrial service industries now, and am finding there are relationships I haven't had much exposure to in the past.
Can someone please help me with constructing the tables and relationships needed here?
This is something I should probably know, yet I've never created junction tables which must take into account nested data. I have created a relationship which works, but I feel it could be done in a simpler fashion.
By the way, I cannot find Pat Hartman's many-to-many sample db. It is either missing, or the restrictions on word length in Search just aren't letting me find it.
Table: WorkOrder is the primary table I'm working with. I need to store 4 pieces of information for every WorkOrderID, two of which have subs.
I’m having trouble defining Relationships I’m thinking I need a “Junction” Table and I have tried looking at the Orders.mdb but it hasn’t helped (I’m sure I’m just missing something) I just don’t see how it works. If at all possible please don’t just give the info try to help me understand so I can get the answer myself. Here is what I have: Far table: FarNumID (PK) > autonumber FarNumber > Text “224-10C” FarTitle> Text
FarParagraph table: FarParaID (PK) > autonumber FarNumID > Number FarParaTitle > Text FarParaText > Text
AC table: ACNumID (PK) > autonumber ACNumber > Text ACTitle> Text
ACParagraph table: ACParaID (PK) > autonumber ACNumID > Number ACParaTitle > Text ACParaText > Text
1. Each FarNumber can have only 1 FarTitle 1:1 Each FarNumber can have many FarParaTitles 1:Many Each FarNumber can have many FarParaText 1:Many 2. Each FarTitle can have many FarParaTitle 1:Many Each FarTitle can have many FarParaText 1:Many 3. Each FarParaTitle and have only 1 FarparaText 1:1
All I ever seem to do is sit about trying to figure this out and then give up and ask for help :(
I have three tables.
One for contacts One for groups (groups like people attending meeting a, b c) and a junction table as the top two create a many to many relationship.. one person can be part of multiple groups, and a group can have multiple members.
In the contacts table my primary key is an auto number, and is contactsID I also have some contact details, and a groupID field
In the group table my primary key is GroupID and is an auto number, it also have group name, and description
in the junction table I have a primary key, then GroupID, and ContactsID which are also set at primary keys and are set with the same values as the same named primary keys in the relavant tables (I beleive this defines them as foreign keys... i hope so anyway!)
I have the relationships set up as a one to may relationship from contacts to junction and groups to junction, contacts linking from ContactID to ContactID in each table, likewise with the Group table.
However, when I try to put everything into a main form for contacts,with a subform for groups, all I am getting is the autonumber... which isn't much good for my end user..... :( How do I solve this?
I have so far only worked with single tables and queries but not with linked tables. Now the following problem has arisen.
We have a database all of whose fields except two are of type = Text. Two fields are type = Memo.
We have to submit this database, i.e. its main table, to an external company for automatic 'cleansing' (UK: Telephone preference service). The cleansing program converts the cleansed database into a comma-delimited text file and returns it to us as such, and we then have to convert it back into an Access table.
(That's the way our supplier works, it is not under our control, and we have to learn to cope with it.)
Now the cleansing program is being screwed up by our Memo fields, because they contain commas. So when the cleansing program sees a comma, it interprets this as the start of a new field, and the same happens when we convert the text file back into a table.
Two solutions were suggested by the cleansing company, but only the following seems suffiently automatic and therefore acceptable to us.
I NEED HELP IN IMPLEMENTING THAT SOLUTION.
-----------------------------
I want to move the two memo columns into a separate table (memo table), linked to the original table (main table). Have the ID column (unique identifier) in both tables to keep the records together. Then I send only the main table for cleansing and the commas in the memo can no longer work havoc.
I have an idea of how to create the memo table: make a copy of the main table and then delete all columns except ID and the two memo columns.
But then I have to link the two tables (I do not know how to do that); where do I start.
I must also ensure that when new records are created (now usually through a form), both tables are expanded. And I must get information from both tables into one form.
At present I do not even know where to start, except for creating the Memo table by copying it and deleting certain columns.
I have a good book (John Viescas: Running MS Access 2000), but it is huge, and the problem is urgent (it stops our tiny company from marketing). I have no time to study the whole book to find what I need in this case. A chapter or page reference would be very useful.
Or some Tutorial on the web that deals with my particular task.
Since last week I am working on a Database which I will use for my company. To make a long story short, I didn't find any appropiate softwares nor examples on the internet, hence, with my some knowledge in Access, I am trying to make this "to be" useful tool for my self.
Short brief on the project: This is going to be used for a trading company to register: - Suppliers -- Products
- Customers -- Inquiries -- Orders -- Offers
*Relations: - Suppliers can have one or many products - Customers can have one or many Orders / Inquiries / Offers
Problems: (I have tried many different ways, but going nowhere)
1) The "Customers" table have 3 tables (Orders/Inquiries/Offers) linked to it - and all are based on the primary key "CustomerId". --> How can I make a form to enter these data linked?
2) Under "Orders" I want to select supplier from the Supplier table and Product from the Product table -- How?
I know this is a lot of info and probably too vague, but if someone could assist me with a few problems - then I would really really appreciate it and compensate somehow.
