Help with design structure of database Results

I seem to have all the "off the wall" questions but here goes anyway.

I'm building (or trying to!) a database for our local 4-H club (at no charge -- they don't even know it yet). I'd like to present it to them later this winter to use for their 4-H shows. Generally, the class list will be the same for each show. However, if they decide to put on a "specialized" show, I want to give them the ability to click a menu item (Make New Class List), which will then save it as a different Class List. For example, let's say the default class list is called ClassList1. Now, they want to build ClassList2 which is quite different than the default. They'd click on the menu item, it would open a conversation box asking for table name, they'd fill it in, press enter, and the form would open (datasheet view) where they would then enter the classes, giving the class # (not autonumber), class description (typed in), sponsor (typed in) and fee type (selected via drop-down). Then when they would close the form after entering all the classes, the table would be saved and could then be accessed as usual in building that particular show.

I know there is the Make Table Query but unless I'm remembering incorrectly, that takes data from one table and builds another table with it. I want to build a new table altogether where none of the data is coming from the existing table(s) -- although the structure is identical.

I know this has to be possible, I just don't know how! I'll be digging out my Access 2000 Bible this weekend; in the meantime, any help will be appreciated! Also, if necessary, I can send what I have so far for the database if it will help for you to see the structure as it stands now. Oh, and I'll probably also need help with setting up the database so we can track points for each horse/rider combination over a series of shows (circuit).

Yes, there are show software programs/databases out there already but our 4-H can't afford to buy one. I have a soft spot for 4-H (was a 4-H leader for several years) and want to help out by donating this database. Perhaps it can serve them well until they CAN afford to buy a COTS show program.

If all else fails, the 4-H leaders and I may end up visiting the local community college to see if the Spring Semester Access class will do it as an end-of-term design project. I think I've just been out of it for too long and using it too inconsistently to be able to do it easily.

Thanks!

I haven't messed with Access in 10 years.
I now realize that a Database is the best way to go.
I have gone beyond the capabilities of Excel.

The task:
A patient tracking Database.
I need to track the LDL Cholesterol results for patients.

My Spreadsheet now has the following fields:

Name - MRN - Date of LDL - LDL Result - Contact Type - Contact Date - Notes - Retest Date

The MRN is a unique Medical Record Number

The Retest Date is either 6 months (182 Days) from LDL Date or 50 Days.
(If LDL Result < 100, Retest Date = Date of LDL + 182 Days, ELSE Retest Date = Date of Last LDL + 50 Days)

I invision Splitting the Spreadsheet into the following Tables:

1. Patients:
Field 1: Name
Field 2: MRN *

2. Results:
Field 1: Result_ID *
Field 2: MRN
Field 3: Result_Date
Field 4: LDL_Result

3. Contacts:
Field 1: Contact_ID *
Field 2: Contact_Type
Field 3: Contact_Date
Field 4: Notes

4. Contact_Types:
Field 1: Contact_Type_ID *
Field 2: Contact_Type (Phone, E-Mail, Message, Appointment, Letter) - For a list Box

* Denotes Primary Key

Can any of you Guru's assist me in setting this up, if in fact My structure is sound?
Or can you make suggestions as to the proper structure.

I would like to see a list of patients with all data on one line.
I would only need the most recent LDL Date and Value to show.
I would like the Contact_Type field to be a drop down box to select fro.m.
I would also like to be able to edit the Contact_Date, and notes fields

Greetings,

First I would like to say that I'm portuguese and apologize for my bad english.

So, I'm a begginer in Access and I'm making my first database, but I got stuck. I need to put in a form a way to do record searches based in some criteria. Given my database structure, I really don't know the best way to do that, but I was thinking in setting up a few comboboxs to filter some criteria and show the results in a few listboxs. Perhaps for each criteria 1 combobox and 1 listbox, and the same for other criteria.
Well, for better understanding, I uploaded to here my database. It's a poor database, but don't forget I'm a begginer. For what I need this is enough.

I would be very gratefull if someone could take a look and help me solve this, so I can move forward.

Quick explanation:
- the file is in edit mode
- when you open it should go to a navigation panel
- there we have:
- "New Contractor" that leads to registration form
- "Update Contractor" that leads to a form for making changes
- "Contractor Overview" that leads to a form for getting information
- "Search Criteria" that leads to a form where I want to make searches based in combobox and list the results in listboxs. The combo and listboxs are already designed and sorted, the problem is that I dont know how to put them working properly. I'm sure when you see them you will know what I'm looking for them to do.

