Main table about 3000 customers with 18 fields
Will input payments for each customer monthly and / or quarterly total about 12,000 per year.
Plan to purge payments over 18 months old frequently.
1) Put it all in one table or break into two ?
2) As far as db size wise and efficiency, would it really matter ?
Thanks in advance.
I'd like to say thank you to those who helped me with questions I had on my database. Even though some of you didn't even realize that you helped. Searching the forum yields answers to many questions.
So now that the database is complete, I'd like opinions. I'm attaching it as a zip file.
A couple of notes. I forgot to remove the database password so it's "fornax".
Also, being that I work for a very large hospital I cleared out my table and removed the logos.
I hid the menus and disabled the F11 key, but there is a hidden button on the far right of the footer. A double-click will restore the menus. Also try clicking on the logo--or it's place holder.
Okay, as mentioned in another thread, I was given a huge project involving MS Access. I need some opinions on what to do for this situation, and here's the situation. So far, unless suggested a better idea, I'm going to have two databases. One database will be the final version, the other will if I want to make additions, changes, etc. They will contain the same data as users will only be using one and a relationship between the two will be created. I can't remember how to do it, but I remember there was a way to prevent users from changing anything about the Access file with the exeption of what the form and the vb code allows. I plan on creating some type of login using vb. The only access to the database I want users to have is what options the forms I create give them. Please give your opinions/suggestions so I can figure out what to do.
Thanks much,
jmh
I have very painstakingly worked out a new database and I'm about to present it to the departments who will be using it. At various times words like 'password' and 'locked' have been thrown out but as of now I have not implemented any security measures. I started reading up on the topic this morning and the general consensus seems to be that unless there is a sincere need for them the security process is excruciating.
Does anyone have specific advice or opinions on access security? If I were to do anything, I think it would be to set up working groups only allowing users access to the parts of the database they are concerned with.
I would love any opinions or advice from people with experience of this on if I should recommend such measures or try to steer the department away from them.
I work for a property management compnay and they have put me in charge of coming up with a way to go some what paperless. What they are wanting is a way to enter a work order for a particular client (i.e description of work to be done for maintenance). This would then need to be printed in a report to be faxed over to the workers. This info would then need to be pulled out and placed into an invoice where, where items could be entered such as items bought ets.
My company is willing to spend thousands for software, but I think it is insane. to do so.
I was thinking that surely this could be done in access. I have an understanding of how access works (forms, reports, tables, etc.) but I have never designed one like this.
It does not have to be complex and it does not have to have a lot of formulas, I was thinking that the total could be placed in field when the invoice info is entered. Only really calculation would be adding the total of the items.
Anyway is this something that can be done relatively easily by someone with my experience, or is this to complex.
Thank You for your time
Hi,
We all know we're not supposed to duplicate data right? So was wondering how people deal with the deletion of Master records.
For instance let's say I have 3 tables.
tblSales
tblSalesSub
tblProducts
tblSales - lists the sales summary - stuff like SaleId, SaleDate, SaleTotal
tblSalesSub - lists the products contained within the sale.
tblProducts - is the Master list of products storing the product specifics
Now to prevent duplication of data I should just store the productId in tblSalesSub then reference that to tblProducts with an innner JOIN of some kind.
But what if the Master record gets deleted? The reference is broken and NULL values are returned.
How do you deal with this?
Don't allow deletion of these records?But just hide them when deleted?
Duplicate data by storing the product title and description within tblSalesSub?
Tell the user - tough cheese you shouldn't have deleted it should you?
Any other ideas?
Dan
Hi all: I currently have a database with a list of clients, and it is to be accessible to others to enter information on clients and to add new ones. However, I want to make it so that the user who wants to add a client can easily search the database for that particular client so that they won't enter a duplicate.
I have created a search function on my form, but then it occurred to me: what if there are two people with the same last name's? At first, I wanted a search operation to display a list of client names, along with their respective ID numbers and possibly a link next to each that would take the user to the corresponding record.
However, I have no idea how to do this, and I'm unsure as to whether or not this is the best way to tackle the problem. Please, someone show me the way!
Am fairly certain that I will be asked to design and implement a Machine Status Application.
Description below is very simplistic but believe you will get the idea
Basically simple stuff at Machine Operator. He clicks a command button on a display that signifies machine is okay or not. This updates machine status. Image it just toggles between Running and Stopped.
Each minute little process runs to at 1 to Run value or to Stop value. Okay so this will get a little more complex for sure.
Now I could end up collecting data on say 100 machines. Okay so I would not expect the status change to occur any more than once per hour on average so it would be a bit like having 100 very very slow users on the system.
Now the crunch. What effect (if any) would these 100 very very slow users have on performance and particularly the One much quicker user who is executing preset queries one after another for say 20 seconds every minute.
Thoughts and opinions welcome
Len
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies View RelatedI need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies View RelatedHi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
Ok my problem is this......
I have a form with a field "Property Status" on it. It has 4 possible values -
"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
Hi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?