Advice On Structure & Concept
Jul 14, 2007
I am designing part of my database to simply the production of predictable quotations. (and have been for the last 6 months). My quotations tend to be for one product whose price varies based on the number of software licences. In each quote there will be the price for the software and the price for installation.
I will have a TblItems. In that table will be all of the items available for quotations and may include, for example, the following
Group (5 User system) Item, Description, Price, cost
In this group there will be two matching items
On my quote form i want to have a drop down field which will allow mw to simply select the number of users and then i want Access to take any item which belongs to the 'number of users selected' to another table and i will then include a quoteID etc etc.
Can anyone advise me on, the main thing, the kind of database concept on how to achieve this please? :confused:
View Replies
ADVERTISEMENT
Nov 8, 2005
hi,
i need a db to monitor attendance (I know there are examples that do this but I need to satisfy my own curiosity and haven't seen one exactly like i need). My plan is to set-up the tables as below. I now have one-to-many relationships. There are many students. each student will study many courses.
I would like a form that shows the student info and a subform that shows all the courses they are on and weeks 1 -10. i could then use the yes/no attribute to record an abscence. later i would then need a query to see which students had missed two weeks in a row or more than 3 in total.
I think i need a form bound to tblStudents with a subform control that is displaying a form that is bound to tblStudentCourse. i am then a bit stuck as i need another form that will display the course records for each student. am i off track??? any ideas would be great. thanks.
tblStudent
StudentID (PK, number)
FirstName
LastName
tblCourseID (PK, number)
Week1 (yes/no)
Week2 (yes/no)
etc.
tblStudentCourse
StudentCourseID (PK, autonumber)
StudentID
CourseID
View 3 Replies
View Related
Nov 17, 2005
Hi ,
I am developing an access application that will be used by our Marketing Dept to create BRIEFS for our AD agencies.
The Brief has the following structure
1. Option1
1.1 A1
1.2 A2
1.3 A3
1.3.1 AA1
1.3.2 AA2
1.4 A4
2. Option2
2.1 B1
2.2 B2
2.2.1 BB1
2.2.2 BB2
2.3 B3
2.3.1 BB3
3. Option3
........................
........................
........................
The users will be basically selecting from a list of Categories,Sub Categories and Sub-Sub Categories.
I am a bit confused as to what kind of Database Structure should I use for this kind of requirement.
Right now I have a table tblBRIEF that conatins the basic information about the BRIEF like
Project Name
Company Name
Date
Responsible etc etc
My question is , where and how should I store the selections made by the user for each of the Categories , subcategories and su-sub categories.
Should I have a table for each of the Categories and Subcategories and have a relation with the BRIEF table ???
You advice will be apprecaited?
View 1 Replies
View Related
Nov 14, 2006
I need some advice on the best way to set up a table please.
I am trying to put together a downtime log, we use items referred to as “Events”
An “Event” is a stoppage on the production line
An Example of an Event is “A101” this would represent “Jam up on Line”
I have set the table up as follows to look like:
EventcodeID - AutoNumber
EventCode - -Text (A101)
Description - - Text (Jam on Line)
The Letter A the Beginning Referes to a machine.
A=Filler
B=Packer
C=Box Maker
etc
Attached to and “Event” is a “Failure Mode”
A failure mode is the reason the Event occurred in more detail
An Example of a Failure Mode would be “A101A” you notice that the Event Code is given a letter “A” at the end, the failure mode also has a description
Example:
A101A – Jam On Line – Carton Fell Over
To Break this all Down
A (Machine Type) 101 (EventCode) A (Failuremode) - – Jam On Line (Event Code Description)– Carton Fell Over( Failure Mode Description.
It is very much a Parent Child situation the Event Code being the Parent and the event code being the child.
A Parent can have many children, each time the letter at the end would be different, B,C,D,E etc..
But not all Event Codes have Failuremodes
Should this all go in one table? Or 2 or more tables and make a relationship? An example of this would link up would be really helpful
Thanks in advance
Andy
View 2 Replies
View Related
Nov 15, 2006
I need some advice on the best way to set up a table please.
I am trying to put together a downtime log, we use items referred to as “Events”
An “Event” is a stoppage on the production line
An Example of an Event is “A101” this would represent “Jam up on Line”
I have set the table up as follows to look like:
EventcodeID - AutoNumber
EventCode - -Text (A101)
Description - - Text (Jam on Line)
The Letter A the Beginning Referes to a machine.
A=Filler
B=Packer
C=Box Maker
etc
Attached to and “Event” is a “Failure Mode”
A failure mode is the reason the Event occurred in more detail
An Example of a Failure Mode would be “A101A” you notice that the Event Code is given a letter “A” at the end, the failure mode also has a description
Example:
A101A – Jam On Line – Carton Fell Over
To Break this all Down
A (Machine Type) 101 (EventCode) A (Failuremode) - – Jam On Line (Event Code Description)– Carton Fell Over( Failure Mode Description.
It is very much a Parent Child situation the Event Code being the Parent and the event code being the child.
A Parent can have many children, each time the letter at the end would be different, B,C,D,E etc..
But not all Event Codes have Failuremodes
Should this all go in one table? Or 2 or more tables and make a relationship? An example of this would link up would be really helpful
Thanks in advance
Andy
View 2 Replies
View Related
Nov 3, 2007
Good evening all!
Part of the function of my Db is to produce quotations. I specifically need one table containing line items and will use another table (update query) which will contain the 'quoted for' items. Transfer will be based on numbers of users required. I have the basic idea in mind; i will have a form with drop down to select numbers of users and then some Vb to take records from one table to the other. I can do that fine. The bit I am struggling to structure in my mind is as follows:
In a quote there will be typically three line items
1) Software
2) Installation / Configuration
3) User Training
I can easily do as described above but that relies on the main table being pre-populated with all three line items. however, I'd prefer to have the ability to have prices for daily rates, relating to installation and training, in another table so as to be able to change/update them and not have them fixed in a table.
So I think I would want to have the three line items update query across buy somehow control the list price of the services element controlled elsewhere.
Any advice? Many thanks.
View 1 Replies
View Related
Nov 3, 2007
Good evening all!
Part of the function of my Db is to produce quotations. I specifically need one table containing line items and will use another table (update query) which will contain the 'quoted for' items. Transfer will be based on numbers of users required. I have the basic idea in mind; i will have a form with drop down to select numbers of users and then some Vb to take records from one table to the other. I can do that fine. The bit I am struggling to structure in my mind is as follows:
In a quote there will be typically three line items
1) Software
2) Installation / Configuration
3) User Training
I can easily do as described above but that relies on the main table being pre-populated with all three line items. however, I'd prefer to have the ability to have prices for daily rates, relating to installation and training, in another table so as to be able to change/update them and not have them fixed in a table.
So I think I would want to have the three line items update query across buy somehow control the list price of the services element controlled elsewhere.
Any advice? Many thanks.
View 4 Replies
View Related
Apr 5, 2006
Hi there,
I'm still very new to Access....and learning it's strengths and weaknesses. A thought occurred to me today. Currently you have to build tables and set up relationships between them in order for 'searching' or 'querying' to work effectively.
Surely...it would make sense to develop a 3D database application..ie x,y,z axis in a table. That could eliminate the need for relationships between tables, as all of your data could potentially sit in one table (or at least reduce the number of tables required in a large database).
Does anyone know if this has been considered (I'm sure I'm not the first) or if there have been serious attempts at developing the concept into an application?
Cheers
Rob
View 14 Replies
View Related
Oct 27, 2006
Ok, Here is what I want to do....
I have 3 comboboxes and one button in a form. When I click on the button, it generates a querry based on the criterias selected in all the 3 comboboxes.
the generated querry gives an output something like this..
Indicateur | value1 | value2 | value3 |........... | value n
Now I want to display this selected criteria in a tabular format !!! I can use a subform, which I have used in many other forms as well, but my problem is that the querry generates different number of records everytime (as I have shown above, 'n' can have any value.) So I have to create textboxes dynamically in the form and do all sort of complex coading. I want to escape that and do something not too complex.
So I want Ideas on how do I do that? Maybe use pivot table? but I don't know how to use it and link to a table in my database??
Please, any help will be really appreciated!
View 8 Replies
View Related
Jan 2, 2008
I have been asked to create an RDB for my homeowners association that has 420 lots. That number, and the addresses, will never change. The purpose of the DB is to contain contact data between the homeowners to the association. I thought I would use 3 tables to do this:
tLots (lotID, name, address,etc.) PK=lotID
Relationship: 1 lotID to many caseIDs
tCase (caseID, lotID, type, date, status, detailID) PK=caseID
Relationship: 1 caseID to many detailIDs
tDetails (detailID, caseID, date, details, etc.) PK=detailID
I would also create pass-thru queries for these 3 tables on which to base my input form and my reports.
I have done a 2-level input form (i.e. the top half shows data from tLot and the bottom half shows a table with data from tCase, but I draw a blank how to show data from tDetails in the form. Can somebody point me in the right direction? Like maybe there is a sample of this already built somewhere that I could modify? I'm using Access 2000.
View 9 Replies
View Related
Nov 23, 2007
Hi everyone,
I just want to have information about the concept of using an Access application with different users at the same time.
Thank you for help,
Nizar
View 2 Replies
View Related
Aug 27, 2012
Is there a way mimic the vlookup function of Excel in Access using SQL? What I am trying to do is create a table with planning values and based on the field title to grab a particular value in the two-column table.
View 1 Replies
View Related
May 12, 2005
Im in the process of building a database for a friends business, and im a bit of a newbie with access.
Id like to get some opinions on structure and overall how i should build the Database.
My goal is to have two types of clients ... donors and buyers.
A client can be both a donor, a buyer or both.
When a client is a donor, they get a certain amount of credits added to their account.
When a client is a buyer, they will be purchasing those credits from a donor.
heres an example of what i want to accomplish;
John smith donates 500 credits; I enter John Smiths info and credits into his profile;
Jim Doe buys 100 of John Smiths credits; I want the DB to automatically update Mr. Smiths Credits, and then add 100 credits to Jim Does profile.
Also, I want John Smith to be able to purchase credits from Jane Johnson, and again, have the credits added to John Smith and deducted from Jane Johnson automatically.
Get my meaning here?
The tables will also contain the typical client info ...ie; Name, Address, Phone, SSN etc...
Can i/Should i do a seperate table just for credits and link it to the client tables? Should i create seperate tables for Buyers and Donors?
Also, I have an excel spreadsheet with formulas to do credits already, but when i tried to import it into a table in access, it didnt work so well.
Any opinions on table structure, design etc would be greatly appreciated
Thanks all for lookin in
View 3 Replies
View Related
Jun 4, 2005
Hello to all,
i have a non-windows application and i would like to create a vb program to print invoices.
I would like to send to this program a txt file with all the values (qty, vat, customer name etc with vertical & horizontal positions in the form etc..) and then superpose all i need to print with an image (gif or jpg wich is the my customer invoice presentation.
In fact i have 2 layers , one with all the value i print and another with the invoice image background.
I'm a beginer with VB, so i need advices to create this program, maybe someone did this already.
Thx in advance
VINCENT
View 2 Replies
View Related
Sep 3, 2005
Hi all,
Im fairly new to access and im having trouble constructing a stock control system that can create sales orders and adjust stock levels accordingly, hold customer details linked to sales orders. Ive spent about 20 hours trying to do this and its just pickled my brain, ive searched everywhere but sometimes im uncertain what exactly it is im looking for. Can anyone give me some pointers?
I have 7 tables at the mo but its 4 of the tables i need for the sales order:
tblcustomerdetails
customerID,first name, last name (general customer details)
tblorder
orderID, customerID, delilvery address fields..., subtotal, total, delivery
tblorderdetails
orderID, productID, productname, description, listprice, quantity, discount, linetotal
tblproducts
productID, catagoryname, productname, description, costprice, listprice, profitmargin, suppliername, instock, reorderlevel, quantityperunit.
what im trying to do at the minute is contruct a subform for a form that would require entering the products into through a combo box selected by productname and then autofill the product description and listprice. Ive ended up deleting all my forms and queries because nothing seemed to work right. I will then add this sub form to a form containing all the customer information and the total price for the subform this then needs to be output to a report for printing, but i can figure that out later. Ive attached my database if anyone wants a look if you dont understand my jibberish.
thanks
View 6 Replies
View Related
Oct 29, 2005
Hi all
First post.
I was wondering if this could be done in Access. Let me explain
I work at a candies manufacturer in Puerto Rico. Right now we are not tracking any kind of inventory. Is it possible to efficiently track our kind of inventory ( raw materials, work in process and Finished Goods) in Access?? Maybe using a bar code system??
Is it possible??
Please advice
Thank you
View 2 Replies
View Related
Nov 2, 2005
Hi
I would like some advice or opinions from people who have worked with access and mysql.
Currently we run a large database in access which holds around 3500 records. It is actually running quite slow at the moment. What would you suggest to speed it up? ive heard running it on a sql server but i dont have the info to know if this would be correct.
Also i was thinking or changing the access database and getting it fully redone in mysql why would this be more advantageous?
Also i havent any knowledge on MySql is it easy to learn for a beginner? Do you have any information such as websites i could visit to learn or sample databases? Or would it not be worth me learning it? What would you see at the front end and back end?
Sorry to bombard you with questions
I value your opinions
Thanks
View 4 Replies
View Related
Sep 14, 2006
Hi,
My client wants me to make fields from different tables on the same form which he wants to use for input. This has made it very difficult for me as my queries have to involve a lot of outer joins and in some cases full joins.
Any suggestion please?
View 14 Replies
View Related
Jul 5, 2007
Hi all,
I'm trying to set up a database, which I've done before on different programs, but I'm new to Access. I have a rather elaborate plan but am not sure it's actually possible.
I would like to set up a system that will effectively take input from the user within a record on the database. In simplest terms I'd like to set up a form on which the selection of a value for one field for a record affects the list of options available for a second field. As a basic example, say there are two fields: Input with possible values Red and Blue; Options, with possible values Red1, Red2, Blue1, Blue2. Ideally I would like to set up a form on which if Red is selected in Input, the options Blue1 and Blue2 don't appear in the Options box. Crucially you can also then select Red1 or Red2 as the value for 'Options' for that record (as opposed to just having a text box with the options written in it), as this provides the potential for a string, with the selection of a value for Options affecting another field.
Obviously in reality there will be many potential values for Options, and it won’t be obvious to the user which are compatible with each value for Input.
I wanted to use Program Flow functions with a combo box - say for the Record Source: IIf ( [Input]="red" , "red1;red2" , "blue1;blue2" ), though this would probably need to become a Switch/Case/Break command in the real database - but I don't think you can input equations into the Record Source.
I've also thought about trying to use queries, but can't see how it would work either, (the form for every record is the same, so the combo/list box for Options will always have the same properties. Switching between forms based on the value of Input seems impossible).
Then again perhaps I'm trying to make a database do something it wasn't really designed for, and should go back to basics and just display the possible options in a text box that is dependant on Input (but this way I won't be able to use the value of 'Options' in a further process).
I'd really appreciate any suggestions, especially since I'm pretty clumsy with the system still (first day using it, oh joy) and so could well be missing an obvious solution.
Thanks for your time.
Kate
View 2 Replies
View Related
Mar 8, 2008
Hi, I'm still an amateur at using Access and have just recently been introduced to normalization.
I'm looking for some advice on how to proceed with a database I'm trying to create.
I need the database to store vehicle information (name, make, model, color, license plate), along with parking information (date, time, place, who issued the notice)
My biggest question so far, is finding an efficient way to list a vehicle with what would be an undetermined number of parking slips. and then of course being able to retrieve that information on one form.
I tried using a from for VehicleInfo with a subform for ParkingInfo but I'm not getting the relationships right, the parkinginfo form is not displaying all the information connected to the license plate when the main form shows the vehicle information..
if that makes sense, any help or advice on how to proceed (or begin) would be greatly appreciated.
Thanks
View 2 Replies
View Related
Apr 9, 2008
Sorry - duplicated post.
View 2 Replies
View Related
Apr 9, 2008
I have been developing a catering order system at work. A demo version has been in test and initial issues sorted. The users are very happy with the way it works and though far from perfect it does everything they asked for and then some.
Basically, each order for refreshments/food creates a record and order number. Orders feed through to a daily 'jobs' diary sorted by date/time which the catering staff work from.
However, what they are asking for now is to be able to link some records together for collation/charging purposes. Grouping using the customer ID and the order Date doesn't work as customers could have many orders across many dates , and some of the orders by the same customer won't need to be collated together. My initial thoughts are to add a unique code to each order that needs to be linked , has anyone any ideas on this , is there an easy way to generate a code (perhaps CustID, OrderID , Date, other?) which can be added to other records to 'link' them.
I would be grateful for any suggestions.(other than a complete redesign :eek:)
Thanks
RussG
View 3 Replies
View Related
Nov 5, 2004
Greetings,
I was hoping someone could offer some advice on how I would design the following project:
Student Table
- ID
- Name
- Unit (each student belongs to one specific unit)
- License type (each student could have multiple license types)
Unit Table
- Unit Name (string)
License Table
- License Type (string)
I have created a report that dynamically updates information according to what unit the student belongs to via a drop down box, i.e. while the report is open, select a unit from a drop down, press a button to apply the filter and the report automatically updates. I want to add the same kind of functionallity to the report based off of licenses as well. My original design had all license types in the Student Table as a yes/no option. I couldn't get the filter to work properly so I moved license types to its own table (which makes more sense anyways...) But, unlike the Units Table, any One student is allowed to have many licenses so this creates a bit of a problem. If anybody has some insight on this I would much appreciate it. If you're not following, please let me know and I will try to be more specific. Thanks.
j
View 4 Replies
View Related
Jul 25, 2006
Hi. I just recently started studying Access independently since my school never taught it to me and I'm trying to design a invoice type of database as a summer project. I'm stumped on queries because the office 2000 guide I have only briefly goes over it.Basically, what i'm trying to do is create an automated value like in excel so that the "Net" column i have will subtract with the "sales" column to automatically enter a value for the "profit" column. I can't find any place for me to enter anything like [profit] = [net] - [sale]. i tried to use the input mask but since my data is in currency, it won't allow me to do it. can anyone please tell me where to start or what i've been doing wrong? thanks.btw, i'm also trying to do the same thing with the y/n feature of access. i'm also trying to find a way so that if i type y/n for a column, it will copy the value from a different colum so say i put yes on "account R" then i want the "AR$" column to copy the value from the "sale" column automatically. if i can solve this problem the same way as the previous problem then please ignore this (i THINK this can all be solved with queries.)
View 10 Replies
View Related
Feb 3, 2005
Hi everybody !
I have an assigment and have to create a database, i'm just starting to learn how to use access properly.
there's a screen of a form I made, if anyone has some advice to make look better it would be welcomed. As you can see it is very basic.
I also would like to know if it possible to create a search bar, for example typing in "sales" and the list of all the candidates working in sales comes up (I know how to do this in a query, but how do you transpose it into a form).
thanks for your help
Ilan
View 2 Replies
View Related
May 16, 2006
is there any1 who can give me tips on an exam i have 2moz on databases. its a theory and i'm crap at them. thanx loads, luff me xx
View 2 Replies
View Related
