Relating Assembled Part Numbers To Their Components
Feb 10, 2008
I didn't know which forum to post this in.
I'm stumped.
The inventory table has both cabinet assemblies and their components (tblInventoryMaster) in one table and a table that has what components go with each assembly (tblInventoryAssembly)
When a cabinet is sold, I need the ordering process to commit the components to ship (but still show the cabinet only on the order)
I've attached part of the database in question (Inventory tables, Order Form & relationships)
I am pritty new to access and have been tasked with creating a database ! I have two tables one called main which has 26,000 claims (listed under there part number). The other table called part numbers is a list of all the part numbers in the main table but with no duplicates (126 part numbers) I need to somehow count how many times a part number apears in the main table. Maybe display a count in the part number table as a extra field ? then i could put this field into the form i created to browse the parts numbers (to show how many times the part number appears in the main table)
We are trying to use Access to generate, job, task and craft numbers in Access. Job and task will be unique. Craft numbers can use the auto number function. We need the numbers to be 4 digits for Job and Task.
I need to get an average repair price of multiple part numbers with the same root number i.e.
8 each 8TJ124GGN1 4 each 8TJ124GGM1 7 each 8TJ124GGP1
First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.
If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages. HOWEVER, if I use the expression:
…to get a more accurate average by throwing out the high and low values, I get a:
“…expression too complicated to be evaluated”
when I try to average the averages in the second query using the AVG function.
I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.
I have made a database for use by some of our sales and marketing team whom all have laptops. The database works fine on my PC, but when I try on their Laptops they do not appear to have the correct VB library's installed. The follwing does not appear to work:
Transferspreadsheet Date Left([Field], 2) Right([Field],2)
There may be others also, but these are the ones that have not worked so far.
I can probably get our IT Helpdesk people to reinstall it on their Laptops(in about 6 months time) with the correct options selected, but as a quicker measure, does anyone know which .dll or .ocx are used by access, so that I could just send them a copy of mine, to see if that resolves the problem.
I have a form with a combo box (sup_numbers_combo), which has a list of supplier numbers in it. (on a master form).
In a sub form, i then set the row source for a combo box (product_numbers_combo) to this:
SELECT [product_number] FROM Supply_link WHERE [Supply_link].[sup_number]=[FORMS]![myForm]![sup_numbers_combo];
Hence, "Sup_number" is a PK in Supplier and an FK in Supply_link.
I want the product_numbers combo to be filled with the values of product_number in Supply_link, where the sup_number is equal to the number selected on the form.
Im pretty sure I have the query correct. If I enter it as a query and then force it a value for: [FORMS]![myForm]![sup_numbers_combo], it works fine.
However, when I set it as the row source for "product_numbers_combo", "product_numbers_combo" is never populated with any values.
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
I have the following form and subform combination:
The subform is linked to the main form by way of the "ProductID" control. (It's hidden in the subform).
Basically, each product (the finished article) is made up of many components.
What I would like to do is this:
1. Increase the stock of a "Product". 2. The Stock level control on the main form will then increment as much as selected 3. Contrary to that, I would like each component to decrement the relevant amount based on how much of the component is used up to make the product.
E.g. If I have Product A and it contains 2 components of which component A is used once per product but component B is used twice, then when I increase stock of product by 1, then component A goes down by 1 and component B goes down by 2. But If I put two of a product in to stock, I would obviously need component A to go down by 2 but component B to go down by 4.
I am thinking I need some way to loop through each record in the sub form for the current form, such as "Do While Not", or a "For Each". And then stick an Update query within the loop, but to be honest I can't think of how I need to address it!
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) ) However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
I have a table of guests that holds all of their details such as name; address; medical info; family background; etc. What i need to do is to hold on the database if they would like to share a room and if so, who with.
I thought it would be possible to lookup the names of guests in the same table but access tells me otherwise.
I thought about creating a linked table of my guests table but when I link person A to person B then person B is not linked to person A which I would like to be the case.
I thought about creating a table called tblShare to hold 2 names from tblGuest but im not to sure how I would make this work. I think I may have the same situation as above. If I go to person A and link them to person B would this show up on Person B? I'm not too sure.
I will try this out now. If anyone has any other suggestions or some guidence on this I would be very gratefull.
I have a table with the following fields: Login ID, Description Password, Login Type, Location, Status. For every Login ID there are some old passwords that had different effective dates. I want my form or table to behave so that I can enter all the old passwords with the effective dates and keep adding to this record. I know it has something to do with subforms but I don't know how to do it. Please help. Thank you Yusuf
I have a main form, Contacts, for the user to input all the necessary data about a contact. What I'm ultimately trying to do is also include a subform which contains the name of each program (there are 12 or so) with a true/false checkbox. I'd like it to work so that if a program is added, it automatically gets added to the subform with a checkbox.
Also, I'd like the results of that subform to get dumped into tblProgram_Contacts.
Sigh...
A great vision... but I really am not sure on how to do any of this (specifically, properly defining the relationships and properly constructing the subform). Any suggestions would be greatly appreciated!
Hi all. This is my first post on this particular message board. I'm having a little problem getting Access to do what I want and hopefully someone here can help me out.
I have 2 forms. One larger form called RTV and one smaller one called Product Line. Now, most of the fields that are in the Product Line form are also in the larger RTV form. What I want, is for the product line people to enter information into the product line form. Once they're done, they exit the database and then, later, their managers look at this information in the product line, verify that it's correct, and then hit a command button that transfers all the fields that are the same on both forms, to the RTV form.
For instance, a prod line person would come in, fill out a date, part number and PO number and hit add record. Then, a manager would get on that same form, make sure everything is ok, and send the data over to the end of the RTV table.
Also, the product line people should not be able to see or edit the RTV form. Only a few people should have the priveldge of seeing both forms. Anyone have any ideas?
Hello, I have a pretty basic question about creating relationships in Access. When in the relationship window that shows all tables and associated relationships, I am a bit confused as to whether you are to drag the parent field to the child field or vice versa. When trying both, it appears to create the 1 to many relationship in the same direction no matter what way I create the relationship. So I am hoping someone will explain this a bit further to me or at least direct me to a reasource to clear up my, and possibly others, confusion. Sorry for my ignorance on this one! :o Thanks a lot, Dana S.
Hi I have tried searching for an answer with no luck. I am writing a table to do a filing cabinet archive.
I have a table with 3 fields in one is box no, second is item no and third is a description.
I have the first 2 linked as a primary key. The description is just text. I want to be able to enter a box no and to have the second (item) to enter a number automatically following a sequential no which is relevant to that box. ie box no Item No 1...........1 1...........2 1...........3 2...........1 2...........2 3...........1 1...........4 this one takes the next no available for box no 1 2...........3 and this one does the same for box no 2
if you go back to a box and start reinputting it picks up the next number. should i sack trying to do it with the autonumber?
I am fiddling with a DB right now just to try to work out some table relationships. I have a PK, Name, it is accompanied by a year in school. I then want to relate the year in school in a different table with the requirements for that year.
such as
Year Requirement1 requirement2 requirement 3
these would be check boxes (yes/no), and I could accomplish it in this format, but I am trying to have a normalized DB so I realize it should be like this
1st year requirement 1st year requirement 1st year requirement
but I do not know how to link this back to the PK name when switching it to this format, so that I can pull up a form that shows if the person has completed the requirements for the year they are in. I am I going about this wrong? I have been looking at normalizing DBs and relationships but can't figure out the logic for my DB thought.
I'm having a bit of a problem figuring out how to do this...
I have a form (single form) and a subform (datasheet which is based on a query which includes the data elements [via outer join] in the single form). What I'm trying to do is have the data sheet form include (view only) all records from the query and have the single form display selected fields for edit/entry (will update a table that is the outer join in the query).
Example:
Table A has "Policy Number", "Line of Coverage", and "Insured ID". Table B has "Policy Number", "Insured ID", and "Insured Name".
Query 1: SELECT A."Policy Number", A."Line of Coverage", A."Insured ID", B."Insured Name" FROM "Table A" A, "Table B" B WHERE A."Policy Number" = B."Policy Number" (+) AND A."Insured ID" = B."Insured ID" (+)
(+) means outer join (sorry i'm used to using oracle).
So in the form I have a single form (FORM X) bound to Table B and a datasheet subform (FORM Y) bound to Query 1. I would like the user to be able to edit (or add if it doesn't exist) the "Insured Name" in FORM X based on the record currently selected in FORM Y.
Other things that may monkey with the process are the fact that even though Table B will have a unique key based on "Policy Number" and "Insured ID" Query 1 (and Table A) may have multiple "Line of Coverage" based on a single "Policy Number" and "Insured ID" combination.
Alternatively not relating the two forms, but simply populating the FORM X based on the record selected in FORM Y would be sufficient. Would this involve populating FORM X using the "on focus" function and how do I use vb to reference to a specific record in my subform?
Please let me know if I am not making any sense and thank you for your help.
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
I want to relate two records in one table, a parent child relationship. I can accomplish this task with two tables I realize. I'm wanting to write a database that will map a family tree. Internal to one table I want to be able to establish a series of relationships, parent-child, sibling, et cetra. Can this be done?
In Filemaker, when you open a table in datasheet view and then select a record you can then open the form relating to this record. Can you do this in Access - I can't get it to work.
I have 3 tables joined as attached and Im trying to use the PO from the PO_Detail table to display the Description from the Material_Req Table. The two values are linked as the description in the Material_Req Table is for the PO in the PO_Detail table but I just cant get the results to show this.