It's important to mention that there are 4 tables that are related to eachother through a secondary key.

I wich I could explain better, but my lack of english doesn´t let me.

This is very important for me, because my job needs this database, so I'm counting on you to give me a hand.

Thank you very much!
Philman

Hi there all,

I'm completely new to the world of databases, but I was thinking that using Access might make this particular task of mine a bit easier. I'm a teacher, and have a huge number of resources, prepared lessons and various other bits and pieces linked to lessons or topics that I would like to start piecing together in one place, rather than having all the various files scattered across a huge number of different locations. I guessed that setting up relationships based on the topics or the level of work in Access would make this a lot easier, and the database itself could provide links to the original files in question, regardless of their location. As far as I can think, I would basically need to assign keywords to each file, and then I'd be able to either run searches or navigate a series of forms to sort all the files according to the topic in question.

Now...

That all sounds fine (in my head, at least), but I have absolutely no idea about how to start transcribing this into a database! I've tried watching some tutorial videos online, but they almost always seem concerned with keeping and managing customer data, which I don't think is relevant in this case (or more likely, it is, but I can't think about how to equate customers with topics in my subject!). Could anybody who is a bit more database-minded perhaps give me some help in getting an overall recommended structure for this project?

Any help will be greatly appreciated, and thank you for your time in reading this.

John

WARNING: LONG POST
I know there have been plenty of discussion over inventory control but I have a situation with some additional variables and I need some heads to bounce ideas off of.

I work for a custom cabinet manufacturer and I have a database that I developed that we’ve been in use since 2001. There are several different components to the database, but the last one to be completed (and the one I’m working on now) is job costing.

PROBLEMS/CONSIDERATIONS
Problem #1: Since we are a manufacturing company we use many different materials to make any one product. So far I have all items ordered (since 2001 anyway) in the database but to date I don’t have these items allocated to any particular project – hence the need for what I’m talking about here. One of the unique problems I’m having is the fact that some items are ordered for a particular job (I.E. you know what job they are for at the time of ordering) and some items are “stock” items, meaning that we always keep a certain number of units on hand.

Problem #2: Another unique problem (relating mainly to “stock” items) is some products we use are ordered from different vendors (depending on availability, price, ect.) These products are essentially identical and need to be grouped with other like products at the time of assigning materials to a particular job. On the surface this problem sounds like an easy fix just by grouping the same product name across all vendors. However, it’s not quite that easy because we have many different stock items and product ids are different from vendor to vendor (and I always tell users to use a vendors identification for the product ID and they can use the product description to clearly identify it for our users). Another problem is I have users that are not disciplined enough to keep track of how they named a particular product previous ordered from a different vendor. Therefore I’m convinced I need another method of identifying like products across vendors.

Problem #3: Lastly, I’m still trying to figure out if I should subtract current stock or calculate it anytime “on hand” numbers are needed. Any thoughts on the best method (keeping in mind the variables listed above) would be greatly appreciated.

MY SOLUTION IDEAS
Concerning Problem #1: I want to allocated materials to a job at the time a work ticket is written (this would be the most logical input location since any given work ticket deals with a fairly small portion of an overall job and a material summary at that point is typically already given by another program or would be easy to figure by the person writing the ticket). I would like to do a material input form that would list only materials that have been ordered for the job and then either call another form for stock materials or have a switch to show all stock materials. What I’m unsure of: will I run into problems selecting both from the same form (since they’ll most likely run from different queries) and how will I pull stock items from multiple vendors (i.e. I used 10 sheets of particle board: We have 5 sheets left from vendor #1 and 7 sheets left from vendor #2). – Any thoughts on better methods and/or ways to improve database structure?

Concerning Problem #2: My main thought on combining like products from different vendors without requiring that the product ID match exactly is to enable a “Universal Product Code” anytime a product is tagged as a “stock” item. In the product entry form this would be a combo-box so the user has to select from a list. – With the amount of data I have so far I think I could create a fairly comprehensive list and furthermore my hope would be that if a user had to go to the extra step of adding an item to the list – he/she might think a little harder about properly coding it. – Thoughts/suggestions?

Concerning Problem #3: Given the considerations previously listed, I would ultimately like the database to accurately track inventory so when I order a laminate (or anything else) for a job and we had some left over from a previous job I could see that at the time of ordering. This would also save us time while entering work orders because we could see at the time of release that we are short of materials instead of waiting for the ticket to get into production. Whether I use a subtraction method or calculation method, I still need to figure out how to allocated or indicate which work order the item was designated to. Any thoughts?

Attached is a PDF showing the relationships to my existing tables. Keep in mind that additional tables may be needed to implement the changes I’m talking about here. Also, I’d be glad to email my database to you if you’d like to take a look at it.

I apologize in advance for the naming conventions used in my database. This was my first database and some of my earlier work was done without the foresight of proper naming.

I currently work for City Government and am involved in a project where we are designing a Roofing Maintenance / Building Management database. We are using Microsoft Access as our database program of choice. We had originally shopped around and it was decided that we could create a more customized-to-our-needs system by building it in-house.

Truth be told? They reviewed several outside sources database programs. Most of them were built in Access or with VB and they were VERY generic, which doesn't fit the needs of the department doing this project. SO... knowing that I have like 10+ years MS Access experience, it was decided to do it in-house (yippee... bleh), ergo, I build it for them... saving them MUCHO DINEROS.

So... I have so far built several tables, setup the relationships, and now we have hit a snag. Apparently, we aren't covering as much as we could. Apparently, there is more that the database needs to track. Our outside party keeps adding things we need to track, AFTER the fact. So, rather than rebuild this database every other month... I am writing in the hopes that SOMEONE has started / completed / whatever a project similar to this.

I would like to work WITH someone else in designing this beast. Someone who has a grasp of Roofing Inspections and Maintenance and maybe even someone who has experience with Capital Tracking or Building Management. Whatever we work on together would be available for all, but I would ESPECIALLY like to work with other City Government Employees if at all possible... no prejudice to whatever city, all cities welcome.

Currently I am just an Office Administrator with experience instead of college, but I have done consulting work with databases (MS Access oriented) in the past and have built several large-scale projects / database-applications for previous employers, so don't think you will do all the work. I am MORE than willing to setup a spot on a personally owned website where we can collaborate on this project, BUT I am under pressure to turn something out quickly... So, answer up fast.

Those who are interested, make sure to reply to my email: randomblink at yahoo dot com

Use the subject: Roofing Database

Any other subject get's put in the SPAM CAN... chuckle

Answer up fast... This will be rather open-sourced or whatever, so no worries about the license. I just want to get this damned database built as fast as possible. Like I have said, I have a massive table structure setup already and I have started on a form design as well... I have tons of notes and thoughts on paper that I will willingly transfer to my Project Collaboration Site for those who would like to help out.

Email if you are interested... Thanks

Hi everybody,

I am designing PC inventory database. Butbefore i do, i wanted some suggestions for my reports.
I want to be able to run the following types of reports from my database.

1- I will have a lookup table that will allow me to enter computer's condition (for example New - Good working order - needs replacement - broken)
Then i want to be able to see HOW MANY (i want to see numbers) computers i have that are really old.

2- Another report will be similar that shows how many PCs i have that have Windows 98 - 2000 - XP and Vista (again i am after numbers like 10 PCs with windows 98, 20 with windows xp) - i will have a lookup field where i can select teh version of windows

3- Similar report again that gives me the report showing office versions (example: 10 PCs with Office XP - 20 with Office 2003)

4- How many PCs are in repair? When were they sent?

5- How many PCs have anti-virus software installed. How many of those are up to date with latest virus definitions?

WHAT I NEED YOU GUYS TO HELP ME WITH IS STRUCTURE.
WHAT IS THE BEST STRUCTURE I CAN USE FOR MY DB?

I thought i will have the following tables:
PC info tble:
(which includes PC No - Windows Version - Processor - Memory - Office Version - Antivirus installed? yes/no - antivirus up to date? yes/no

Repair table:
Will have which PC No that's been sent away for repair - When it was sent - how much it will cost - repaired? yes/no

Thanks!

I am after someone who can help me with a project, don't think it will be too difficult for someone with a bit of savvy, but way to complex for me...

here is what i need at this stage..

I need help to correctly design and link up my tables. I entered the info into them as best as i know how.

Rules.

We have 2 divisions
12 teams in division 2
14 teams in division 2
Teams sometimes share pitches
Some dates are reserved for certain game types (example county cup games take priority)

Can anybody please take a look at my structure and advise me where to go.

Outcome of finished application is to be:

generation of fixtures for a whole season for the 2 divisions and the cup games. games are sometimes cancelled so will need to be re generated at the next available date.

Teams play each other twice a season in the league

Teams should not play at home twice in the lague on consecutive weeks.

The biggest problem is the shared pitch issue...obviously ony 1 team can play on a specific pitch each week.

Cup games take priority on dates chosen for cups.. if team is out of the cup league fixture should be generaed

Extra dates will be fed into the generator later in the season to cater for midweek games.

Think thats about it...

Can anybody please help me.

I am designing an application that tracks information on Choir membership and sheet music that we have on file. I am starting with a database of church members. There are four different choirs and choirs share some members. Some members of some of the choirs are also not members of our church so I will have to place non members in the member table. I am new to database design and would like the collective wisdom of this list to tell me of any problems I may encounter before I start doing any detail work.

What is the best way to deal with someone who is a member of more than one choir and may belong to a different section in this other choir (Tenor in one and Bass in another). An individual may also hold different offices in various choirs.

The table structure I have is as follows

CHURCH MEMBERSHIP DB:
MemberId Autonumber (pk)
FirstName, Text
MiddleName, Text
LastName, Text
DateJoined, Date
Phone, Text
Address, Text
City , Text
Zip, Text
EmailAddress
BirthDate, Date
Member, Boolean

CHOIR MEMBERSHIP DB (How do I efficiently track someone in > 1 choirs)
MemberId, FK
ChoirId, FK
FolderNo
RobeNo
Section
ChoirOfficeId, Fk

CHOIRS DB (This lists the various choirs in the Church)
ChoirId, pk
ChoirName, Text
DirectorId, FK (Pointing to Member DB, Person may not be member of any Choir)

MUSIC DB
CatalogId, PK
Title
Composer
Arranger
Type (Single Copy/octavo or book/collection)
PublisherId, FK
PublisherNumber
VoicingId, FK (From table with possible voicing)
NumCopies
UsageId, FK (Where in the service is it appropriate
Location, Text (Where in the filing system, or off site)
ClassificationId, FK (List of classification/genre in table so can update)

PUBLISHER DB
PublisherId, PK
PublisherName
PublisherAddress
PublisherPhone
PublisherWeb
PublisherContact

MUSIC CLASSIFICATION DB
ClassificationId, PK
Classification, Text (Christmas, Easter, general anthem etc)

To be able to track performances and plan services and performances I have the following table.

PERFORMANCE DB (This is to keep track of and plan the regular service)
PerformanceId, PK
Pdate, date (Date of Past/Planned performance. Possibly more than one per day)
ServiceTypeId, FK (From table of types of performances – morning service, evening, etc)
Location
Speaker
Pianist
Organist
Introit
Invocation
Anthem
Meditation
Benediction
(etc)

I would also like to be able to prepare mailing labels for the various choirs as well as the general membership from this DB. My primary focus will be on the music. I would like to have an efficient music DB that I may find out what music I do have and when I last performed them, what options for performance (usage and classification)

Would be grateful for your comments, Thanks!!

Rmiller

Hope the thread title wasn't too confusing.

I have a database that tracks emissions from painting. Bear with me since this is going to be a long post.


Some background info.
- a paint can consists of many parts mixed in a specific ratio.
- a part cosists of many chemicals
- a part may be used is many different paints

Here is how I have the existing database structured now. I’ve simplified it somewhat.

tblPaint
PaintID (PK)
PaintName - String
PaintDensity - Double
PaintVOCContent - Double

tblPart
PartID (PK)
PartName - String
PartDensity - Double
PartVOCContent - Double

tblRatio
RatioID (PK)
PaintID (FK)
PartID (FK)
Ratio - Integer

tblChemicalWt
ChemicalWtID (PK)
PartID (FK)
ChemicalID (FK)
WeightPercent - Double (Percent)

tblChemical
ChemicalID (PK)
strChemicalNumber - Long
strChemicalName - String

tblUsage
UsageID (PK)
PaintID (FK)
UsageDate - Date
UsageAmount - Double

PK = Primary Key (Autonumber)
FK = Foreign Key (Autonumber)

The Density or VOC Content (VOC = Volatile Organic Compound) for a paint can either be given OR it can be calculated by the mix ratio of parts and their respective Density or VOC Content values. One or the other must be complete.

What I did not account for was that there may be changes due to the paint manufacturer revising their paint composition, such as;the parts that make up a paint may change chemical make-up of a part changes (can be a change in Weight Percentages or the addition or deletion of a chemical). ratio in which parts are mixed for a paint changes Density/VOC Content values may change for a Paint or Part
The problem is that I cannot simply change the existing records as the emissions are calculated using all the data from each table and emissions need to be calculated using the paint/part/ratio/chemical weight percent info that was valid at the time of usage.

Another thing is that the Paint Name will not change, it’ll always be something like “BrandX Acrylic Blue”.

The person entering usage data only knows how much of what paint was used for a given day.

The person who enters paint usage has nothing to with entering the chemical make-up for parts and information for the paints and vice versa.

At any rate, my new draft table design is as follows. Two of the tables (tblChemical & tblUsage) will remain the same.

tblPaint
PaintID (PK)
PaintName - String

tblPaintVersion
PaintVersionID (PK)
PaintID (FK)
PaintDensity - Double
PaintVOCContent - Double
PaintVersionDateIN - Date
PaintVersionDateOUT - Date

tblPart
PartID (PK)
PartName - String

tblPartVersion
PartVersionID (PK)
PartID (FK)
PartDensity - Double
PartVOCContent - Double
PartVersionDateIN - Date
PartVersionDateOUT - Date

tblChemicalWt
ChemicalWtID (PK)
PartVersionID (FK)
ChemicalID (FK)
WeightPercent - Double (Percent)

I might be able to do away with tblRatioVersion and just have one table to store the mix ratios. It should be the case that a change in mix ratios (either a change in mix ratios and/or what parts make up a paint) means a change in the Paint Density & VOC Content. But I am presenting both versions of the Ratio tables here for completeness.

Version 1
tblRatioVersion
RatioVersionID (PK)
PaintVersionID (FK)
RatioVersionDateIN - Date
RatioVersionDateOUT - Date

tblRatio
RatioID (PK)
RatioVersionID (FK)
PartVersionID (FK)
Ratio - Integer

Version 2
tblRatio
RatioID (PK)
PaintVersionID (FK)
PartVersionID (FK)
RatioVersionDateIN - Date
RatioVersionDateOUT - Date
Ratio - Integer

I plan on having the DateOUT fields be populated automatically to match the DateIN for the new version. That way I can use “BETWEEN DateIN and DateOUT” to select the appropriate info for calculating emissions. The idea came from an old thread I started. I think this is the way to go, but with all the relationships going on, I'm having a hard time wrapping my head around it all. Am hoping someone here can help me with this.

Anyone see any problems with the new table design?
Anyone know a better way?


Some potential issues that I seeIf only the Density/VOC Content changes for a Paint, then the old set of records in tblRatio must be duplicated. If only the Density/VOC Content changes for a Part, then the old set of records in tblRatio & tblChemicalWt must be duplicated.
Thanks for reading this post all the way to the end!


EDIT: Thought about it some more.
A new version of a Part, should trigger a new version of Mix Ratios which in turn should trigger a new version of a paint.
Part --> Ratio --> Paint
Ratio --> Paint

Also, a change in a Part must trigger a New Paint version for ALL Paints that currently use it!

Thanks for your help in advance you have saved me an a number of occasions now.

I am about to built quite a sizable database and need help with the table structure. The database will contain lots of values of chemical tests from rock samples.

If I was to have each chemical that could be conceivably tested for it would be 200+ only 10% of the columns would be populated by a value. The chemicals do have classes however and a previous database has worked by having about 15 tables representing different chemical classes in which each column is a different chemical.

I think its better to have a lookup table of all the possible chemicals and then store the values in row form so that you have the same sample repeated in many rows where each row is a different chemical and then using crosstab queries to tabulate the data later on.

My only concern is data entry surely its going to be a pain to add data through the row method as opposed to the column method.

Is there an easy way to add data using forms in this method and is there any vba to convert data from columns into rows. i.e.

B C
1 q r
2 y n

into

b q
1 c r
2 b y
2 c n

Many Thanks for your help.

Oliver

Hi everyone and thanks in advance for any help!!!!

Recently I decided to help a friend set up a database for his small business but at the min I'm having some trouble with the actual table and relationship design with it...

the database is to store customer accounts, as well as details of the various "jobs" offered, in this case pc & laptop repairs, data recovery, website design and search engine optimisation. So far I have the table for storing customer details, as well as a table for storing details on each job (each job has its own table for storing details since details are different for each job).

now this is where I'm stuck - how do I link a customer account to a job

my tables are so far

tblCustomerAccounts
CustomerID
custFirstName
custSurname
custAddressLine1
custAddressLine2
custCityTown
custContactNumber
custMobileNumber
custEmail


tblDataRecoveryJobs
JobID
JobType
Date
Problem
FormatPCPermission
BackupData?
DataToBeBackedup
AdditionalRequests
AdditionalHardwareSoftware
EstimatedJobTimeDays
TotalCost
Quote?
Completed?

tblSEOJobs
JobID
JobType
Date
HoursPerMonth
KeywordIdeas
BusinessDescription
IdealVisitor
CompetitorWebsites
TotalCost
Quote?
Completed?

tblCustomerInvoice
InvoiceID
CustomerID
JobID

But surely having JobID in each table would cause problems with duplicates? And giving each table a unique primary key e.g tblRepairs has RepairJobID, and the invoice table like this:
tblCustomerInvoice
InvoiceID
CustomerID
RepairJobID
DataRecoveryID
WebsiteDevelopmentID

would lead to redundant data as only one job type would be filled?

Any help is greatly appreciated!!!

hi everyone...i'm a newbie and this is my first database design. i'm trying to create a database that holds information for different sporting entities. each entity has the basic information such as name, address etc...a sporting entity has members (according to its type) and controls various sports clubs...for example an Athletic Association (sport entity) can control athletic club A, athletic club B, etc.

now the challenge that i'm facing is that each sport entity can only be of a certain type (NSA, SRE or SP) and this type determines various characteristics. So if i put a type column in the sport entity table, i would have to put all the attributes that are related to each type and this of course will generate a lot of nulls depending on the type selected.

based on the attached pic:

1) are my tables made up correctly?
2) if so, how do i implement the relationships?
3) if not, how should i re-structure my design?

