We are a small Library just opened in our community, and the first one in our local area.
We are putting together an access query that will help aleviate the problems when our main library systems go down.
The database is a library records db. I want the query to ask the user for an author and a site and then to list all the records that match that query. Ideally i would like the user to be able to select a number of sites (from a possible 5), perhpas from a drop down type menu or checkboxes, type in the Author and then click on a button to perform the query.
Any help would be gratly appreciated - we are all beginners with access here.
Do you need to see what I have done so far? if so I could upload it for you.
We are a small Library just opened in our community, and the first one in our local area.
We are putting together an access query that will help aleviate the problems when our main library systems go down.
The database is a library records db. I want the query to ask the user for an author and a site and then to list all the records that match that query. Ideally i would like the user to be able to select a number of sites (from a possible 5), perhpas from a drop down type menu or checkboxes, type in the Author and then click on a button to perform the query.
Any help would be gratly appreciated - we are all beginners with access here.
Do you need to see what I have done so far? if so I could upload it for you.
I am trying to compare 2 tables in a mdb. One table is a linked xls file. Lets call it table A. The other is a standard table. Lets call it table B.
Table A is the "old" data that may need updated. Table B is the brand new data that will always be correct.
I want to find the differences in table B from table A. Keying off one field (ASSET_TTAG and T_TAG) if any of the attributes (8 attributes) of a record in table B are different from that matching record in table A then that record needs to be updated.
Also, if a record in table B is not in table A that will need updated also.
All of these discrepancies need to be displayed in a query along with all the attributes.
LCAMdump = Table A, SCANDATA_tbl = table B
SELECT SCANDATA_tbl.ASSET_TTAG, SCANDATA_tbl.BUILDING_NAME, SCANDATA_tbl.FLOOR, SCANDATA_tbl.DESK, SCANDATA_tbl.COLUMN, SCANDATA_tbl.FNAME, SCANDATA_tbl.LNAME, SCANDATA_tbl.SSO FROM SCANDATA_tbl LEFT OUTER JOIN LCAMdump ON SCANDATA_tbl.ASSET_TTAG = LCAMdump.T_TAG;
That is what i have so far. But it is returning a test record that i know is the same in both therefor should not be listed. :confused:
SELECT SCANDATA_tbl.ASSET_TTAG, SCANDATA_tbl.BUILDING_NAME, SCANDATA_tbl.FLOOR, SCANDATA_tbl.DESK, SCANDATA_tbl.COLUMN, SCANDATA_tbl.FNAME, SCANDATA_tbl.LNAME, SCANDATA_tbl.SSO FROM SCANDATA_tbl INNER JOIN LCAMdump ON SCANDATA_tbl.ASSET_TTAG = LCAMdump.T_TAG WHERE ((([SCANDATA_tbl]![USER _ID])<>[LCAMdump]![USER_LOGIN])) OR (((SCANDATA_tbl.BUILDING_NAME)<>[LCAMdump]![BUILDING])) OR (((SCANDATA_tbl.FLOOR)<>[LCAMdump]![FLOOR])) OR (((SCANDATA_tbl.DESK)<>[LCAMdump]![DESK])) OR (((SCANDATA_tbl.COLUMN)<>[LCAMdump]![COLUMN])) OR (((SCANDATA_tbl.FNAME)<>[LCAMdump]![USER_FIRST])) OR (((SCANDATA_tbl.LNAME)<>[LCAMdump]![USER_LAST])) OR (((SCANDATA_tbl.SSO)<>[LCAMdump]![LOGIN_SSO]));
I have also tried this but it is not quite what i am looking for. :( Not sure how hard this is, at first it did not seem like a difficult query but i am not getting anywhere fast.
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.
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.
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.
Ive come here to find out how to start making a date base from scratch,
I have had to change my life dramaticly over the last two years taking up new hobbies and interests, to try and keep my mind active and out of depression. My main interest is mine making and I want to create a megga database so that I can monitor recipies and ingrediants, against the wine cellar stocks.
It will need to be quite a comprehencive database with two or 3 elements working indevidually but also inconguntion with each other.
Sections I need to create inclue
Wine Celler, stock control and drink ability. Some wines are not drinkable or 12 months but will taste well improved after 3 years storage.
Recipie collection, I want to keep a data base of collectied recipies and all to do thith them but want recipies to link to Wine cellor stock control.
Can anyone please help me, I know it is a lot to ask but really dont know where to start.:confused::confused::confused:
Ive come here to find out how to start making a date base from scratch,
I have had to change my life dramaticly over the last two years taking up new hobbies and interests, to try and keep my mind active and out of depression. My main interest is mine making and I want to create a megga database so that I can monitor recipies and ingrediants, against the wine cellar stocks.
It will need to be quite a comprehencive database with two or 3 elements working indevidually but also inconguntion with each other.
Sections I need to create inclue
Wine Celler, stock control and drink ability. Some wines are not drinkable or 12 months but will taste well improved after 3 years storage.
Recipie collection, I want to keep a data base of collectied recipies and all to do thith them but want recipies to link to Wine cellor stock control.
Can anyone please help me, I know it is a lot to ask but really dont know where to start.:confused::confused::confused:
I'm trying to develop a simple company price list. I have created a suppliers products table. But I want to use the suppliers products to make up our coded goods (A Bill of Quantities simply). But I'm caught in a certain area that I dont have any knowledge on.
I want to create a form / subform whereby I can create my company codes, add up the cost price and put mark on it.
I have attached a image of what I'm trying to do in excel. In excel all I do is enter the code and the descrition and price comes up in the cells automatically. I just enter the quantity I require and total (sum) it all up.
In the subform itself. I can't get the the rest of the text boxes to automatically update once I have entered the code in the combo box. I rekon I have to enter a event procedure. Can some one help
Hi, I'm a student, I need an answer to what I suspect is a very basic question for experts or experienced Access users. I'm creating a basic booking system for a real-life client, a professional singer. I have a table with each gig she takes, with details of how much she will earn, and the date of the gig.
I want to be able to show how much she earns every month, so basically adding up all the booking fees up for each month. I'm pretty sure it's a report made from a query - with the query somehow separating fees earned from month to month. Let me know if you have any ideas...
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.
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 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.
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.
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.
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
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?
Hi 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
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?
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.
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.
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?
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.
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.
