I am looking at a database where the Primary Key is a composite of the customer's First & Last name. Obviously a huge problem! The funny thing is, the table was set up to with an Autonumber CustomerID, so all customers have this information already.
Here's the question: there is a one to many relationship with an orders table, how can I change the primary key from the composite key it uses now, to the autonumber field and not mess up the relationships?
I have tblWO_Points and column with firstname and lastname and dateWO column. What code do I use to prevent duplicate dates for same firstname and lastname? Also if a date is a duplicate that it wil ask if you want to go to that record?
Example: Jane Doe Wednesday, January 07, 2012 Jane Doe Tuesday, January 06, 2012 Mike Doe Wednesday, January 07, 2012 Mike Doe Tuesday, January 06, 2012 Mike Doe Thursday, January 12, 2012
So the same dates can occur for different people but the same dates cannot be recorded for the same person.
This is a useful trick: If you use a + to concatenate strings then it propogates nulls, if you use a & it doesn't...
So in the expression:
rec![LastName] & (", " + rec![FirstName])
If rec![FirstName] is Null then the expression (", " + rec![FirstName]) will be too, but the rec![LastName] & (Null) part won't be. This neatly drops the comma and space if the firstname is Null
I am finishing a project at my client, and they said that I could take a copy of the database I created home (i.e. so that I will support them ;) )
I have a table tblPeople, however, the only condition is that I have to change the First- and LastName columns in this table, since it contains personal information. I need to change the names for about 600 people (400 men, 200 women)
I was thinking of adding the First- and Last Names of movie stars, to keep the flair in my DB :) Does anyone maybe have a sample database with (movie star) names that I can use to populate my DB?
This is the SQL from a query for a Payroll report. It was an already existing report, so I'm trying to modify it to meet the business's needs. The problem is that there are multiple entries for WOLabor.Hours for a given day. These entries come from the invoicing program when a mechanic's hours are logged for billable time. So there may be 3 hours here and 1.5 hours there...but all for the same day. Payroll is just concerned with total hours worked...so I'm trying to combine all the hours for a given day into one variable or something and use that. I know this is long, but apparently a lot of fields are involved:
THIS GIVES ME NO ERRORS:
Code: Original - Code SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode; SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode; When I make the following changes to the SELECT section, I get this error:
You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.
Code: Original - Code ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ... ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ... Any insight is greatly appreciated.
I have 2 tables with the same field names, but different data. I need to check if one person is linked to different companies.Both are linked with CompanyID. When I look for a person via the last name (field is called LastName), it should show me from both tables the rows that this person is in. how can I construct this kind of query?
P.S. I want to select all the not just a few from both tables
I am redesigning an old database. There is a form that has an unbound combo box field that gets its values from a query. Basically, someone will choose a value from the combobox and a subform will display all records for that value.
Here is my dilemma...
In the old database, if I typed an N in that combo box, it would immediately go to the "N"s in the combo. However, in the new database...nothing happens when you start to type in the combo box. You either have to type the whole thing in or actually use the dropdown button. I have checked every setting imagineable...and I can't find how to get it to autofill based on the letters entered. It is driving me nuts!!!
Hi, I'm trying to build a mdb which holds customer details, which insurance they have, who sold them this and which Insurance company provided this. The relations I have to make are driving me nuts ! When I try to enter a new customer in a Form, there is a advisor added, the package the customer wants is added. But this shouldn't happen for the advisor and the package allready exists !
Who can help me with the proper relations ? Attached is screendump of the current relations.
I just started learning Access and I have created a database with 53 records, 3 tables and 1 Split form. I creared a new yes/no field and I am attempting to display "Completed" in green for yes and "Not Completed" in red for no. I used the correct code in the format field:
"Not Completed "[Red];" Completed "[Green]
This worked for different field before I created the split form but now I cannot get the new field to display anything but check boxes regardless of what code I use. It won't even let me use the default yes/no, true/false or on/off options.
I've been studying this for about 2 weeks and I've figured out quite a bit but this one thing is really iritating me....
Hehe... and since me and my girlfriend just broke up, that seems applicaple both in the dbase and the real world ;)
I have one Access Database. For now containing three Tables:
KlantNAW (customer adress data) with one primary key, Klantnummer (clientnumber) which is an Autonumber Data Type, Long Int, Increment, Indexed: Yes (No dupes).
CaseDateTimeInfoTable with one primary key, Casenumber which is an Autonumber type, Long Int, Incremental, Indexed: Yes (No dupes). Within this table I have included the Klantnummer field from KlantNAW as a Number, Indexed: Yes (dupes OK).
CaseTechInfoTable with no primary keys but with both the KlantNummer field and the CaseNumber field included. Both have a direct relationship to the tables in which those two values are created. Both values are now of type Number, both are Indexed, but Klantnummer is set to Duplicates OK and Casenumber is set to No Duplicates. I don't know if it might be wiser to just not index those values in this table, since they are already indexed in the tables in which they are created, if someone could tell me which is better in this case, please do.
I have created a one-to-many relationship (well actually, access seems to decide whether it becomes a one-to-many or one-to-one relationship) between KlantNAW.Klantnummer and CaseDateTimeInfoTable.Klantnummer with Join Type 1 and I've created a Join Type 1, one-to-many relationship between KlantNAW.Klantnummer and CaseTechInfoTable.Klantnummer. There is only one extra relationship left, which is a one-to-one, Join Type 1 CaseDateTimeInfoTable.CaseNumber with CaseTechInfoTable.CaseNumber.
So ehrm.... a recap: KlantNAW.Klantnummer (P-key) with CaseDateTimeInfoTable.Klantnummer KlantNAW.Klantnummer (P-key) with CaseTechInfoTable.Klantnummer CaseDateTimeInfoTable.CaseNumber (P-key) with CaseTechInfoTable.Casenumber
If I leave it like that the dbase works perfectly except for the fact that when I delete a customer the related case date/time info and the case tech info don't delete with the client account accordingly.
So, I thought I'd "Enforce Referential Integrity" and "Cascade Delete Related Records". I do not know if it would be wise to also enable "Cascade Update Related Fields". Could someone please tell me if that would be wise to do or not? And maybe even why... I have some beginner and more advanced books but can't be sure, probably because of lack of experience (or maybe lack of brains ;) ).
Anyway, back to the problem: when I make all the relationships to Enforce Rererential Integrity and I add a customer through a form (KlantNAW_InvoerForm) and click the "Create new case for this customer" button, the form I use for entering the case date info pops up nicely, but when I try to close that form I get an Error stating: "You cannot add or change a record because a related record is required in table "KlantNAW"". So, I click ok, then get a messagebox stating I can't save the record at this time. Do I want to close anyway? .... well, ok. Let's do that. And now the strange thing is that after that, when I do absolutely nothing other than pressing the create new case button again, enter the date/time info into the form and close it again, no errors or messages come up. Wel... that should happen the first time around right? What's wrong?
Now... I'm really lost. I'm new at this. I made some tables, queries and forms, set all options for all values, that which I didn't understand mostly tried solving or finding out on my own but now I'm hitting a brick wall.
If you guys and gals could help me out I would really be very grateful for that.
EDIT: I even tried making it more simple by removing all relationships and creating just one new one between KlantNAW.KlantNummer and CaseDateTimeInfoTable.Klantnummer (type 1, enforce referential integrity and cascade deleted records) but still, the same problem arises.
EDIT2: made sure it was not a form thing by just putting in data in the table data views directly. Same thing occurs.
I just started learning Access and I have created a database with 53 records, 3 tables and 1 Split form. I creared a new yes/no field and I am attempting to display "Completed" in green for yes and "Not Completed" in red for no. I used the correct code in the format field:
"Not Completed "[Red];" Completed "[Green]
This worked for different field before I created the split form but now I cannot get the new field to display anything but check boxes regardless of what code I use. It won't even let me use the default yes/no, true/false or on/off options.
I've been studying this for about 2 weeks and I've figured out quite a bit but this one thing is really iritating me....:eek:
I am trying to create a chart on a Form. In Excel I have no problems arranging my data. This is my first try for charts in Access. I have a table. I created a query summing two fields of this table:
Total Workers Total Workers: DCount("[WorkerNameLast]","1-StationVerifyTbl","[ClockNbr]<>'*'") Total Workers = 120
Total Verified Verified Workers: DCount("[WorkerNameLast]","1-StationVerifyTbl","[StaPrimary]=True") Total Verified = 43
So far so good. That is the correct information. What I want in a Chart is two bars side by side. One bar at 120 and the other bar at 43. I have tried everything and cannot get this to work. I am getting confused on the Axis, Data, & Series. I think I need another Field somehow. The totals I would call Monthly Totals
Could someone please help me get this thing started. I have wasted a week and feel like I have learned nothing except how to count in the Query.
After this I would like to be able to show a percentage of those verified. I have 120 workers and 43 are verified. That would be approx 35%. Anyway I am assuming that there is a solution and that I am just floundering for nothing. Thanks in advance.
I am biulding a database with quite a few subforms but now I have a wierd problem.
I am building a form that displays upcoming IT courses. so I have sumbforms for word, excel, outlook... you get the picture.
All the subforms work well except the outlook subform. When I create the subform with a wizard. When I go into form view mode to check, the form displays Outlook courses. When I save the form, the outlook subform then changes itself to Excel!
When I open the excel form and the outlook forms independently (not as subforms) the work fine and the data sources (queries are correct)
I've tried deleting the subform over and over again but everytime I hit the save button, The outlook form reverts to Excel.
I've gone as far as checking if there is any code behind the main form that makes this happen, but nothing.
Can anyone heelp>? this is driving me crazy! :confused:
I am pretty sure that this is crazy! I have attatched a word document (the quickest way for me to show and explain at the same time) that shows in a print screen picture what I want done with this form. There is colored text explaining. Any questions feel free to ask. I am pretty sure that this is ridiculously tough and am not sure that it is even possible with access. Please help, and may the gods give tons of wealth to whomever can solve my problems.
I searched on IMPORT, but didn't see anything like the problem I have.
I have a large Excel file formatted thus; COLUMN A COLUMN B COLUMN C 1 Full Name 2 Full address 3 City State Zip 4 Phone SSN Sex
Alas, as you can see, the first four rows contain information on one person, then Column B contains just their SSN on Row 4, and so forth.
Row 5 begins the cycle again. This goes on for 160 people.
Is there a way to get the employee information contained in Column A in a 'nomalized' format, such as Full Name in Col A, Full Address in Col B, and so on?
Unfortunately, it's illegal here to whack the person who provided this data to me.
I have a table with starts with an autonumber as a primary key. Recently it has been giving out crazy high and sometimes negative numbers when a new record is created
How Can I make this work Right? If anyone knows how to do this please Help. If it must be done in code please provide an example. I need to get this worked out this weekend.
Hope someone can help. My code behind my command button is not working. It is opening Report1 but not the criteria in the query.
I have a form (form1) with 2 unbound text boxes on, called "txtDatefrm" and "txtDateto" in which dates can be entered. There is also a command button on the form which searches for these dates and then opens a report(Report1). The report's control source is a query. (query1). On the ExpiryDate field in the query I have the criteria: Between ([forms]![form1]![txtDateFrm]) and ([forms]![form1]![txtDateTo])
Behind the command button I have the code: Dim stdocname as string Dim ExpiryDate as Date txtDateFrm.setfocus ExpiryDate = txtDateFrm.text If Trim(ExpiryDate) <>"" Then stdocname = "Report1" Docmd.Openreport stdocname,acViewPreview End if
Ok so I have my employee database and each employee has a bunch of different professional credentials that expire at different times. In layman's terms, Is there a way that I can set something up to automatically e-mail the employee to let them and their manager know that their credentials will be expiring within a couple of months?
Hello, When I try to run any query that contains a specific table I get a message a Not enough memory error. I can open that table which, contains 47 thousand records and view that data unless I get to one record then the entire database crashes. I have tried to Compress and Repair and the database crashes. When I say crash I means it completely disappears and I get the send error to Microsoft Message. I can not select record due to it crashes when I get to it. I have also tried selecting record by going to one above the quickly scrolling to a record way below it and it errors and crashes. I have tried to delete it using a delete query and it only returns “Invalid argument” when I try to select or delete.
ANY suggestions or ideas would be greatly appreciated.
Run-time error '438'. Object doesn't support this property or method
The command:
If Not IsNull(DLookup("[JSANo]", "JSA", "[JSANo] = '" & Me.JSANo & "'")) Then
I think the rest of the code is uncessary. I have searched Google over and over. From what I can gather this error can mean more than one thing.
I have moved the data into another new exact same field, deleted the old field, renamed the new field to have the old field name. Still keep getting this message. At one time, I had messed with the index settings on the field. Thought I saw something about that can cause problems.
I have this exact same command on another field name and it works perfectly, but on this one I get the above error. Ideas?
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain [IntakeMainID]
tblIncidentDetails [IncidentdeatailsID]
tblPersonnel [PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:
The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.
The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.
The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.
The relationships are set up in the relationships window and referential integrity imposed.
Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).
If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.
