I have two fields in a query, [Stock] and [Minimum_Stock]. I want a new field called "Order", that displays a "Y" if the Stock field is equal to or less than the Minimum Stock Field.
Can you please explain how I set this up inside me query.
Thanks
Hi all,
I am a novice database user, having only ever used simple databases.
I need to produce a database which stores the path to images.
I want to design a database with a table of Items with fields such as :-
ItemID
ItemName
ItemDesc
ItemCategory
ItemPhoto
each item may have many photos of the item, but I will not know the exact amount, although probably less than 10 photos per item.
Photo will have details (fields) such as :-
PhotoID
PhotoName
PhotoPathURL
PhotoDesc
I realise that each item can have many photos, but a photo can only have one item.
Would I need a table to link these two tables?
Your advice greatly appreciated.
Hopefully someone can assist me. For an advanced user, this will probably take only few minutes.
We have conducted a survey online and now need to analyse the data. As the online questionaire is about 25 pages long and about 20 Managers were asked to fill out. Each manager had to describe the different positions they maintain for example, Business Analyst, Business Consultant, Postion description ..... Now the results are trickling in, its pretty tedious to go through each response one by one and compare how many common job functions there are....
It has been suggested to build a dB in MSAccess with the "questions" that were asked and upload all the responses from the online questionaire to the dB. The online questionaire & responses from each individual has been converted to excel flat files.
Can some one assist me in building the dB with the ability to upload the excel flat files one by one.
The point of this exercise is so that the analysis of the result process is not cumbersome.
Hopefully I am making sense?
I need to figure out how to program an expression that will reset a value on a form to zero if the product of it's calculation is less than zero.
I am figuring out sales commissions for listing Ebay items and there are some instances where the item doesn't sell, so it's a net loss because of the fees Ebay charges to list the item. I have programmed the form to take 10% of whatever the net gain is and use that as the salespersons commission. But in instances where the item doesn't sell, it actually takes money away since 10% of a negative number is a negative value.
In cases like this, I need the expression to just show a zero, if the number is negative.
If anyone could help, I would be greatly appreciative.
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
View 3 Replies View RelatedI have created a new Access database and need a few additional things that I would like to add. I created a database for inventory within my company. Only a few people will have access to it so security will not be an issue. However, I have a couple questions. Is their a way to have a popup when you open the database to insert let's say the serial number of the computer (already in my forms) and then it will automatically bring up that information. Also, I do not want any of my forms edited. I want this done strictly through the tables. So this leads to my next question. Is their a way once you have a form up that you can enter a different serial number and the information will automatically populate. If not, can you add a search query or option or what needs to be done. Thanks in advance for your help.
View 4 Replies View RelatedHi
I'm not sure if I have posted this in the right section, but i'm sure someone will tell me.
I have 2 questions, first is about concatenation.
I've looked endlessly & have tried a few scripts, but all to no avail.
eg db:
Name: Dpt:
Tom sales
John sales
Jerry mgm
Albert sales
Keith mgm
eg report that I want
Sales - Tom, John, Albert
MGM - Jerry, Keith
What is the best way to do that?
2nd Question.
In the same db, what i'm wanting to do is if I change the data in any record, it will track the changes.
eg
Albert has changed departments from sales to management (mgm as above), and so when you view albert's information, you can do something to pull up any & all changes made to that particular person. Any ideas there! Once again, i've searched the net and can't seem to find the best result.
Cheers in advance!
Hi,
I am just a new greenhore learning ms access and I am planning for a warehouse/inventory storage database.
My question :
1) I have 4 product division (Do you think it is good to have 4 tables):confused:
2) The calculation field. (How does the mechanism work when I take the the item out from the inventory, it will show as total storage -1 (eg, the storage have 5 units and I took 1. it show balance left 4).:confused:
About question 2, is it that I have a to do separate table again.
Temporary, I have these 2 problems and hope all access expert can help.
Regards,
Richard
I am fairly new to access and am trying to put together a project for work. I need to be able to create a list of all of the employees and which team they are in. So no problems with this.
I also need to be able to mark in their records where they can advertise and the quota they have. Each employee may be able to advertise in more than one location.
What I would like to create is a form for each team that displays the employees and where they can advertise. So far I have managed to get a subform to show this for each office but each employee has record for each job board. See Picture1.http://i112.photobucket.com/albums/n170/nharri/1-1.gif
Ideally I would like something like picture 2.
http://i112.photobucket.com/albums/n170/nharri/2-1.gif
Is this possible?
Hello!First of all I am new to these forums and I'm also a beginner with Access so have patience :) and I don't know if this thread is in the correct part of the forums so if a moderator feels like it's not feel free to move it.Me and a friend are interns at a company in US and the company assigned us to gather data from several excel sheets and build a completely new database. This database contains several tables.This isnt the correct names of the tables, it just to give you an idea how it looks at this moment.Customer information. (key: Customer ID)Resellers (Key: Reseller ID)Newsletter subscriptionsProducts (Key: Serial Number)Software (Key: Licence) Product2 (completely different product from the others) (key: Serial number)Invoices (Billing information, debit, credit, etc) (key: Invoice ID)OK, that explains how our tables looks like. And ofc in the tables we have different fields.Our problem at this moment is our form. Because we want to use a form to enter the data. We believe that it will be the fastest way and the easiest.I have a picture for you to show you our problem.It seems like the FORM is looking in the same table. But we want all the "fields" in the form to represent its own table. We tried to gather all tables into 1 Query and use the query when we created the form, the problem dissapears BUT a new one comes along. If we use a query in the form we can fill out all the fields but we can't create a new "record" with empty fields to enter more data hence leaving us with only one record of information. This is realy starting get on our nerves. Can someone help us?
View 1 Replies View RelatedHi there, Heres my situation
I have a basic form for a customer shopwing all general details the top field is Customer ID in which I would like to enter a customer ID and for the rest of the data to appear for that customer in the remaining fields, I have a small amount of knowledge with access and believe I need to a Dlookup but I am unsure where and how to put this expression i'm thinking maybe in a query. Any help would be great, My field names are listed below to make any help a little clearer
Customer ID field = txtCustomerID
Linking to = tblCustomers
to find = txtSurname
txtForename
txtPostCode etc
Many Thanks
Hi this should be very simple, but it's not for me.
I am creating a database logging machine error's.
Each error has a recorded start and end time, which I enter.
I want the database to enter the elapsed time into a third field, 'ElapsedTime'.
I see how to do this in the builder, just subtracting the two fields. But I know not to make it a condition of my query, that doesnt make sense. Where do I put my expression so that my table updates itself with the new field?
Thanks!
I am creating a database concerning products sold.
I am a beginner of using Access Database and would like to know if this is possible...
At the designing stage of tables and relationships is it possible to link and construct, so that the stock quantity of each product is kept up to date. so, when an order is made of produc '123', product '123' updates it stock quantity be deducting the quantity ordered.
attached are the tables/fields/relationships.
Thank you very much.
For a couple of years now I have been keeping a spreadsheet record of UK football match results and statistics - I use this as the basis for gambling on future matches. I now want to migrate this information into a database so that
1) It is much easier to enter the new data(via picklists)
2) I can generate reports on particular teams to help me on future betting decisions
Being almost completely new to Access I am struggling with the design. The information I'm capturing is:
Match Date
Competition(table including five)
Home team/away team(table including many depending on competition)
Home formation/away formation(table with standard list)
Home goals/away goals(numerical)
Home corners/away corners(numerical)
Home bookings/away bookings(Numerical)
Referee(from a standard list)
All the above is in a match results records table with relationships to each data element table.
For the form for data entry, I want pick lists for all non-numerical values - eg team, formation etc. I would like to get a little cleverer and for the team pick list to only contain teams that are in the competition I've selected(eg Uk or European).
I've created some tables and relationships, but when I create a simple form with combo-list boxes, the list itself comes up blank even though I've linked the field to a particular table of options(eg a competitions table).
Can anyone help on suggestions for the overall design(should I have separate match results tables and team tables for each competition?) and with this particular combo-box problem please?
Hi all. Had a quick peruse and this place seems really helpful, lets hope you can help me out here.
I have a project to do which roughly described is engineers doing jobs on aircraft. Three main tables:
Engineer
Booking
Job
The job table denotes the work required on an aircraft, with a unique barcode for the "job card". The Engineer table contains information about our engineers, with a unique ID.
Now here's my problem. The booking table needs to store information from both of these tables, but store it seperately. So I need to take some information from Engineer (ID, name, trade etc) and some from the Job table (barcode, description etc) and store it again in the "booking" table along with a few other "new" fields unique to the booking table. When this "booking" is made, it must record the start time, furthermore when the record is returned to later and closed, the duration of it being "open" must be worked out (total job time).
Ideally I'd like to create a form whereby I drop down a combo box and select an engineer (or type and autocomplete) it fills in the rest of his details, I then drop down another box and select a barcode (or again autocomplete) and that completes the rest of the job details. I then type in the new fields I also need, and this all gets stored in the "booking" table.
Any help greatly appreciated :)
I'm working with A2K3 for clarification, and can post/email my current database if required.
Thanks :)
I've searched and could not find what I was looking for.
I have NO database experience but need to learn quick
I have an html page with an iFrame (flash menu on left - iFrame on right)
When I click a product image on the left this is what I need to happen.
- Flash send a variable
- The iFrame to load an HTML page that will populate with an image, 2 text fields, and 3 hyperlinks dependant on what product is clicked in the flash.
I am using access since it is what i have at work, and i can just add all the products in a table pretty quickly and easitly. I set up a table in access with the image, hyperlinks, and text that needs to populate in the html page. The HTML page is set up in tables if that makes a difference...
I just don't know where to go from here to make my connections and how to set it up.
Any help would be appreciated.
Thanks in advance.
I downloaded a database of historical baseball stats recently and having been playing around with Access for a bit although frankly, I haven't been able to learn or do much. Anyways, here's my question:
I have one table that is a master list of all the players. The two main things I'm worried about are the playerID and the birthyear (year of birth). In another table I have batting stats from the players from each year they played. However, it is missing the year of birth which I need to calculate the age in that year. So, I need to get the birthyear from the master table and put it into the batting table next to the corresponding ID. Most players played for multiple years so then for everytime a playerID occurs in the batter table, I have to insert the corresponding birthyear from the master column. Hope that makes sense.
As I said, I'm completely new to this kinda stuff so any help or tips would be greatly appreciated.
Hello all. Would anyone be so kind as to help me out with this beginners problem. I have a lot a books which i'd like to sort into categories. I'd like to be able to select a category from a combobox on a form, and for relevant books in that category to appear in a listbox. Upon clicking on a particular book on the listbox, relevent info should appear on the form eg. date purchased,author etc.
Is it also possible to have an option to view all books from a particular genre in spreadsheet format embedded in the form, and an option to view every book regardless of genre. I'd then like to be able to print reports on each/all. I'm sure i'd be able to manage if someone could kindly get me on the right path with an example.
Thanks,
Richard.
Could someone please help me with the following.
I have a form that gets some basic information...
My table looks like the following..
NewApplicationID
ClientName
ClientAddress
ClientPhone
ClientEmail
later on down the road, we will be inputing other fields, is it possible to have another form for specific information, that would be linked to this....
thanks for any help
Josh
I apologize in advance if this question is completely obtuse, but I'll pose it to the group nonetheless.
My task is to analyze accruals versus actuals for the past quarter (i.e., July-September). What is of particular interest are the variances between the accrued and actual amounts. I have several fields of data, but the common field is Shipping Unit (SU). My initial plan is to upload the tables with the accrued and actual records separately and query SU's to pull in all instances in which there are matching accrued and actual SU's. This is where it gets a bit fuzzy for me. I would export the resulting queries into Excel, unless there is a means to produce the analysis directly from Access.
I haven't used Access in ages, so I don't recall how I might best proceed. Please advise with your thoughts. :confused:
Hi everyone, i 'm a begginer at access so forgive me. i have a table that looks like this:
polygonID tree species other.atributtes
1 a ....
1 b .....
1 c ....
2 b ....
3 a ....
3 a ....
3 a ....
4 b ....
5 a ....
5 c ....
what i want to do is to create a query or querries that will show me the polygons that have only one tree species. for example in the above table it would be polygon 2,3,4. thanx in advance
I think my question is easy...just not to me.
I want to make a query that changes byte 75 in a certain field to a capital P.
I also need to change byte 199 to N and 200 to S.
Any help would be greatly appreciated.
Hello,
I have created swithboard form that has couple of buttons. i need that button to display other forms. i am really struglling with that. i have looked at northwind project where it uses =openforms command but it doesn't work for me.
thank you
Hi, I'd like to ask for help about Access, ASP - all this is new for me...
I'd like to make an Access based website for searching, comparing and adding some electrical appliances. These appliances are supposed to have a lable name, type, serial number and they also have some options. (Just like cars: volvo, s80, color red, airco).
My question is about the structure of the database: is it better to store for example the "colors" in a separate (related) table in Access or to have one table for everything and use the lookup option with dropdown menu? Can I connect such a dropdown list from the lookup in database to the website using ASP? And if some options have options themselves (like manual airco/automatical airco), what is the best structure for it?
The final product must be a site where new models can be added and available appliances can be searched and compared.
Thank you for your help,
Alex
I have a simple table "Line" with Columns Key, OrderNumb, Panels, Gates, Posts, Color, Memo, Comp. A user enters the information into the powder.asp page. It is then added to the database. The Comp Column is populated NO by a hidden input type. The data is then displayed on a page in a table and the last column has a submit button. When the user Presses that button I would like it to update that record in the database to change Comp to YES. Some code snipits are below. The first is where the data is presented and where the submit button is. It then will go to the next snippet that hypotheticly will change just the Comp on that record to YES. I can't get it to work. Thanks Code:<form method="POST" action="powder_comp.asp"> <tr> <td valign="top" align="left"><%= rs("OrderNumb") %></font></td> <td valign="top" align="left"><%= rs("Panels") %></font></td> <td valign="top" align="left"><%= rs("Gates") %></font></td> <td valign="top" align="left"><%= rs("Posts") %></font></td> <td valign="top" align="left"><%= rs("Color") %></font></td> <td valign="top" align="left"><%= rs("Memo") %></font></td><td valign="top" align="left"><input type=hidden name="OrderNumb" value="<%=rs("OrderNumb")%>"><input type="submit" value="submit"></form></font></td>
Code:<%OrderNumb=request.form("OrderNumb")accessdb="Powder" cn="DRIVER={Microsoft Access Driver (*.mdb)};"cn=cn & "DBQ=" & server.mappath(accessdb)Set rs = Server.CreateObject("ADODB.Recordset")sql = "SELECT * FROM Line Where OrderNumb=OrderNumb ;"rs.CursorType = 2rs.LockType = 3rs.Open sql, cnrs.Updatesql = "UPDATE Line Set Comp=Yes"rs.CloseSet rs = NothingSet cn = Nothing%><META HTTP-EQUIV="Refresh" CONTENT="0; URL=powder.asp">
I'm still very new to this so please go easy on me.
Hi, I went to school over 4 years ago learning ASP but I forgot almost everything.
I am able to setup a connection to a an Ms Access file and get information using Windowx XP's IIS.
I need to change the code where the location of the Access file is on my computer to my webhost.
In other words, I have uploaded all the pages and the Access db file but now the pages can't find the Access db because the file was original on my computer in a certain folder. Now that it is uploaded, I need to change the location where it searches for the db from my computer to my webhost.
I have not dealt with ASP for almost 5 years ago.
Please give advice, or websites or 3rd party programs that can do this for me.
I am trying to build a webiste where you enter the username and login then be able to retrieve information or update, or add new or delete from the Access db.
This should be a simple problem for most you professionals out there.