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 am creating a database that tracks reports that are generated from people in the field...The statistics are tracked on a 28 day cycle, beginning on a Monday and ending on a Sunday. Every week the 28 day period loses the first week and gains a week on the end. I need to develop a way to generate the queries for the reports based on a clock that automatically "calculates" the current 28-day period.
I am trying to make a simple inventory control system. This system will only record input of products and output of products. Explanation: This is a catering service company. I buy tomatoes, rice, oinions, Oils, meat, ect. When we buy this products they usaly come in different packages. The rice can come in 100 pound bag, 50 pound bag or just by the pound. The onions can be bought by the bucket, half bucket, dozen, half dozen ect. The oil can be bought by 100 gallon barril, half barril, Gallon, half gallon, pint ect. The meat by the pound.
I am able to make the a simple invoice and PO application, the only problem is that I want to be able to select what type of packaging the product has and the invoice or PO subforms.
I started with a simple product table, I than created a packaging table and a package detail table. This gave me the chance to create a subform on the product form where I can choose what type of packaging this product uses and also tell it how many units the package has.
So far so good. Now I created a Chef table "this is my Chef to whom I give the product to for cooking" I than created an authorization slip table "this is like an invoice table" and ofcourse a authorization detail table "this is where I pick the products that I give the cook" once I print this out the chef will be able to go to the warehouse and retreive the product.
What I want to do in the detail subform is to be able to pick the product and the type of packaging that I am giving them from a dropdown box then it will automaticaly show only the packaging I assinged to that product and give me the units . This way if I pick the type of packaginh name 100 pound it will put 100 units in the quantity field automaticaly.
So basicaly is an invoice form with its detail but on the detail I am able to pick the product and the packaging that I am giving the chef.
Can you guys guide me on the right direction on how to do thsi?
Hi. - Please do not lecture me: I inherited this database and it's table designs.
I have multiple tables, each containing a SSN text field and multiple other text fields. There is some overlap in SSNs among tables.
I need to create a report with a subform for each table.
The user would ideally be asked for and enter a specific SSN and a specific string. The result would be the report showing the SSN and each table's field that contains the string. ie, parameters would be: Specify SSN and for the string would be: Enter TEXT VAUE. SSNs are simple enough, but the text string entered would need to be, for example: LIKE "2400*"
How do I do this easily so that each text field in each table is searched for that string, without having the user re-enter and re-enter and re-enter... it multiple times? (I do not know very much VBA, so other ideas?)
A friend of mine who is in real estate is looking to learn about Excel. My knowledge is very limited. I can Conditional Format, IF, VLOOKUP etc but I have no idea what can be done with different Sheets in a file.
For example, in one set of data he has it is details on properties for rent. Sheet1, Sheet2 and Sheet3 are for 1, 2 or 3bedroom properties. I assume if a 4 bedroom property is for rent then Sheet4 would be done.
With Excel how easy (or hard) would it be to link data between the sheets. For example, show the properties where the lease is about to end (or whatever) and whereby properties that met the search criteria would comprise 1, 2 and 3 bedroom units and hence be in all 3 sheets.
If what is in 3 sheets was placed in one sheet can Excel do much with that or are you stuck with data that is spread over what appears to be several acres:D
If you can do this in Excel would you need to learn a lot about Excel. To put it another way, this is very easy for anyone here to do what he wants in Access. However, for someone starting from scratch would Excel allow him to get up to speed and functioning easier and quicker than Access
I have supplier records where the supplier name in each record is different. It may be Supplier ABC, AB,C, ABC!, ABC Inc., or several other variations. In addition each of these suppliers has their own supplier ID. The supplier ID can be one of 5 different versions since the data is from 5 different ERP systems. I am trying to create one table that does not have the supplier listed many times, but has one correct name and one ID that will be able to link back to the original file with all the different names and IDs. I hope this is a good explananation. Any advise?
I have always had trouble with this and no matter how much I read I cannot get a visual picture of how to do this.So if any of you could show me how this can be done. It would be appreciated more than you can ever know.A company has many employees.Each employee can have many certificates.So how do I set up the tables and the RELATIONSHIPS(this is the part I can't understand), so when I create a form and enter the different types of certificates they all go to the right table?This is what I have so far.Employee TABLEEmployeeID(PK)EmployeeFirstNameEmployeeLastNameEmployessAddressEmployeeCertificateOneEmployeeCertificateTwoEmployeeCertificateThreeEmployeeCertificateFouretc....toEmployeeCertificateEightCertificate TABLECertificateID(PK)CertificateNameCould someone explain in detail how I can do this?I really would like to understand this.I'm sure it is simple but for some reason I just can't get a visual picture in my head on how to do it.I'm a visual learner, I have to see it work and then read how it works to fully understand how something works.Thanks in advance.P.S. I forgot to mention that I need DATE fields for the Employee Certificates because they expire after a certain time.I was not sure where to put those.
I designed a form to provide options for a group of reports. Each option when clicked, runs a specific report. On my machine when I created and ran the form, worked very well. This database is on a shared network drive with few other people having access to it. When these other users opened this form (they don't have problems running ANY other form), they got errors such as "Undefined Format function in the expression". I checked on their machines, the Access version and permissions of the database (I haven't set any special permissions on this database at all). It is same as mine. Please help me. Why is this happening and what should be done to avoid it? The database opens in a default shared mode.
I'm really sorry to bother with my stupid questions, but I'm definitely not a programmer, and after reading about Access, SQL and ASP so much, I feel really confused...
I've already explained what I want to do in the SQL server forum. It's for an experimental project, I need a database that keeps only 100 records, and every new entry of data replaces the oldest record. So in the end there are always only 100 records.
Well, Lauramc nicely gave me this code that should do that:Code:CreateTrigger YourTable_Trigger1On YourTableAFTER INSERTAS DECLARE @OldestID intSET @OldestID = (SELECT MIN(YourTable.ID) FROM YourTable)--Table name is YourTable, with a primary key of ID.IF (SELECT COUNT(YourTable.ID) FROM YourTable) = 101 --You can also use @@RowCount to get the number of rows.BEGIN DELETE FROM YourTable WHERE YourTable.[ID] = @OldestIDEND
But all I know is how to do is put some simple SELECT statements in an ASP page to call a csv database.
I've realised that I might need to use Access but I don't understand anything.
- Should I use Access and if yes, where am I supposed to copy this code? Should I bring any modification to it?
- Apparently it's possible to create a database from SQL, does it mean not using Access? Where should I type the code? In what sort of file?
Please please, can someone tell me it's actually very simple?
Hi. Back again with a badly designed number of tables that I inherited. Anyway...
I have 20 tables each with an ID field and a dozen-or-so text fields. - (I planned on using a master report with sub-reports, but perhaps I am ahead of myself here.)
I need the user to enter the ID and a value which would only appear in one of the dozen+ fields for that record, in that table. I set up queries with parameters for the ID and the desired text value. However, when run, I get the correct client record, but also all of the fields for that record... Not only the field with the desired value.
Is there a 'simple' way to do this knowing ho wmany possible fields froma ll of the tavbles would have to be searched? And not all tables have the same field names!
I'm trying to sort a combo box alphabetically except for one value which is always at the top of the list.
The row source of the combo box is a query. record to remain at the top of the list is static- same value and ID number always. I've fiddled around with a few things, but at the end of the day, i'm just not smart enough! (or its not possible -which i doubt).. any suggestions? cheers.
I am now right at the last fence in what has been an "interesting" assignment! Sending an attachemnt from Access through winfax!
I have found the following excellent code but, the problem is that the documemnt appears in a "preview" kind of pane, I just want it to go ahead and send! Everything else works fine
Any sugestions pls?
Many Thanks Andy
Public Function SendFax()
'On Error GoTo ErrorHandler
Dim strRecipient As String Dim strFax As String Dim strattach As String
'Test for required fields strFax = Forms!frmFinanceProposal!Text1077 If strFax = "" Then MsgBox "Please enter a fax number" GoTo ErrorHandlerExit End If
'Start DDE connection to WinFax. 'Create the link and disable automatic reception in WinFax lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL") DDEExecute ChanNum:=lngChannel, Command:="GoIdle" DDETerminate ChanNum:=lngChannel
'Create a new link with the TRANSMIT topic. lngChannel = DDEInitiate("FAXMNG32", "TRANSMIT")
ok I have an access db which is all on access. the dbase has been operational now for 3 years and working well however, do to business needs and changes I'm trying to figure out a better way to collect and share data. So the backend of the database is stored on our local office server when users log on to enter or review data they must be in the local office. We now had the need to access the database remotely from customer sites. Now the way this works is we use a local dialer to connect to our company's network and then are routed to our local server (you can imagine how slow this is) it is impossible to operate the current database this way. So I have been reading different posts and different options (front page, sql, asp etc.) What I would like to do is convert my dbase to a program which can allow input and review of data both remotely and locally without a huge speed loss. What is the best approch for me?
I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?
With rstComment .AddNew ![CommentNo] = DCount("CommentNo", Me.OpenArgs, strCriteria) .Fields(strItem) = nNumber ![ProgressionID] = Me![cboProgressionID] ![Comment] = Me![txtComment] ![PersID] = 0 'Will be changed by SQL Server ![Date] = Now() 'Will be changed by SQL Server .Update .Close End With
This code is used in a form that is accessed via a main form when a progression is selected. The form that opens allows the user to selct a type of progression and to enter a comment. This data is then transferred to a table.
This form is used elsewhere in the database and works fine. The code is exactly the same. The form name is different and the table it points to is the same design of table as the other but obviously named differently. i am confident it is referenced correctly. But you lot are the experts.
I am not trained in VBA and have only just started to understand bits of it.
Action: 1) Update the blank fields in a table + add newly imported records but...make sure that there are no duplicates. I need to Append the records from a table called: XLS_Imp_11_27_07 to my main Table " Invoice Tracking for A/P 10_30". The fields found in the XLS _ table: Release Dt, Entry Dt, Liquidation Dt may have been populated by a live report found on Internet. Since it's a live report, when Appended, to the other table, it will create numerous duplicates. The fields found after the 7 first fields in the Invoice Tracking Table may have been updated by users
Is there a way to avoid duplicates and get the data from one table to the other without wiping out whatever is already filled-in? :(
I am completely new to Access but probably all I will need is a gentle push in the right direction. I'd like to think I'm fairly quick at learning new things. Thank you in advance, here's where I am, (first step anyway):
Table 01 Parts List ID - name - cost
Table 02 Item List ID - nick name - description
Table 03 Items to parts relationship (this is where I fall apart) ID - Item ID (relationship to table 02) - Item nickname - PartID (relationship to Table 01) - Part name - Part quantity.
What I am trying to do: I resell items in table 02 which are built of various parts from table 01. I need to generate a table that says: Item 1 is built from 4 of part 1, 2 of part 2, 9 of part 3, and the total cost of parts for Item 1 is x. I want to update the parts list cost on a regular basis and have the item price change reflected.
If I can understand how to make this work, I can ultimately add in labor and overhead etc etc. Baby steps for now.
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.
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 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.