thanks

Hi all,

New to both Access and this Forum, but after putting it off for so long, have now decided to try to get my head around Databases. My first project is to build a Database that enables me to search a customers past orders with my company. What I need to know firstly, is how best to structure the database. These are what I have so far (trying to migrate from Excel):

Table 1:
Customer ID (PK)
Customer Name
Customer Address 1
Customer Address 2
Post Code
Phone Number.

Table 2:
Product Code (PK)
Product Description
Pack Size
Case Size

So, table one is essentially a customer list and table 2 is essentially a product list. Keeping the 2 entirely seperate makes complete sense, but how should I go about recording each customer's purchase history. Obv Each customer will buy many products - some of which will be on several customer's histories.

Do I create a seperate table for each customer with their history on? That seems to make sense, but equally, it seems to go against all the fundamentals of database design.

I am doing this with a view to incorporating several other elements such as Customer complaints, enquiries, etc later down the line. For now though, my goal is to be able to type the Customer Code and a Keyword into a search query and have all the items a customer has bought containing that keyword returned in the results.

I would appreciate any help on this - and would also appreciate any pointers to good tutorials. I am self taoght in Excel and reasonably competant, so am confident I can pick up Access once I get my head around the principles and how it differs to Excel.

Many thanks in advance!

I'm in the process of normalizing my data into 2 maybe 3 excel tables (and then importing them into Access 2010 and creating the relationships/links there), and I had thought that the information I needed for my small startup was simple, but the more I'm looking around, I'm realizing that I may be in a bit over my head and can use some input before I make a mess.

