I have a general question about Access Projects which I plan on learning soon:
I will be using SQL Servers for the databases.
Are Access Projects very different from ordinary Access and VBA (.mdb)?
I have a good grasp of Access Databases using Access as the back end. How much would this help with Access Projects?
I need to be able to add a feature to an Access project that will select a group of records based on an area code( user will have to be able to select a desired number like 100, or 1000), mark those as selected, and create a single csv file...The area codes are stored in the SQL SERVER database...right now they just export groups of #'s based on area code to excel select the amount they want and cut and paste into a flat file for another application to use...they would like to be able to select a number from a list box or type it in...like 100 ( which means take a 100 #'s from all different area codes) then have them automatically noted in database as being selected..and then export all selections to one csv file... should I do part of the work in the Sql Server behind the scenes..the format of the csv file also needs to be specific in that the first row is the columns separated by commas and then the data follows all text fields enclosed by qoutes..
Thanks for anyone's help ..Im very inexperienced with VB and Access
Hi, Big Jim here:
I am really not sure where to ask this one.....
My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.
Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".
Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.
Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.
Thanks in advance!
Big Jim
Set Up:
Windows XP
Office XP
SQL Server 7.0
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 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:
Hi
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.
Kind Regards
Hi
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.
Kind Regards
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
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.
Any help would be great.
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...
Just curious as to what others are working on. Here is what I'm in the middle of and what the basic purpose is:
It's a db to track parts we swap with our customers. It's primary table is on a sql sever so that the anyone in our company can view only the data via DAP's. The other tables are in the .mdb on the file sever. I plan on having it up and running in the next week or so. It will be used (add/edit,etc data), by approx 8-15 users in three different buildings off a file server share folder...
See screen shot attachment.
Hello, So far you guys have been helping me through my database that i am building. Thanks so far!But at the moment i need help on one more aspect of this Database Job. Currently i have a database that supports and holds records for jobs that are under contract. That means we have certain numbers and statistics of Jobs and there properties.what i have right now is a table with a couple fields. (there are more than this but this is just for example) project number "primary key" aerial units buried units planning units ICGS unitsThat part works fine with the current table. But since jobs are under contract the number of units might change further on during the design and construction. There may be more than one mod.Now the part that im stuck on is the most important. I need a table/query/form/report that i can input the changes to particular units. Like i said before there might be more than 1 mod. So i need to make it show ALL the mods ive done to a project.This is a huge part, and any help would be very much appriciated. ~Thanks so muchJon
View 2 Replies View RelatedHow to input new orders for old projects:
I have an orders form that looks up data from 3 tables (projects, orders and orderDetail) with a subform that looks up data from a query.
The form has several unbound comboboxes relating to the project that synchronise with VBA code
Projects are setup in another form and this form is only used to lookup or add orders to existing projects, so project data will remain the same on the form
At the moment when I press the 'add new order' button, it does not clear the unbound combo boxes and all the data in bound fields and subform is cleared.
When I press the 'Add new Order' button I want the form to clear all the data on the form and subform relating to the order_table, but to retain, or automatically update(with the same data), the data in the fields relating to the project table.
How do I make this work?
Any help is greatfully recieved.
Thx.
Kev.
I have two tables. The first is a list of users. The second is a list of projects. I need to loop through the user list and assign each one to a project. At the end of the user list it will just start over at the top until all the projects have someone assigned to it. What is the best way to do this?
Table1 Users
User1
User2
User3
Assignments
ProjectAssigned To
Project1
Project2
Project3
Project4
Project5
Project6
After processing.
ProjectAssigned To
Project1User1
Project2User2
Project3User3
Project4User1
Project5User2
Project6User3
I'm aiming to create a stacked area chart to display the progress of multiple projects over time, so we can review and forecast project load over time.
The data comes from this table:
tblProject
* ProjectID
* DateProspect
* DateConfirmed
* DateStarted
* DateComplete
A project passes through the statuses Prospect, Confirmed, Started, Complete. The current status of each is calculated in qryStatus from the dates in tblProject. The past and forecast statuses for each project will probably be calculated each time the chart is created (or maybe not necessarily so?)
To create the stacked area chart, the x-axis will be months (e.g. Sept 2013 - July 2015). On the y-axis will be the count of ProjectID, and the series will be the different project statuses.
The sticking point is getting from the Date* fields to the past and forecast statuses in each month. After that I imagine it'll be reasonably straightforward to put into a pivot/chart.
I am creating a database that tracks current projects for my team at work.
Some projects are only due once (e.g., mailed brochures due on 1/1/14) and some are due at scheduled intervals (e.g., status report due monthly, quarterly, etc.)
Ultimately, I'm hoping that my end result will allow us to click on a form and look at what everybody has due that day, in the next 7 days, and so forth.
I've attached a rudimentary example db to give an example of what I'm trying to achieve.
There is a form on the db called Resourcing with several combos.
When I choose a Training_Type from the first combo it automatically filters the Project_Title combo to show only the projects that match that training type. When I choose a Trainer_Name the text box for team automatically completes.
The bit I'm struggling with is Trainer_Name. As you will see from the table Course_Details not every trainer can deliver every Project_Title. So what I want is to display only the trainers associated with the project title chosen rather than displaying the whole team in the Trainer_Name combo.
Someone suggested a junction table but I'm not sure how this would answer my question. I'm thinking some sort of Dlookup but don't know how I would write it when it has to include data from four fields.
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.
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