My Current Data Model... (Please Take A Look)
Jun 29, 2006
What's up everyone, I'm new around here and to db design as well...
I'm posting a really rough data model for the first real database I've been putting together.
I'm still in the early stages, and the concepts behind db design are still pretty foreign to me, so there are bound to be many errors I hope to iron out ASAP.
One specific question:
On all of the data models I have referenced, the "OrderDetails" table has no primary key, can someone help me to understand this? Or explain how it is wrong... Thanks
I was hoping some of the experts and advanced users around here could take a look at this and help steer me in the right direction.
Thanks!!!!
View Replies
ADVERTISEMENT
Jan 13, 2008
Hi I have designed a database for a small organization. It is a ver small database. can any one verify and tell me the mistakes in my design. It hardly takes 20 min for the people like you. This is my first step in designing. I am attaching the zip file which contains dbm file and business requirements. Complete physical data model is there.
especially in "category_tests" table i feel that i have done some thing wrong.
Thanking you all.
View 2 Replies
View Related
Jul 10, 2006
Hello,
I have a fairly complete data model, my question is, how best to create tables for entry where I do not have subform after subform, and subforms in subforms. With subforms for the subforms and subforms on top of that.
I am about to start pulling out some teeth because at this point it sounds fun compared to this!
Do I need to use a query to base my table off of to build my Customer entry form? Or is my only option to use 15 frikin' Subforms? I I use a query, does that cause problems with data integrity?
I am having trouble finding useful reference to this, both online and in books I have purchased.
Normalization is fantastic, I understand.... But besides the crumby Northwind sample DB that everyone and their brother directs you to, is there an actual useful sample to look over that illustrates the use of a normalized data model?
I have grown more than a bit irritated with this, mainly because of my own inablilty to make progress in putting this project together.
I would like to create a simple to use, streamlined systems that promotes efficiency and speed, not a super convoluted system that has 85 subforms on every form in the DB.
I would REALLY appreciate some feedback/direction here, I am about to give up on normalized data and go with a simple flat table approach.
I would rather have redundant data than indecipherable forms that I have to answer incessant questions about.
Thank you,
- I am Rick's Utter Frustration
View 2 Replies
View Related
Apr 13, 2014
I am looking for a data model to define and track proficiency/currency.
Example:
You earn a licence to do X. You need to renew your licence every 2 years or so. But also, to maintain currency, you need to do what the licence gives you the right to do often enough, or else you need to do a test.
Specifically, you need to do X a specified number of times N within, say, last 3 months at any one time to maintain currency. If you have lost your currency, you need to do a test/check. So at all times, you need to have N times on record within last 3 months, or a test.
In addition, to do X legally, you also need to document that you have done X at least 10 hours within, say, last 6 months, or that you have another test within those last 6 months. This is independet of the previous requirement (concerning the number of times you did X).
Both requirements must be fulfilled at all times for you to do X legally.
So, any suitable model out there? Essentially, something capable of defining and tracking currency for various ratings and license privileges of pilots would do just fine.
View 2 Replies
View Related
Jan 9, 2013
I have researched the 'party' data model but it is a bit too complex for what I'm seeking. For those familiar with it, I don't really need the intermediary relationship from-to tables.
I'm interested in ideas about setting up a data structure that will allow users to search contacts or select contacts in dropdowns regardless if the contact type is a person or an organization.
Obviously the fields needed for both are different and the biggest issue is the name field because the person contacts are
The way I am accomplishing it now is writing the company name, or "first name " & "last name" for a person, to kind of a bridge table when a new record is inserted into the person table or the organization table...kind of inefficient.
Is this a relationship thing or should I just write a function to create a temporary recordset when needed?
View 3 Replies
View Related
Apr 26, 2005
I am trying to build a table that lists aircraft viewed by plane spotters.
In the “aircraft_spotted” table I have two look up wizards. The first lists the make of aircraft and the second lists the type.
What I am trying to do is filter the models down to the maker.
If I select Boeing I will only get Boeing models and nothing else. The same would apply to Airbus and so on.
I would like to keep this information contained within the table without using a query but if I have to use the query for the model lookup then so be it.
This one is driving me nuts. :)
View 1 Replies
View Related
Dec 1, 2005
Here is my problem:
Say I have a products table:
PRODUCT
========
Prod_id
Prod_name
Dept
Type
:
It is now possible that some of the products can also be a product
group, and a product group would then consist of multiple products.
(Note: if a product is a product group as well, both of them have
the same names. therefore for consistency, if in future someone
alters the product name, that should be reflected for the product
group if that product happens to be a product group as well)
So, I started of by thinking that the products table should contain
a binary filed like "Is_this_a_ProductGroup", where for any product
that is also a product group, while creating or editing the product
the user would flag this field "Yes" and "No" otherwise.
S, my revised products table is like:
PRODUCT
========
Prod_id
Prod_name
Is_it_a_ProductGroup
Dept
Type
:
Based on some earlier suggestions in this forum, I tried to create a
junction table like
PRODUCT_PRODGROUP
=================
Prod_id
Prod_Group_id
and joined the Prod_id above to the Prod_idin PRODUCT table and
created a copy of the PRODUCT table and joined the Prod_Group_id to
Prod_id in the PRODUCT_Copy table.
I would like some help now on:
1. whether this design is really correct?
2. If so, how does one go about the process of data entry through
forms for these tables?
Any help is much appreciated. thank you
View 1 Replies
View Related
Jan 30, 2015
In my transaction table, I would like to find out the latest transaction dates of each family models. It sounds a simple Max function can get the desired result. However model number consists of the first 6 characters for the family model and the rest for the versions (variants). The Max function fails to work in this scenario. Is there any other way to group the model numbers once the 1st 6 characters are identical (the rest is ignored)?
View 3 Replies
View Related
Jan 31, 2006
Hi,
I've been working hard at learning Access for past two months and spent a lot of time Googling and speeding through Alison Balter's books and at a slower pace the Paul Letwin’s Developers Handbook set (fantastic but I probably need a year to absorb the incredible detail).
I've built a prototype reconciliation tool using the recommended FE-BE model. I opted for a total ADO solution and have had great success in putting together a fast and useful tool for 10+ users.
I avoided using Workgroups in my design and developed my own basic sign-on authority model which allowed an Administrator (don't confuse with Admin user in Workgroups) to add new users who can use the Access app. The allowable users and which ones are signed on are stored in the BE. This all works reasonable well until network issues occur and the signed on user record on the BE is not reset as should be if the user signed-off genuinely.
This is where I realised I need to be able to monitor who is connected to the BE. There is a way of doing that using ADO which I can get to work. I then noticed because all users are signed on with Workgroup Admin as default I could not differentiate between users. I then decided to implement Workgroup authority retrospectively.
I decided to place the .mdw on the LAN with the BE (this seems to go against conventional wisdom but could not workout how else I could achieve what I wanted to). This enables the two Administrators in different places to maintain users via the FE. I use the /wrkgrp command line flag to point to the correct .mdw.
I then started getting all sort of problems . . .I'm still in testing . . . not so bad but deadlines are looming and I'm worried.
Sorry, I digress. . .the problem I seem to be getting is when I open the FE :
Run-time error -2147217843 (80040e4d)
Cannot start your application. The workgroup information file is missing or opened exclusively by another user
I also have problems developing the FE with the workgroup file in place. It seems to complain about another user is locking the FE.
The problem is a little verbose but just trying to paint a picture . . .now, this is what I'm after. . .I need the following ability:
1. Two Administrators who can maintain users. When a new user is added the Administrator chooses a password.
2. When the new user signs on the user is prompted to enter new password.
3. The administrators can view who is genuinely signed on and reset accounts appropriately.
I am currently studying the Access Security sections in the Paul Letwin book to better understand what is going on. It does seem retro-fitting an app to a workgroup model is not straightforward. Admittedly, I should have considered Workgroups in my design before starting (I will know).
Thanks for staying with me if you're still reading . . .any suggestion/pointers would be great.
Lastly, using Access 2000 : O/S W2000
View 2 Replies
View Related
Mar 18, 2013
I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.
View 14 Replies
View Related
Oct 28, 2007
I have a task that will require me to subtract the current time from the previous time as long as the records fall within the same Extract Count. For example:
I have 8 transactions associated with ExtractCount #2 and 8 start/end & processing times. I need to subtract each row's time from the previous row's time. I have a conceptual idea of how to do it but need real help to figure out how to do this using a query.
Thanks,
Phoenix81
View 4 Replies
View Related
Mar 6, 2007
Could someone point me in the right direction on how to statically store current pricing for a product in an invoice database, whereby future price changes would not change pricing on past/previously created invoices...?
View 6 Replies
View Related
Jan 26, 2006
I'm a bit new at all this but am trying to produce a query that filters on dates within the current week. Is there any way of doing this? I now how to do it for the day but at the moment I'm having to manually input 2 dates to get data in between them. Is there a command that will automatically detect the week and pull up dates within that?
View 8 Replies
View Related
Jun 5, 2014
I'm trying to make a button on a form that pulls data from a table in a database on a network share to the current open database (with the form).
Database on network share info:DB location: est.com est estdb.accdb
Table in DB: Table1
The current open database has the exact same table:Table2.In the following VBA line I get Runtime Error 3126: Invalid using of bracketing in name '' (The quotes at the end are 2x ')
Code:
DoCmd.RunSQL "INSERT INTO Table2 SELECT [ est.com est estdb.accdb].Table1.* FROM [ est.com est estdb.accdb].Table1"
I tried everything with changing the [, " and even the path name of the external DB as variant of type string. Nothing seems to work.
View 3 Replies
View Related
Feb 6, 2005
I have a Form> [Management]
I have a SubForm> [Management_History]
There is data in the [Year] field and the [Season] field.
I placed a button on the subform to create a report based on the data of the current "Sub Record" .
Basically, I want to use the data in the subform to create a small "Lookup" report.
OK:
I have the Management Form
I have the Management History SubForm
I Placed a Button Called PRICE LIST
The button kicks off a Macro that previews my PRICE LIST REPORT
A Query is the RECORD SOURCE for my PRICE LIST REPORT.
In the QUERY, I have two fields that I specify criteria "hopefully" based on the sub form record I am viewing.
I does not work on the SubForm. However if I Open the SUBFORM directly, the report pops up correctly.
What syntax in my QUERY is required to GET the data right frm the current SUB-FORM??
I tried the following:
Like [Forms]![Management]![Management_History].[Year]
Like [Forms]![Management]![Management_History].[Season]
View 2 Replies
View Related
Mar 10, 2014
I have a query that shows data from January to March (which is the current month). is there a possibility to exclude the current month
The query has the following fields
SegmentDate
PCC
AgencyName
SegmentData
View 12 Replies
View Related
Mar 14, 2013
I have a report that shows all visit dates and the rep that did the visit. How do I only show the most current data. I use an append query to add records to a specific table. The report pulls from that table.
View 1 Replies
View Related
Mar 8, 2008
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
View 2 Replies
View Related
Jul 30, 2007
Hey all,
I am working on converting someones Paradox Database to Access and making some modifications.
I have a table with people in it and they are linked to a number of interests. But i am struggling to create a query so that on each page of the form for the person i can call the interests and display them on the form as a list...
If anyone could point me in the right direction that woul dbe appreciated!
Give me mysql and a bit of php anyday!! hehe
I am using Access 2007 but i have used access before so i should be able to work my way through instructions for 2003.
thanks
RF
View 4 Replies
View Related
Oct 9, 2013
I have a table which is used to record electric billing period dates, total kWh and costs for a list of locations each month. I would like to compare the quarter total kWh and costs for this year "2013" against same quarter last year "2012". I have created two queries, the 1st is capturing data from 2013 and the 2nd is capturing data from 2012. I created a 3rd query which joins the 1st and 2nd together by month where fields match. Both tables have the same fields, however there is more records in 2012 than 2013. How can I join the tables together so months that have no data are left blank?
View 14 Replies
View Related
Sep 16, 2013
I want to export details from a table in access to a word document or any other document that will keep the current design and let me make changes afterwards.
View 4 Replies
View Related
Apr 14, 2014
I have a report that I am trying to show data for the current year, but with a record count.
So, for example: I have 3-OVI, 3-Conduct Unbecoming, and 3-Did not transport for the current year.
When I run my report, it will show the above data for the current year, but in this format;
OVI-1
OVI-1
OVI-1
Conduct Unbecoming-1
and so on.
What I would like is;
OVI-3
Conduct Unbecoming-3
Did not transport-3
Total-9
If I take out the date code for the current year in my query, then I get the desired results but I get all data.
If I leave the date code in, then I get the data for the current year but I get the first example above.
View 3 Replies
View Related
Jun 27, 2013
I am extremely new to Access. I have my database up and running ok(ish) and would like to know if in my data entry form, I can have the "Date Entered in Database" box display the current date that does not change from day to day. I know you can use the "Now()" function but won't this just change everyday?
View 3 Replies
View Related
Oct 1, 2013
I have a very large Access application and use a hidden "main switchboard" to store data such as the current customer ID. Prior to the advent of tempvars, I embedded a key control into many query's underlying combo boxes, list boxes and forms criteria:
"Forms![main switchboard]![currentcustomerid].
The problem arises when the user quits the application on with forms using this in their underlying query(ies). As far as I can tell, the application closes forms in the order in which they were opened, so the "main switchboard" closes first. Then, for some reason, the current form has a requery and the user is presented with a prompt to enter the parameter "forms![main switchboard]![currentcustomerid]. I tested and found I could replace the criteria reference to a tempvar to "fix" the problem. I wonder if there would be a way to open every query in design view and do a search and replace on the criteria statement.
View 9 Replies
View Related
Aug 27, 2013
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
View 1 Replies
View Related
Dec 4, 2013
I have 2 form, form1 and form 2 (picture attached)
In Form 1
(main form) there are "reference" field with key pressed event
then call form 2 (list of reference that user can choose)
In form 2
the problem is, when "enter" key pressed (keypress event), selection will move to next record
so the data that transferred to form 1 is wrong (next record)
The question is : how to save current record in form 2 before enter and send the data to form 1
View 3 Replies
View Related