Basically, I'm creating a very basic wine varietal database with a very basic profile of the wine, and the food that pairs with it will mostly be food that can be stored easily for tastings (cold and room temp food). Where I'm having trouble is that I'm "rating" how the wines (not every wine is a "match" by using a scale of +2(Perfect), +1(Positive), 0(Neutral), -1(unpleasant), -2(avoid), there will be fields that are empty with no "match" or relationship between the food and wine, while some matches will have many. I have several reference books and my own notes that I will be using to enter the data.

Currently, I have my "wine" data in one "table". Fields are:
Wine_ID, Wine_Name, Wine_TastePro(file), Wine_Color, Wine_GrapC, Origin, Noble (Y/N), Tasting Notes.
For the food, I have:
Food_ID, Food_Catagory, Food_Name, Food_Prep(aration), Food_TastP(rofile), Food_Temp, Food_Notes.

What would be the best was to go about linking the data? Should I create a separate table with my "ratings" and then enter each possible "pairing" in it's own field using the id's of both. Ideally, I would like to be able to generate a "Wine" report and bring up the different "food" matches, from best to worst, sometimes maybe only the best match, and vice versa, I'd like to bring up a food item and find the best wine pairings.

My brain is having trouble making the next step, I've worked with databases in the past, pretty deeply in the design but not development stage, but it's been 20 years. I think that having this information at my fingers instead of spending the next 3 years at Sommelier school would help me make a go of my little catering business. I appreciate any assistance you all can give me.

