General :: Sub-Clients For Records In Table
Jul 3, 2013
I have a table that contains clients. There are a few select times when there will be one client organization but I will need to have two entries--say when one department has a separate contact & record of events than another department.
In most cases these separate departments can be treated as entirely separate clients however in some ways they should be counted as one--such as when counting the number of clients that we serve.
What is the best way that I can accomplish this?
My current idea is that when this situation arrises I have some type of command button that will create a new client record in the table, copy the information from the first record, then I can change it as need be.
I don't know if this is possible, how to do it, and how to set up queries that will recognize the two entries as one record. I don't even know if this is the best way to do it
View Replies
ADVERTISEMENT
Apr 9, 2013
I'm creating this database for a claims management company. Yesterday I learnt about making a many to one relationship between the claims and clients tables.
Turns out its more complicated than that. Since I'm dealing with claims, a person may add their partner as the second claimant. (usually the case in mortgages)
So where parties A and B are in a relationship:
person A may claim on their own
Person B may claim on their own
Person A may be the primary claimant and add person B
or conversely person B may be the primary claimant, adding person A.
The end goal is to automatically fill in a financial form (making use of a mail merge). So from what I know I think this means:
that grouping them as one contact is not going to work as I need them to be able to do a solo claim, and also I will not be able to easily change around who is the primary or secondary claimant, which will then carry over onto the mail merge.
So I think this means I need them as two separate contacts. However, I don't know how to create a link between them to easily add their partner to a claim.
Would this also mean I need a many to many relationship between clients and cases.
View 4 Replies
View Related
Jul 2, 2013
We have a split ACC2010 database, with the back end on our server and 5 front ends on 5 client PCs. I hadn't run a compact/repair on the back end for months. When today I tried, I got the 'database is locked' error. The lock file showed 'Admin' logged on at all 5 clients. The front-ends had been closed on all 5 clients. We do not have a user called 'Admin'. Because my issue occurs for all clients, it might be caused by some sort of programming error in the front end.A VBA bug (implicit comparison to a control in an If .. Then construct) caused this type of behaviour but was resolved in Access 2007.
View 12 Replies
View Related
Sep 17, 2014
I have a requirement to send a email to individual clients via my access database 2003 . I am using the email address of the client in the current open form . I then created a query using the id to filter that particular client, this query then becomes my record set.The content for the email is held in a .txt file , On my c drive . Every things appears fine however the fonts that I select in the .txt file are not being use in the actual email body. I created a word document with my preferred fonts and copied and pasted them into the .txt file but on sending the email the selected fonts do not appear.
I have attached a demo of what I am trying to do . To test enter your own email in the email address field of the form . Note the .txt file is on the C:/ drive.
View 4 Replies
View Related
Jan 7, 2006
Hi, based on previous advice I had merged my customers and owners into one table and selected whether a client was a customer, owner or both via a lookup table.
This is working fine, however I must assign a rep to each owner, I'm not quite sure how to do this, I need to be able to assign a rep to an owner in the clients table, I must also make sure than a rep can not be assigned to someone who is just a customer obviously.
I have attached the database (http://jonroberts.redirectme.net/database.zip), hopefully that will make things more clear.
Thanks in advance.
View 2 Replies
View Related
Feb 4, 2014
I have a Client database table in Access. I now need to add a simple order table (related) to the client table. I have a client ID field set to autonumber in the client table. As I start to create the orders table I'm not sure how to link the two so that I'm not entering data twice and have assurance that they are tied together.
View 4 Replies
View Related
Dec 2, 2013
Database has a form to add a new item. This info is stored in the Item TBL. There is also a table that lists available UPC codes called UPC TBL. When a user enters a new item a dialog box will give them the choice to add a UPC code for that item. Clicking no - nothing changes and user return to the original form (no brainer) But if they click yes what I need to happen is:
1. The next available UPC code in the UPC TBL is entered into the Item TBL field for UPC.
2. The UPC TBL is updated to remove the assigned UPC, leaving it ready for the next assignment.
3. - and for another day - I need to create a message when the UPC table is empty because all UPC's have been assigned.
View 7 Replies
View Related
Feb 6, 2013
I have Office 2010 working on XP Pro SP3.
This is the first time I have tried to append/import a bunch of names and addresses from a spreadsheet into my access table.
I thought it would be easy with all the integration of applications but it's not. What happened to the bit where I could match the fields where it's not quite the same name and set the type so it worked OK.
All I seem to be able to get at at the moment is a wizard which does a very poor job and won't allow me to append the records. I tried exporting the Excel as CSV - same thing - error messages.
Is there a way of matching up the fields so everything works? What am I missing - this should be a cinch right?
View 3 Replies
View Related
Sep 3, 2013
I select records from a table based on criteria:
< Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [tblLVRWrittenStatements] WHERE [tblLVRWrittenStatements].[seedrsID] = " & seedrsIDVar & "") >
I now want to append these filtered records to another table called ArchivedWrittenStatementsTable;
< CurrentDb.Execute "INSERT INTO [ArchivedWrittenStatementsTable] SELECT * FROM rst" >
Would be lovely except rst not recognised. Is there a way of achieving this without having to <addnew etc > looping through the records.
View 1 Replies
View Related
Jan 9, 2014
Still working on the registration and badge print system for their upcoming exhbition the company I do a bit of work for. Managed to solve most of my problems but have got stuck here.
Basically each of the badges have a barcode (code 39) on them which is in the following format...
*1000000123$M*
(*'s required for the barcode to work, the numbers are the badge number and the $M is the enter command (i think))
Basically we need a form that allows us to scan the barcode (the number of the barcode is the Primary Key in the tbl_Attendees), and for this to update the "Attended" (Yes/No) field to yes. This bit I can do with a simple update query, but we need some form of confirmation, something simple like a line of text showing up on the form saying
"Joe Bloggs of Leeds registered as Attended".
View 3 Replies
View Related
Oct 9, 2013
I would like to know how to add new records into a table from another table.
I have 2 tables A & B.
A is the main file and if customers wanted to add new data into table A and if he clicks on add records in table A then table B needs to open. So that they want to add records in Table B. Later we will update table A from B.
I used macros to do it but it's not fine.
View 1 Replies
View Related
Jun 4, 2013
I need to add new records to a table depending on what the cbo box displays. I figure this is the way to do it but I it's not working, I have only writen the first select,
Private Sub Comando6_Click()
Dim dbPalavraChave As DAO.Database
Dim rstchaveCFEPC As DAO.Recordset
Set dbPalavraChave = CurrentDb
Set rstchaveCFEPC = dbPalavraChave.OpenRecordset("chaveCFEPC")
[code]...
View 9 Replies
View Related
Aug 11, 2013
I have two tables, one is GENETIC and the other one is BoneSampleDNAprofile. Both tables have the field CY-Code. I want to create small form with 2 text box fields and one command button on the form to be able to insert CY-Code from the table GENETIC to designated DNA_LAB_Code in the table BoneSampleDNAprofile.
In that small form I would type DNA_LAB_Code and CY-Code where after pressing command button will insert CY-Code for the designated DNA_LAB_Code. This is continuous process after our Laboratory receive results from DNA laboratory. I have attached two .png files which are example of two tables.
View 2 Replies
View Related
Jun 13, 2014
I am losing records in one table when I delete from another, i.e., I do want to delete records in one, but the related records in the other are also being deleted. This was not happening until I did a recent up date.I created a database in Access 2002 about 8 years ago and my client has been using it successfully ever since. I have occasionally made updates and enhancements. They have over the years upgraded and are now on Office 2010. I'm still using Access 2002.
I recently (3 weeks ago) added a new feature and installed the new code. The database is split, code and data. The new feature was working well, but suddenly they were losing records and they cannot operate until I have it resolved. I spent 2 hours today in their office and could not work out why it was happening.
I have a straight forward client table with names and addresses and some other information. I have another table which links together Clients, Counsellors and Supervisors and works out a room allocation for Therapy sessions. Once the Therapy sessions have been completed, we need to delete the Client, Counsellor, Supervisor + room allocation, but we do not want to delete the Client record in the Client table. The User is offered the opportunity to either archive the client record or not archive it, that's all. The Archive procedure is simply to put a tick in the "Archive" field, not delete the record.
However, when the Client, Counsellor, Supervisor + room allocation is deleted, it is also removing the Client record. I do have a relationship between Clients, Counsellor, Supervisor, but it doesn't have "enforce referential integrity", it is just a one-to-one relationship. The odd thing is that when I delete the allocation here at home on my Access 2002 system, it does not delete the Client Record, but it does in their office, using exactly the same code and data.
The Allocation is on a sub form and the way they delete is by highlighting the Allocation and then clicking on the X - Delete button on the Access menu (the program is not very sophisticated, but has worked until now). I have some code in the "on delete" event, but even if I take out all the code and just allow the deletion with the usual Access message "you are about to delete 1 record... " I have seen that the related Client record gets deleted at the same time. it does not happen on my system, only in their office.
View 14 Replies
View Related
Jan 9, 2014
I need to know the best way to append,
table1=productid,ProductName,Qty to table2=Productid,productName,Qty,date daily
I want to be able to append Records daily from table1 into table2 OnClose. But i do not want duplication of record in the same day.
If changes occurs in table1 after append,table2 should be updated using If conditions
View 1 Replies
View Related
Oct 1, 2013
I've set up a piece of VBA code to loop through and email various recipients data, in the form of records within the email body. This works fine, but occasionally produces an error whereby the email output body lines are duplicated spuriously. This only seems to happen when running a large number of email loops.
The data source for the email body content is a table, which for each 'loop' is refreshed with new data by 1) deleting records from that table, and then 2) appending data to the table.
The portion of code for the loop & email:
Do
'Build outputbodytext
DoCmd.OpenQuery ("EmailQ2")
DoCmd.OpenQuery ("EmailQ1")
'set up email
Set rec2 = CurrentDb.OpenRecordset("outputbodytext")
strSendTo = rec2("Email")
[Code] .....
View 3 Replies
View Related
May 14, 2013
Is there a way to select non-contiguous records in an Access table or query?
View 3 Replies
View Related
Feb 25, 2015
I have records using a select query that I am sending to a make table. I would like to have those records excluded from being used again for 180 days, at which point they can be used again. Essentially, I have an ID and an email address which gets stored in the Make Table. I would need to ensure that if we send an email out in Week 1, we do not send an email again for another 180 days if there is activity from that same ID. On day 181, the ID/email address can then be resent.
View 6 Replies
View Related
Jul 2, 2015
I am looking to add a field to my table with a timestamp for each new entry.
I am working with a split database. All of the records are inputted by forms (in the back end by other users). I want to be able to see the exact time that a record was added, but without having to add this field to the actual form. I just want to be able to have a field in the table that shows me when the user submitted an entry. Is there a way to add an automatic timestamp field to the table?
View 1 Replies
View Related
Jul 10, 2012
I think what I want is:
1 table(1): record of people & contact details
1 table(2): list of events with check box's with the names of people from the other table
1 report: listing how many events people have attended.
When I add a new person to table 1 I want a field to be added to table 2 in the form of a checkbox, also when I delete this person I want this field to be deleted in table 2.how to make this an automated process.
View 1 Replies
View Related
Jul 17, 2014
I have a database created in Access 2003 about six years ago. I’m the sole user of the Application, which I use to keep track of my personal assignments and to quickly access other resources. A search form was added and tweaked over time until it contained the features I desired. The search form uses a subform to display a list of records, with the ability to click a record‘s "Edit" field to open and update that record.
In about March 2014 we upgraded from Office 2003 using Windows XP to Office 2010 using Windows 7. All users on other Applications that I’d designed and support experienced missing Reference issues, but those were resolved rather quickly. I'd been off work intermittently for about six weeks between April and July and gradually started noticing other issues with my personal assignments tracker that I did not immediately associate with the upgrade. Early on, I noticed that I could no longer delete or rename database objects on several of the databases that I'd designed and support. Eventually, I also noticed that I was unable to create new records and received SaveRecord error messages associated with the subform. I'd encountered SaveRecord issues before and went through previous steps to resolve, but to no avail. Eventually, I came to the conclusion, right or wrong, that all of my recent issues were associated with the upgrade.
I found through online research that my loss of ability to delete or rename Objects was possibly due to the loss of Object Owership and Permissions, which was associated with my Security Workgroup. I confirmed that Ownerships and Permissions had been lost and was unwilling to recreate the database, just yet. No user of other Applications that I support reported any problem creating new records.
I know now that new Access doesn’t have the Security menu item that allows Workgroup creation and joining. After months of online research and trial and error, I was directed to Microsoft Knowledgebase Article 918583, which provided VBA code that allowed me to Run the Workgroup Administrator to rejoin my previously established Workgroup.
After rejoining my Workgroup, everything seemed to return to normal. I could delete and rename records and was able to create new records. After a few days, I noticed that new records were not being displayed in the search form right away. I confirmed that the records were being added to the main table, but still not available in the search form, queries, or reports until after an additional record had been added. In other words, for example, in order to view record 1, a record 2 would have to be added. In order to view record 2, a record 3 would have to be added, and so on. This makes it very tedious to enter new assignments.
I believe the present issue is greater than my search form, because the newest record is not only not available in the search form, but not available in any other form, query, or report. I have no clue where to start.
View 2 Replies
View Related
Apr 3, 2014
I have a work order system that people use but it somewhat randomly puts blank records into my table. I've added a lot of validation checks when submitting, closing and resetting the form and limited the way people can exit out of the form to fight this issue but it still happens.
View 3 Replies
View Related
Dec 25, 2006
I have a table -'client' with 5 fields,
id
name
address
email
mobile no
I want to see only those clients who has email id.
I know its simple but not so simple to a layman like me :)
Please help
View 3 Replies
View Related
Jul 29, 2005
I have a database with a list of clients, their enter and exit dates. I need to do a query that will count how many clients we have on a specific date. Keeping in mind that clients leave. I can do a query that will prompt user to enter a date. However, how would I count the total number of clients on that date? thanks a lot for any help!
View 3 Replies
View Related
May 2, 2007
Hello, I am in the process of trying to get my database so i can access over the web.
Currently over the Lan I have the main database located on the server, and have created a replication for the two clients. This works over the lan where i can open the original and sync etc etc. How can i get this to work over the web so I have the same functionability as the lan method but over the web. I thought of some kind of interface but im not sure on how to do this.
Thanks in advance
Craig Clarke
View 3 Replies
View Related
Nov 18, 2007
Since I left school I have had to jump into application design on my own because I am finding it difficult to find work without 1 to 3 years of experience typically required by the current job market. This leaves me without any gut feeling for how to charge my customers. Charging by the hour is difficult to do fairly because I am not as efficient as more experienced programmers. Charging by the form, query, report, macro, or module, however, doesn't accurately capture the time variable associated with extensive scripting. Any guidelines offered in regards to charging and possibly ballpark rates would be hugely appreciated!!!
View 7 Replies
View Related
