Hi Everybody. I've been nosing around here because I have this difficult problem with a database design. I modelled my program in UML so I have a class diagram. Now I want to create an access database out of it, but this is too hard for me.
It's about a school project for flowers. Every year, they make a cross plan. This cross plan contains crossings. Many crossings. And every crossing exist from 2 genotypes. A mother and a father. With this crossing several new genotypes are created. How on earth do I realize that in a database? A plant is male as well as female so you don't need to indicate which sex it is.
Further more I want a genotype to be judged on his characteristics by a user as much as he wants to.
Well...I hope someone can help and if you have questions about it don't hesitate to ask.
One file is numbers I averaged over the past 3 months. The other file is actual numbers. Not all zip codes are in each file. I need to figure out the difference between the 2 and add the difference to this months numbers. So I created a query and found there were 94 records that matched based on zip cod and customer type. Then I found the difference in sales amounts. Now I'm stuck at figuring out which zip code and customer type don't match in the 2 files. So if zip code 10001 with customer type S isn't in the actual data for this month I need to add the zip code and customer type and have it be -303.23.
I’ve posted on here a few times lately, as I’m creating a database at work. It is going to be used to keep various information about new persons applying for jobs, and I want it be viewed/sorted by either 'Job Grade' or 'Job Title' I keep doing a lot, and then getting stuck on certain things. I’m stuck again, but I think it’s a little more difficult this time…
What I would like to happen is have the database open up, and a screen appears with two choices – ‘View by Job Title’ and ‘View by Job Grade’. When you click one, a new screen appears and a list of some sort appears with all of the job grades / job titles (depending on which button is pressed) on that are currently in the database.
From here, you can choose a job grade / title and it will bring up the form I have created – but only with the records that have the chosen job grade / title.
So for example, there could be 20 records in the table. 5 with job title ‘Manager’, 5 with job title ‘Assistant’. Then there could be 5 jobs with grade ’1’ and 5 jobs with grade ‘2’. When you clicked on ‘View by Job Title’ and then choose ‘Manager’, the 5 records with the job title ‘Manager’ would come up.
I hope I’m making sense so far.
The data would all be stored in one main table, and I already have the form created, so it would be good if I could use this.
Is there any way to do all of the above?
If anyone could help at all, it would be very much appreciated.
(This means: Between the amount of 0 and 100, the eventual amount equals to 5. Between 100 and 500, the evebtual amount is 10. etc. etc.)
Over the amount of 50,000 for every 5,000 the amount goes up, the eventual amount (column c) goes up 10, with the condition the eventual amount (column c) should not be over 500.
My current If-statement: Eventual Amount: IIf([Amount]<100,5,IIf([Amount],500,10,IIf([Amount]<100,15 etc. etc.)))
Is there an easier way to do this... It's going to take mighty long to do it like I am currently doing it. Thanks a lot Regards kruger101
I have a database for my company which I am making.
The products are hydraulic cranes and come in numerous models (or sizes), in turn these models come in numerous versions, 5 different ones in all, lets call them for simpleness 1-5. However, some cranes come in versions 1-3 only, others 4-5 only, some all versions and no-doubt some a random mix!
At present I have a table with the fields "Model" (Text), and seperate fields for each "Version" (ie. 1, 2, 3 etc), these are Yes/No tick boxes. Ticking a given box for any given model, means that only these options are available on a form. Ie When you select a "400" crane, you can for example only select Version numbers 1, 2 and 3 - those that are checked in this table.
What I need to do is when selecting each individual model on a particular order is to display 2 figures hydraulic oil flow-rate and also hydraulic pressure required from a reference table I assume. These figures are different for every given model/version combination.
The difficult part as I see is relating the yes/no check box from the table above to version number on the reference table.
How difficult is it to make a sychronisationfunction? I have a meetings database. Sometimes the meetings are enterd localy and need to be sychronized with the main file. Is this hard to do? where do i start.
Where [WeekUren] contains multiple references to unique records in [Projects].
[WeekUren] has the following collums which are important: -ProjID -Week -Persoon
Where these three always form an unique combination. For instance: 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 1 | 1 | 2 1 | 2 | 2 2 | 1 | 2
Saying: Person1 will work week 1 till 3 on Project1, Person2 will work week 1 and 2 on Project1 and week1 on Project2.
Weeks are here relative to the project start date (a value of [Projects]) and always start with and increment with 1. So week1 of Project 1 and 2 don't neccesarily fall on the same dates, hence Person2 is still able to work both Projects.
Bear with me here.....
Now I need to select all duplicate records (where people are assigned to two (or more) projects in the same real week.
Real week is defined by: Code:DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId)))
And the Query to convert [WeekUren] in to the relative form is: Code:SELECT WeekUren.Id, DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId))), WeekUren.Uren, WeekUren.Persoon, WeekUren.ProjIdFROM WeekUren;
The query to print out duplicates is ofcourse: Code:SELECT *FROM WeekUrenWHERE (((WeekUren.Week) In (SELECT [Week] FROM [WeekUren] As Tmp GROUP BY [Week],[Persoon] HAVING Count(*)>1 And [Persoon] = [WeekUren].[Persoon])))ORDER BY WeekUren.Week, WeekUren.Persoon;
Sofar so good, combination of the two however give me a syntax error. Hence my question here how to construct such a diabolical Query. Thanks!
I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example: I want this: AUTO ONE CLARKSON CLARKSON WA AU006495 to be recognised as: car parts
or with this tranaction: CLARKSON MINI MART CLARKSON553908 look for "mini mart" and return supermarket
This step will make classifying transactions a lot simpler and user friendly. thanks
I have Two fields with DateWorked and EmployeeNumber. All I want to do is count the number of employees that worked on a specific date. In the employee numbers I have 4 dummy numbers that I don't want to count in my query (00001-00004), but will have entries almost each day. The rest are 5 digit numbers.
Some employee will work some days, but not others.
So I set up my query with:
EmployeeNumber Table Total: Count Criteria: >4
I did that think that it would count all employee numbers great than four, but all it seems to do is give me the count of days that more than 4 employees worked.
What am I doing wrong? I feel like this is simple, but after some of the more complex things I've done over the past week, this seemingly "easy" thing has me stumped.
Alright, so my pride and joy and departmental savior database has been fully implemented and people are so pleased with it that they *all* want to use it at the same time. Initially, I made it custom for three peoples' UI preferences, but since it's such an intuitive design, other staff now feel comfortable using it instead of passing the work down the line to the one person who knows how to use that "newfangled thing".
A couple years ago, I used FMPro and allowing for multiple users at once was an annoyingly complicated process dealing with permissions and rights and accounts and passwords and yadda yadda. Is it any easier to set up in Access? Does anything special even need to be done to allow for multiple simultaneous users?
This is a toughie (i think so anyway!). I'll attempt to explain!!!
I start with a CONTINUOUS form in my DB that shows all readings for a single given customer's connections. E.g. Customer A has 5 connections and each connection has say 3 readings. Thus this query which uses joins between the Customers, Connections, and Readings tables, would return a list of all readings for each connection, so in the above example: 1*5*3 = 15 records.
Not too tricky so far, but then what I want is for each connection to have just one line showing the latest reading (easily achieved by use of SQL Aggregate Max function on the date field coming from the readings table) TOGETHER WITH the last-but-one (next most recent) reading. So back to the example taking Customer A's connection 1, the row would be as follows:
Cust Conn CurReading PrevReading A 1 750 500
Where the CurReading value (750) comes from a different record than PrevReading (500).
I've tried all sorts of ways (subqueries etc.) to achieve this without success. The main problem being that any sub query would require parameters from the current record's fields, which seems not to be possible. Can anyone help or is this simply not possible in MSAccess Forms. If it isn't possible anyone have any suggestions as to an alternative way?
Hi,I'm looking for a bug/issue tracking solution done entirely in MS Access. Does such a thing exist?My requirements are that it must need only Access, and be accessible in a shared environment solely by opening a .mdb file from a shared folder. It must support various issue lifecycle related things, and the stuff those tracking systems do in general.It may or may not be commercial software.If anyone knows of such an available solution, please let me know.(And yes, I've searched on Google, and haven't found anything worthwile, so that's why I'm asking here now.)thx
I haven't worked with Access for a while, now i'm working on a project and just can't handel with a calculation. I have somewhere the solution for my problem, I had use it other times, but now i just don't know where to find that sample database.
What I want is to calculate the Benefit=ProjectValue-CostValue.
I know it is possible, in other cases I have used some union queries, sum calculation and I had my results very simply. But now, as I said before, can't find that piece of SQL. :(
I'm looking for a bug/issue tracking solution done entirely in MS Access. Does such a thing exist?
My requirements are that it must need only Access, and be accessible in a shared environment solely by opening a .mdb file from a shared folder. It must support various issue lifecycle related things, and the stuff those tracking systems do in general.
It may or may not be commercial software.
If anyone knows of such an available solution, please let me know.
(And yes, I've searched on Google, and haven't found anything worthwile, so that's why I'm asking here now.)
I need to try and create a simple form that a user enters data into and then hits a print button and the text they entered is printed in a particular way.
i.e. they type in someones name, job and company into 3 fields and then hit a print button and this then prints :
PERSONS NAME JOB TITLE COMPANY
We also need the print to be formatted a particular way but that is another issue
This is for a small exhibition we are trying to run and we need something to print visitor badges with
Has anyone got any ideas that can really help as we have been let down by someone who was going to do this for us
This is for anyone who has made a form with a lot of check boxes and wants to make a report out of them thats decent.Hopefully this simple example file is enough to assist people.Keywords:Checkbox Checkboxes report check boxes box
I do not want security on any of the files in my computer.
I'm placing this in "general" because I'd like a direct and practical answer, please. And if there is none, please say so. Computers are immensely complicated and I am just a little bit tired of people superciliously refering the uninitiated to "Microsoft's FAQ's" That source is to most normal humans as obscure as the software programming itself.
I have also searched this source and Google for hours, without getting an intelligible answer.
This is my problem:
I am the only user and administrator on my computer. I have a back-end file which is easily accessed through the front-end. .... but I cannot open the back-end to access the tables directly. I get the following error message:
You do not have the necessary permissions to use the 'C:Documents and SettingsAll UsersDocumentsAccessThingsWORKLOG_be.mdb' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.
Of course the person who created this back-end is me, but I have no clue what I did, because it is at least two years since I created it.
Could someone please help? I have tried to use the "shift", click method. It does nothing - just gives me the same message.
Link to the original thread (http://www.access-programmers.co.uk/forums/showthread.php?t=89557)
I have realized what I was doing wrong and thought that I would post the solution in case anyone else does the same trying to implement security. First off thanks Pat Hartman for the input. You were right on there needing to be a cutoff and now one is added that ensures they can't edit punches after payroll has started.
I had the right idea with the many to many relationship to get a list of buildings. What I was doing wrong was joining the resulting table to the shifts table. Instead the correct way (well, it works anyways) is include WHERE Building IN (SELECT ....) in the sql where the select statement gets the list of buildings numbers that I have access to.
Now the list is limited to the buildings that they have access to and when you delete only the shift table is affected because none of the other tables are joined.
I have a A97 Db. On one of my forms (see attached screen pic) I have a field "Payment type" for either Cheque, card of Account. There is no code or functions behind it, it just stores a value.
Trouble is my users keep forgetting to fill it in!
My first (and easiest) solution would be to make "Payment type" a required field. However. The field will only be filled out under certian conditions. That is if the "Status" field value = "X" (drop down box holding two values "X" and "Y").
What would the code be? I presume it would be in the Form "after update" field? Would read somthing like:
if [Status] = "X" and if [PaymentType] = "Null" then mssg box "Please enter payment type"
I have a vba book on order to start learning, but as you have probably guessed, I have not received it yet! :)
Any pointers or info would be much appriciated. Many thanks :)
I need a little bit of advice on this one. I have 2 tables that are used for different things, one table, denial data, is used for tracking all requests. It is updated with the form, Daily PAs. On the form are 2 buttons, each running a macro. One button exports the data to Excel by running a query to specify the date range. The second button is my problem. It also has a macro, with an append query, to append only requests that have been denied to my second table, Denials, to be later updated with additional information. The append isn't working because I am getting three different errors, a "type conversion failure," "key violations", "lock violations" and "validation rule violations." Now, I know I can begin working out these violations and get it working, but I'm sure that involves a lot of time and coding. I would really appreciate any other suggestions to accoplish the same tasks. I need to keep the approved requests seperate from the denied requests for auditing purposes. Thank you for your help.
Please forgive a newbie that asking the stupid question.... i just wonder is that anyway to set the date format to short date with instead of mm/dd/yyyy to dd/mm/yyyy to let the user to keyin?
Hello All I am in need of a lot of help. The situation is as follows I have a table with users that have certain classes that they have to take and in another table I have the dates that these classes are offered. My problem is I want to find a way to map all the students to their required class by scheudling them into the required classes taking into account date conflicts and classes required before taking a certain other classes. I guess my question is if there is any possible to do this in access without me phyically having to schedule each users required classes to the correct time making sure there are no date conflicts. Any help would be highly appreciated because we are talking about 3000 users that need to have schedules and that is extremly time consuming if I have to sit here and do the schedule for each user. Thank you in advance for your time
I want to use a counter increment so that I can loop F1 to F3 I don't want to create 3 (actually I'm trying to avoid creating 50) If/EndIf blocks of code. Can someone help me?
Do While mCtr <= 3 If Not IsNull( Eval("F" & Trim(Str(mCtr)) ) Then QueryStr = QueryStr & Eval(FValue & " = '" & Eval(VValue) & "';" End If mCtr = mCtr + 1 Loop
The way that it works: Do While mCtr <= 3 If Not IsNull(F1) Then QueryStr = QueryStr & F1 & " = '" & V1 & "';" End If If Not IsNull(F2) Then QueryStr = QueryStr & F2 & " = '" & V2 & "';" End If If Not IsNull(F3) Then QueryStr = QueryStr & F3 & " = '" & V3 & "';" End If mCtr = mCtr + 1 Loop
I need help on an update query. Case is an automatic letter generation for particular document which has revisions like 00 or 01. So when this is done, on click, open an update query to update date and letter no in main table against that particular document. I made the query but does not work and says result should be an updatable query. I am posting SQL below.
UPDATE [MDL-10], TRANSMITTALGEN, [Transmittal Record Query] SET [MDL-10].[REV 00 SUBMISSION] = [Transmittal Record Query]![MaxOfTransmittal Date], [MDL-10].[REV 00 SUBMN LETTER] = [Transmittal Record Query]![MaxOfTransmittalNo] WHERE (((TRANSMITTALGEN.REV)="00") AND (([MDL-10].[Select])=[Forms]![TransmittalGeneration]![Text2]));
I have almost finished my current database but I was asked to create a log table/log file that would list changes made to every record. Now my current database don't allow duplicate records, so any advice pointing me into the right direction will be helpful. I have ran through the search area and found nothing that I can use. Can any one help me out in this specific problem. I picked up a few books and none of them give examples of such things. Thanking you all in advance...