Hello to all of you

I'm reading lots of your post to try to find a hint of how I could structure my database. But can't quite find something that will allow me to start in a structured way. Let me first explain what is the goal of the database and what it will be doing :

The database is to create an estimate on project that we could do at my work. Let me show you what it will do as a finished result and the global architecture of the db

1- Estimation #1 Name X
a) Phase 1 to X of the project
i)Field ( Mecanic, civil, piping,....etc]
1.Subfield ( each field has it's own subfield ex: civil-> demolition, excavation...)
a. Items : I want to put a form that will vary accordingly with the field
ex: for piping, user chooses what he want to add (Pipe/fitting/welding/valve/..) then he adds the info that vary accordingly to to his choice (ex: for pipe=> he enters spec define from company chosen by user, diameter, length)

Up to know I've been gathering the information in all the sources around me for the Piping ( consider to be the harder because it uses lots of tables, I assume that when I'm able to do the piping I'll be able to modify it to the other fields)

About the informations I have to enter, I'm confused in how I should enter it because everything is related in one way or an other. For instance lets take the table of pipe specs.
to start I have to tell you that every company has its own specs so I have to list every detail of the spec need for calculation after. Here is how it's in a excel file :
________________| spec 1|spec2|.....__________________________
small pipe material_|(linked to a list in case any change occure in spec)
pype type________|(linked to a list in case any change occure in spec)
minimum schedule__| Fixed for a spec but linked to a table
Connection type___|(linked to a list in case any change occure in spec)
maximum diameter__|Fixed for a spec should I link it to a table?
Flange type_______|(linked to a list in case any change occure in spec)
Flange class______| Fixed for a spec should I link it to a table?

the field schedule could have numerical values or not. When not numerical it should go on a table and convert it to numerical accordingly to the diameter entered.

the table is repeted for medium, large and xlarge pipes determined by the maximum diameter field.

When a estimator enters a line, the form looks at the data entered and calculates with different tables the cost and man/hour cost. But accordingly to his choise of company, a correction factor must be inserted and the labor cost is different for each company.
The estimator can also deactivate a phase if he chooses not to do it.
The field will by predetermined by the choises the estimator does but could be changed if it needs customization.

This is for a 1 user use (one at a time)but I think it would be better to separate the tables from the forms, query .... because updates could be added in the server and each user could go and download the data without having to download all the program again.

I consider myself as a beginner in db and it's my first from the ground up. I'll receive a access 2003 bible soon to help me with the work and the internet is my new friend. I have 4 months to do it, at 40+ hours a week. Do you think I'll be able to finish?

I guess my main question is how could I design my database so it's easily upgradable to add more fields if I don't have time to finish...

thank you very much for your time and hoping you'll be able to guide me in creating a well designed database

Bruno

Hello all AWF members and guests.

This thread I have created is in regards to a Microsoft Access Database Project which I have been given in which I am to plan/design and implement these designs to create my own final database. The database will undertake a structure of a Relation Database. The database itself will be based on the information used and dealt with within a Video Store.

For the Database Project for the Video Store I am to create Elementary Sentences in which I am then to form Conceptual Schema Diagrams in which can then be used to form the database and to structure it. The Elementary Sentences are formed by finding what information is needed to be stored and then forming sentences that can be made into these CSD’s.

The following contains the information of which I have already thought of which a general Video Store may need to contain, but this was only what I could think of and I was wondering if anyone would be able to add to my list and if they had knowledge of Elementary sentences be able to form them for me to add to my list:

In terms of the Video/Tape:
Video ID Number
Tape ID
Title of the Video
Shelf placing
Censorship Rating
Hire Cost
Amount of days a video can be hired

In terms of the member and Members of the video store:
Member ID
Family name
Given Name
Phone Contact
Street Address
Town
Postcode

In terms of the process of Hiring and the Fees involved:
Time of hire
Return Date
Returning a Late video
Overdue Video

These are just the first things that I could think of involving a general video store, if you are unsure of the above and need me to explain anything further please just post below. I will appreciate any type of advice or help given greatly.

Thanks .

Hi.

I am trying to design a database to replace a Purebasic program I wrote over 1 1/2 years ago which held my magazine collection.

My original program was complicated and hard to update, as I needed to make many changes to test a theoretically small change. Now I have access via a home use license at work, I am looking to create something in this which is more flexible and easier to update/maintain.

Something to point out is that when it comes to the magazines. I need them effectively stored or listed by: Magazine, then country, then issue/publication date. Everything after that is specific to that issue.

I have sat down with Word, and created a break down of what I need in the way of fields/entries to hold the information I want about any given magazine. I have also split this into sections where the parts are related to each other on a specific level. So stuff that relates to all copies of a given magazine issue (cover price, pages etc), and stuff the relates to the specific issue I have (condition, where/when obtained etc).


But I am a little confused with the concept of normalization, which I understand to be breaking the database into common parts to save duplication of data.

I have attempted to recreate what I think is the right structure in tables and used relationships to link them together, but I would be very grateful if someone can someone look at the images attached and tell me if I have broken this down right and if this would work as a structure for a database before I go any further and find out I am going to hit major problems have to start again.

I do not need to know how to create the database structure itself, ie. what to put in the tables as I would rather work that out myself and ask for help if I hit a problem. I just really need to know whether I have got the initial design right.

Thank you
Max

I was wondering if anybody would be kind enough/have the time to help me with a database I'm currently designing.

The details, including an attachment can be found here:

http://www.access-programmers.co.uk/...d.php?t=209826

I am trying to achieve this:

Each supplier (DB_Lief) has many unique products which each have a barcode (LiefArtNr) and a Reference Number (RefNr).

Either of these codes can change at any time though, hence an internal ID (DB_Artikel).

I want to be able to record all the details for an article, and then only store changes when there are some. I also want to be able to see, when looking at a product, the previous values of each field and when it was changed.

My current approach to this has been to create a table per 'field', each with same structure allowing the identification of the previous/next value etc. I have however been told that this is the 'completely wrong track', and wish to correct it.

I figure it's worthwhile to move the discussion to this thread to correctly categorise the discussion.

I am planning to analysis parliamentary questions. For this reason, I designed a database. My wanting from this database to know the frequencies of questions asks by a MP with question's subject, nature and focus according to questions types as well as effectiveness and relevancy of answers provided by ministry and or Division with the questions among the four types of questions(Starred, Unstarred, Short Notice, Prime Minister's). If questions & answers are shifted/transferred, I want to know the reasons. Certainly, MPs asks questions and relevant minister answer on it. Therefore above information needs to be displayed with the questioner name(MPs name and Constituency name) and relevant ministry and or division name along with question no, Parliament's session no, and question's date.


When question types are 'Starred' or 'Prime Minister's' it may have supplementary questions and answers. Supplementary may ask MPs in addition to the original questioner. Conventionally, first supplementary question asked by original questioner. Subsequently, minister gives reply. Then other MPs may ask additional supplementary until Speaker moves on next question. Therefore, in supplementary questions, I want to know the supplementary questioner's name & his/her constituency name, supplementary no, question's focus, subject, nature, and relevancy with the original question. Similarly, every supplementary question must answered by minister. So effectiveness and relevancy of supplementary answers also need to know. Information of supplementary questions and answers should be displayed on under the respective original question number.

In my database, main table are tblMain, tblOrgQuestion, tblOrgQuesAnswer, tblSupplAnswer, and tblSupplQuestion. All other tables will be linked these tables via combo boxes on respective fields. By the way, MPs details (education, age, term of office, etc) also need to know for analysis of questions asking criteria.

My database and 5 main tables' relationships are shown in attached file. Please check it.

I am not sure whether my table structures and relationships are correct or not. Please advise me how to correct this or modified my version so that I can get expected output from this database. Therefore I requested you please help me to design my database properly. Need any other clarifications please ask?

Thanks in advance.

ozaman


Not finding an answer? Try a Google search.