Im trying to make a simple database in access , so far all is going well but im stuck with the inventory.
I have "products" table with Id [autonum],name[text],price[money], provider[number], stock[number]
Providers table with Id[autonum] ,name [Text],tel[number],
Employes table with Id[autonum], name[text],tel [number]
and i have Sales table with Id[autonum],date[date],employer[number],product[number],quantity[number],total price[money]
I have relationships between table.provider and provider.name then sales.employer with employer.name and sales.product witht products.name.
Now what i want to do is that every time that i make a sale it modify my stock number on the products table. I think the proper way is just to calculate stock every time i need it but i dont know how to do that either :D
Ill upload my db but i have to tell you that is in spanish so if you haveany question im here to answer.Thanks a lot to all
I have created a relational dB (picture included of layout). When I try and create a query and include tblAssets and two or more tables that is in a one-many relationship with tblAssets linked by the AssetId field I can not update any records in the query, wheter it is through a form or query.
I have no idea why this is happening, I am suspecting that I can not/should not be creating multipule tables in a one to many relationship with the same key?
Hey everyone. Does anyone know how can I make the latest 2 dates from a specific date field appear on the screen? What should I ask in the criteria?Is there a certain function or something else?
I am pretty sure that this is crazy! I have attatched a word document (the quickest way for me to show and explain at the same time) that shows in a print screen picture what I want done with this form. There is colored text explaining. Any questions feel free to ask. I am pretty sure that this is ridiculously tough and am not sure that it is even possible with access. Please help, and may the gods give tons of wealth to whomever can solve my problems.
I have a group of customers in a database (aprox 600) and a small group (<10) of people who service those customers. I am able to run a report for each service person which lists all the necessary customer info for any particular day and gives me a total on each report of the number of customers being serviced. What I want to do is generate 1 report that lists each service person and the total customers for that person. I would then like to have the total customers being serviced also on that report. I think part of my problem stems from not knowing enough about access when I started. All my customer info is in 1 table. Several of my service people are handling 2 groups of customers (distinction between groups is geographical). I over came that issue by combining my customer groups when I created my queries for each service person. I feel like I'm too far in to vevamp the whole system as I have forms created to input data and print reports with the help of macros. I did figure out how to create a report that has the customer totals, but the report shows those totals by geographic area and not necessarily by service person. The commands I used to get this are in header area of the report. Because of this it seems that I can't get a customer total. Any suggestions would be helpful. Thanks!
heres one that just don't add up from the beginning, i've got a form that puts data into a table from a text box named txtcomments, the table allows 255 chars to be stored the comments are show later using a copy of the input form, all this works great. now i'm taking this data and inserting it into another table using sql inset in the vb editor so i can play with it and leave the original in tact everything works fine on my test machine but when i transfere the database to the server i am only permitted to enter 127 chars in the text box i've narrowed it down to the sql statement and know that where the error is occuring ive tried deleting things and reinstalling im just going round in circles and when i transfere the database back to the test machine everything works fine the test machine uses access 2003 but the server uses 2000 this is the only differance
any help would be god like
john
ps sql statement as follows looks fine to me,
DoCmd.RunSQL "INSERT INTO tblHistory VALUES([txtcomments]);"
I have a problem that seems like it could be very similar to this post from a few years ago http://www.access-programmers.co.uk/forums/showthread.php?t=46645&highlight=vba332.dll My problem is I dont know how to use Access other then open a program. This is for a customer and they dont know how to do it either as someone else devoloped their database. Basically it looks like the database is corrupt but I can not open it using a newer version of access because it has some permissions and I dont know how to go about making my access "look" like the other computer. Any help is appreciated Thanks James
I created a database for my company and it works very well but, I need it or something to track the way I pay comisions. Its a type of multi-level company and pay rates vary. Also, once someone hits the top level they now join in profit sharing. The profit sharing part is the tough part becase say person A is at the top he is a team leader and he gets money not only from his sales, but also from the people in his group below him. It must also be able to pay the correct person if someone quits, say Person D, the system knows that it all off person D's people under him go up to person C now. I hope this isnt to confussing, but im starting to confuss myself as im trying to set this up.
Is access able to keep track of an organization like this or should I try to use something else?
What I want to do, is have an unbound form, user enters data in to it, then clicks on a 'save' button which then writes the data to a new record within a table
I have got no idea what it is called so find it hard to do the search.
I ve performed a query on a table and now have a set of results which is fine for example £1.56, £1.98, £87.90 (ive set the values in to a currency format), now i want to calculate the total so i have run another query on the last and used 'sum' in the 'total' this calculates the value but rounds it to the nearest integer for example 91!
the thing is i have done this many times before and havent had this problem! i cant understand it, its seriously doing my head in. Ive tried allsorts including the 'round' function which doesnt alter it.
I have created a database in which I have three Fields. 1. Date Joined 2. Renewal Date 3. Member/Renewal
When I type in the date a person joins our club, I have made the renewal date come up displaying a year ahead, when renewal of membershiip is due.
I would like to be able to put in the date joined date and have the field 3 show Member and then when the renewal date is reached, to have in automatically change to Renewal.
It is probaly a simply formula but I can not get it right.
I have installed on my PC, WindowsXP sp.1, MSaccess versions 97 and XP. After I have tested some databases, MSaccess 97 don't works successfully more, but XP version works good. I can't modify files mdb. Access works like in run-time mode: bars and commands reduced, standard icon of access absent, database window absent, ecc. If macro autoexec or a form that play at the start of mdb are present, mdb works but it is impossibile to modify it. Otherwise mdb dont works, I can see access window with only menu files and window.
I have removed access and after yet installed it, but it don't work successfully.
maybe other application leaved files (or modification in file registry) that install procedure of access97 cant rewrite? And that dispose access 97 to work in run-time mode (or like)?
I am hopeless. Can You help me? Can you give me a list of files to remove, or list of modification to do in registry?
please can some1 help?? i created a form using wizard to display the information in three tables but wen the form is in form view theres nothing displayed yet in design view all the information is present. please please some1 help:(
I have a variable cost that is a calculated field (as in the colum only exists in a query not as a colum in a table) that is variable hours * variable rate....the problem is ppl at my company sometimes dont know how the costs are goign to be broken down so they only put a Variable Cost....
and since they dont know the rate and hours my calculated field returns nothing...
how can i accomplish this, VBA might seem the way to go but i don't know how to use it for Access.....
so basically i need to calcualte the variable cost only if they enter something in var hours and var rate...other wise the var cost should just be what the users type ( so i i guess i have to make Var Costs a column in the table to give users the option of entering it)
I have a qry I need for picking the info for sending a series of letters out. I finally got it to work and now Im wondering if there is a better more efficient way. The qry I call to generate my report calls on information from 10 other queries and 7 tables. Some of the other queries are needed for other sub-forms that rely on the data. Is this something I should change? or just be happy it works??? -Dan :D
Hello, Really hope someone can help me. I have 2 lots of info in 1 table - nameley customer address & posting address
I need to print a form that always has the posting address on the same side. but in some cases if the postal address is the same as the customer address then it is not filled in. hope this makes sense:
I'm trying to save a change made to a sql statement in an access query but everytime I save it and close the database, when i open it up back again, the query returns back to the old unchange sql statement like it never got saved. I checked the user permissions and they are all checked (no restrictions). Any help would be greatly appreaciated.
ok.. here is a pickle..my table layout is like so..http://www.londonheathrowcars.com/hoo.jpgthe job table stores the main job details.. every job will be in this tableSOME jobs will have incoming flight details... some wontSOME jobs will have outgoing flight details.. some wontSOME jobs will have incoming AND outgoing flight details.. some wont have anyalso SOME jobs (credit card jobs) will have credit card details.. some wontthis is the general problem i am facing..there are 2 records in my job table..jobref no. 020jobref no. 021jobref no. 020 has incoming flight details.. outgoing flight details.. and credit card details.. which means... the job ref no. 020 is present in all 4 tables..jobref no. 021 has incoming flight details.. it does not have outgoing flight details or credit card details.. which means the job ref no. 021 is present in the job table and the incoming flight table onlyi want to create a form that displays every field in the database..my idea is to make a query using all four tables.. and then the form will display the results..HOWEVER!!!!!!!!the form will only display the job ref no 020.. because job 021 is not present in all the tables...why.. is there a way around this.. cant it just display both records and if job 020 does not have card details.. just leave those fields blank.. then on this form.. if i happen to add card details to job 021.. it will just create the record in the card table using the same ref no..is that clear.. someone please enlighten me
http://i73.photobucket.com/albums/i236/antmoran/databas.jpg here is a screenshot of 2 of the tables in my database. i would like a query that would count up the amount of times the clientno appears in clientmeeting table. e.g c001 appears twice in the clientmeeting table and i would like to show this in my query. But also i need it to be general so that it will work for the amount of time c002 c003......appear. does anyone understand what i mean and know how to help?
Hi all, I have a form that is used to Enter and Edit records. At the end of a record is a "shipped Date". This is blank until the item ships. I want to make the entire record editable up until a date gets placed in this field. Is this possible? and can editing of the record still be done maybe at the table level?
When I navigate from one record to another record in this form, the values in the controls take a looooong time to populate (most of the time they come up as blank and when I move my mouse over that control - the value gets populated....this is slow !!!).
I believe this is happenning because I am using DSUM function all over the form.
What is the fastest way to display this control WITHOUT using DSUM?
i'm writing an Access database to quote for windows & doors, there are many variants like style, width, height, glass types, security specifications and about 4 other options.
what would be the best way of working out how to add the extras for each option,
The price would be based on the style first, then depending on width and height, then on what options were ticked . i.e securtity spec yes/no, then on what glass type, the price gfor that would be based on the entered width x height etc.
is it possible to do a look up, like you can in Excel where it looks at at grid/matrix based on style number and width x height to get the price, but then how would i get it to add the other options which will depend on size and number of openings.
I have made a query with to take the percent difference of two mileages and then if they are greater than 2.5% to put a 1 or if not a 0. I try and put a 1 in criteria in order to only see the ones that are changed by greater than 2.5% and I get the divide by zero error. Any ideas how to fix this problem.
