a link is created between 2 sites
a circuit can travel over any given site
dataflow is from the left going towards the right
if site b fails, so does site c, d, e, f
but A still lives on
so how do I create a relationship between the tables to answer the queries correctly?
a site has many links, and a link has many circuits.
a circuit has many links
its a many-to-many relashonship.
I created a junction table to provide for the link circuit relationship.
I just need to know how to ask if site c is down, give me the link and circuit that is effected.
Or give me all the links and circuits effected.
The other site_1 table is there to eliminate data duplication for start abd end site which is just a site in the sites table. Its a form of normalization.
I’ll do the best I can to explain what I would like my program to do, maybe you can give me a little bit more guidance.
Alright – I have a database with the following 3 tables
Assets – Asset ID (PK) Inventory – no PK Preventative Maintenance – no PK
I created corresponding forms for each table, each with the same name.
Here is what I can do as of now:
Assets form - I am able to enter the asset #, description, serial #, hours between PM (preventative maintenance), and starting hours (same as an odometer) which I have set to 0.
Inventory form – Able to enter Part#, Asset ID (this allows the user to distinguish which asset this part was made for), Part Type, Part Description, Vendor, Location, Quantity, Individual Price, Total Price, Tax (Of the currency fields I just mentioned, I have it setup calculate the Total Price by multiplying the quantity * individual price. I also set it up to calculate 7% sales tax within the form)
Preventative Maintenance – The first field is PM# (auto number), Asset ID (distinguish which asset is being worked on), Date Checked In, Time In, Time Out, Current Hours, PM Performed, Part #, Quantity Used.
Well hopefully I haven’t just thrown a lot of information your way. So now that I have given you what my project can do, let me try and explain what I want it to do. And please keep in mind that I have been trying every which way I can to create the proper relationships, or foreign and primary keys, but that concept still passes by me at times.
I want to be able to know that if I have 10 tires for asset 1500, and I perform a PM on Asset 1500 and use 2 tires, that I will have 8 remaining. Maybe I just am missing the simple solution here, but I have been trying for days. Also, that value 8 should be the new on hand value within the inventory. I need it to update.
If I have 10 tires with part # TIR1500, and then I receive 2 more a few days later, how can I get the quantities to correspond? It seems as though I was able to work this out through queries to get the correct info, but due to the relationships I tried setting up, when I got to the PM and the quantity used, it threw my totals off.
I've read this forum for quite a while, but just now registered and this is my first post. It's full of great information and I really appreciate all I've learned!
I'm looking to contract / hire an Access Developer for a small project. Essentially, it's taking a CSV file, importing it into Access, then exporting the data within certain parameters into an Excel spreadsheet. There are a few other small details that pertain to how the data is handled in the actual db itself, but that is the overall picture of the project. It's rather small, but is above my Access knowledge level.
My most important goal is getting the DB functional, but then taking the finished product and breaking it down to learn from it.
Please PM me if you're available to assist on this project. Have a great weekend!
I have had a client moving to a new server a couple of months ago.
They have a small access application that reads csv files and exports them into Sage format that can then be imported into their accounts program - it also reports the total invoiced (whatever is inside the data CSV file) and has a printer friendly report with all the invoices inside said data CSV file.
The issue I have is that since the little app was moved to the new server - it stopped working.
I think somewhere in the code / options there is place to state the working path for the app. I think the app is trying to read the input data file from the old shared folder and thus not working anymore.
I can post the code and a sample CSV file. Specially because I did find a place with the old path and changed it to the new file share and it still didn't work.
I receive monthly project tracking reports and I'm looking to compare a few things between these monthly updates. I should add I'm new to Access and have been using Excel and dual monitors to manually compare data sets!!! My life might be getting a LOT easier.
Here's what I'm tracking:
I get monthly excel reports that have unique values from a "Project ID"each "Project ID" stays the same every month, but the project status changes from "Installed", "Commitment", "Pre-Commitment", etcSometimes new projects are addedSometimes old projects disappear from the reportA few other columns have numerical data that changes every month as well "Project Cost", "$ Incentive", etc.
Here's what I'm trying to automate:
Find out what Project IDs are newFind out if old Project IDs are missing from the new reportSomehow "flag" or identify projects that have status changesSomehow "flag" or identify which numerical values increased or decreased i.e. "Project Cost", "$ Incentive", etc
Do I create two tables? Do I append the new monthly report to the old one when I import my data, then somehow work with the new information?
I have tables and forms setup but I have 3 general questions on how to finish the project.
1. Should I create a folder where all the files reside for instance, create a phone log folder, put the DB file into the folder and export XML's into the same folder?
2. How do I get the form to launch as a self-contained form without the Access application in the background?
3. How can I get this form and table to automatically update an XML spreadsheet whenever records are added?
4. How can I get the form to sort all records according to time logged? The time will be entered in manually and we need all the records to sort automatically by time in ascending order.
I have tried the count(field name) and the sum(field name) but neither of them are giving me the grand total only the count/sum of that row. Here is my code, please look - I'm sure its something small and stupid
SELECT [qry_Refi_Yield].[LOAN NBR], [qry_Refi_Yield].[BORROWER], [qry_Refi_Yield].[PURPOSE], [qry_Refi_Yield].[FUNDED STAMP], [qry_Refi_Yield].[HSS], [qry_Refi_Yield].[UNIT TEAM], [qry_Refi_Yield].[Cycle Time], [qry_Refi_Yield].[Total Yield], IIF([Cycle Time]<=30,1) AS Yield FROM qry_Refi_Yield GROUP BY [qry_Refi_Yield].[LOAN NBR], [qry_Refi_Yield].[BORROWER], [qry_Refi_Yield].[PURPOSE], [qry_Refi_Yield].[FUNDED STAMP], [qry_Refi_Yield].[HSS], [qry_Refi_Yield].[UNIT TEAM], [qry_Refi_Yield].[Cycle Time], [qry_Refi_Yield].[Total Yield] ORDER BY [unit team], [hss], [funded stamp];
past tries: count([qry_Refi_Yield].[Total Yield]) count(borrower)
I was using access 2000 to build a small office app. It will be on a network and will generally be used by only 2 or 3 people(max). I was wondering if the default jet technology would be good enough for this. If it isn't, what should I use? I looked at other options such as msde and ado but it's a bit confusing when you're new to this stuff.
There are times when it is just necessary to have a little rant, a canniption if you will.
For the last few weeks I have been working off-and-on with a new database for a salmon hatchery. One of the forms I designed contained a subform in continuous form view that flickered inordinately when first loaded. Now, there's a lot of code going on behind controls etc to run sql updates or deletes or appends depending on what the user clicked. There was also some code to alternate the row color of the continuous form. I spent literally days trying to find what part of my code was causing the problem.
I swore a lot.
I searched.
I eventually gave up as other priorities began to arise at work. I resigned myself to my fate.
Then I discovered, quite by accident, that the problem arose by the use of some unassociated labels being present on my form. I removed them, replaced them with labels associated with a control, and presto: the form flickering/reloading magically vanishes.
Great.
But what a colossal PITA for something so bloody stupid. There's absolutely no reason why this should occur IMHO. Honestly: it's hard enough to learn VBA, SQL, and relational db design without having to deal with poorly documented glitches like this. There are days when I think I'm finally starting to get the hang of Access, then something like this comes along and I want to send a lynch mob to redmond! :mad:
Ok, end of rant :) Hope the solution to my problem comes in handy for someone else someday.
What i want to do is have a field called "monthlyhourcount" in my query that totals all the hours in a field i have in a table, the field in the table is called "hours worked" were a figure is entered, i need my query to give a total of all the figures entered. so far in the expression builder i have :
monthlyhourcount: [JobListMonthly]![Hours Worked]
when i run the query with this it gives me the seperate values, i need one total value.
I'm fairly new to Access (2000 is the version im using), and I'm not sure how to do several things within a form:
1) make cursor in a text box jump to the front (left) when clicked on 2) Lock (and grey out) a text box once data has been entered into another 3) assign a field to correspond with another, and to have this shown on the form (ie: having a list of names and their corresponding phone numbers assigned to each; then on the form, having a combo or list box with the names that a user can select- once a name is selected, their phone number appears automatically in another text box) 4)Locking screens or subforms when moving on to another (either by pressing a button to open another form, or by selecting a field in another subform)
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
Here is the code that I was thinking of, I don't think its right but it might be a start.
Dim Count As Variant Dim CountImplementedTotal As Variant
CountCost = 0 CountImplementedTotal = 0
Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordSet("ECM Details") Do While Not rst.EOF If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then If rst![Has Measure been Selected] = "YES" Then Count = Count + rst![Total Measure Cost] End If End If rst.MoveNext Loop
hello all i need a small thing to change in a module but am not too familiar with vb so i reached a dead end, here's the story:
i have a 'dog pedigree' database that i was working on, now there's a report where a dog's parents, their parents, and their parents need to be shown
that turned out to be out of my league so i paid a guy to do it for me, it worked except he didn't do exactly what's need and i can't get to him now :S
anyway, in the report page, you can notice that the name of each parent is shown, but i want the registration number of the parents to show as well, i tried modifying the module but it didn't work (am not a vb user), so can anyone help me add the reg number to the name of the parents?
also, in the report, the dog's color is appearing as a number instead of an actual color so if that can be fixed as well ...
to understand better: record 1 in dogs, click the preview diploma button you'll notice "tat" as dogname and "ry245" below that as registration number now sire is "pat" and dam is "titu", i want their registration numbers to show as well (and same for the rest of the parents)
i uploaded the file here for a closer look: http://www.designcrafts.org/dogs1.zip
We currently have 2 MDE front ends to and oracle box. Database is about 25Gigs and have been minimising the traffic by optimising the queries. However we have always noticed some very strange problems now that our user base is about 150. They seem to become more apparent when the MDE files reach a certain size. I have implimented an autoshut down and inactivity shut down protocol into the access front ends but I would really like to know why the MDE files are getting so large. They are normally about 5MB but get to in exccess of 130MB. The autoshut down is forcing everyone out which compacts it overnight!
When these databases reach critical mass, peoples data seems to be getting mixed up and one persons form is mixing data up with anothers!!!!!! What is casuing this? We are using the front ends over citrix too, is this the root of my problems. I am going to be copying the MDE file to each citrix box to speed it up and reduce the number of users on the mde. Ideally I would prefer an mde for each user but that is impractical.
Really my question is this, Why does an MDE file with no tables except linked via ODBC increase in size so dramtically? Is there a leak or somthing somewhere?
I made an Access DB program in my PC. I have 17 inches monitor and so I designed everything to fit in that screen. When it was deployed in the user's computer, it did not fit in his screen as he has only 14 inches monitor. The command buttons and other things were not visible and were out of view of the screen. I changed the screen resolution but it made the fonts look much smaller. I have used 12 pt fonts in my Prg. Is it possible to correct my forms so that it automatically fits in all sizes of screen?
I'm an audio/video technician at a college and wish to use Access for a simple camera booking system, but do not have time to go through all the extensive help menus and tutorials about this program, and none I have viewed seemed to help.
I want four columns in the table. First, the user inputs a student ID number. Now my first problem is that I want the student name to appear in the 2nd column after entering their number. I don't know where to create the list of names or how to link them to their student ID numbers.
My second problem is I would like the 4th column simply to display the date seven days from the current one (as in whenever the db is used) for the return date without having to input it manually.
Any help towards these 2 problems would be most appreciated, and thanks in advance.
I have the following query that will show the date, studentname, number of classes marked absent for that date.
i also want to show the total number of classes in the day, this is needed to make a quick comparsions to the total number of classes missed per day.
This is the code
SELECT Attendance.Date, Count(Attendance.Attended) AS CountOfAttended, Student.StudentName, Count(Attendance.ClassID) AS CountOfClassID FROM Student INNER JOIN (Groups INNER JOIN (Classes INNER JOIN Attendance ON Classes.ClassID = Attendance.ClassID) ON Groups.GroupID = Attendance.GroupID) ON Student.StudentID = Attendance.StudentID WHERE (((Attendance.Attended)=' Absent')) GROUP BY Attendance.Date, Student.StudentName, Student.StudentID HAVING (((Student.StudentID)=[Forms]![Frm]![cboStudent]));
This shows the date, the number of classes the student was absent for, the student name, the total number of class in that day. But my problem is that it only shows the total number of classes marked absent again..... IS there a way to exclude the where clause from the Count(Attendance.ClassID) AS CountOfClassID part of the code...
well i created a relationship between 2 tables where:tableA = invoice datatableB = company IDsi have 2 questions that i have been unable to resolve on my own. 1. i want to create a query where i am able to find all of the invoices from a single company using it's ID and add up all of the account payable/recieveables i learned how to add all of the values on a field but i don't know how to add the values on a specific row. so while i can add all of the ap/ar on the table with all of the information, i can't add the ap/ar of a single company despite the fact that my table sorts them apart (because i don't know how ><)would a code like iff([company id]= "#",add) be close to what i'm looking for? because that's all i'm been fiddling with to try to get my query to do what i want. so far i'm stuck with: AR: IIf([company ID]=1,[AR$$],0) whereas 1 is the ID of the specific company i'm trying to pull records for and AR$$ is the field that i'm trying to pull the information from.2. find a way so that i can get my table to print all of the records for a specific company based on the ID. so for example, if i wanted all of the invoices from company #9 then i would pull them out and print them. i tried expanding the + sign on the company ID table and printing it, but it obviously failed -.-"i would really appreciate any help that can be provided to me! i just started access so anyone that can link me to an article related to this would be greatly appreciated because i don't know the terminology/keywords that i should be searching for in the article page that i'm using for this case.
This is what I have so far Def: IIf([30 day notification]<"1/1/2005","h") What I need to accomplish is, any records less than 1/1/2005 show me "H". It does show up in some but not others. This is a date field. What is it that Iam doing wrong.
Doing my best at learning code. I don't know how you people learn this stuff
Normally in a form if you use the '&' in front of a objectname or something like that it will underline the first following letter meaning you can use the 'ALT + letter' combination to use it.
Now I want to use the '&' sign in a label, without having it do anything like that. I just want it to display the sign, anyone have any ideas on how to do this?
My client wants a company name that appears on multiple forms and reports to show as font name Garamond which is fine... however, he also want the font to appear as "Small caps". In Word and Outlook, under the Font submenu, I can check Small caps and get what he wants... lower case letters are shown as Small capital letters. Is there a way I can make the same settings on labels in Access without installing a new font on each workstation that runs the database.
Ok here is the problem I have, I need to have a way for an end user to select infomation on a form that is pulled from a table and displayed in a way that they can select more than one item (I am assumeing a list box will be best) then write those multiple values to another field(s) in another table.
Here are some of the problems with that: 1. The user is defineing the amount of possible selections 2. The user can select anywhere from 1 to any number of selections from that list box.
In case I confused you on what I am trying to accomplish let me give an example.
User1: fills out form 1 with 5 different records, which get written to table 1.
he then goes into form 2, form 2 has a list box displaying the 5 records from form 1, he selects 3 of those use the click and ctrl key method.
He then saves that record and Access writes all the data collected in form 2 as a record in table 2.
http://img64.imageshack.us/img64/5269/menubalkem9.pngdoes anybody know how to change the picture of the database to somethng else. Now you see that pictyue of 3 forms I want to set something of my own.please assist
i seem to be geting this message ( attached ) everytime i load the form for the first time. once i click ok, i can the enter data into the field and save as normal. if i load the form and got to a different field, it seems to be ok and the mesaage does not come back but it is definately there when i first click in the same field.
i have checked the form properties, field properties and code but i cannot see anything.
what usually causes this?
the text is the right size the form is not in design view
i cant seem to find if the field is read only but after the box, i can enter data so that should remove any read only issues.
Hey, I'm doing Computing as an A-Level and we have project to do over Christmas and I need some small help writing the queries.
Basically I have to do an Examination's Board Database. It has to store the Candidates that are going for exams, what their Centre (School) Number is, what exam they're taking and so on. It has to be able to produce four reports.
One of these reports have to show every examination that is taken for a particular centre. I am using the variable "Ce_No" for Centre Number. And I have five different Centre Numbers being used. I want it so that when I open my Query it asks me to type in a centre number, then when I've typed it in and clicked "ok" it will show me every exam being taken by people in that centre. I'm hoping this makes sense.
In my query I have added all the information I want to use, I know I have to type something in for "Ce_No" in the "Criteria" bit, but I don't know what... I'm hoping this makes sense.
Basically, I want it so that when I type in a number, everything thing with that number as it's primary key comes up but nothing else.
