Update Relational Databse
Mar 7, 2008
Hello everyone! I am completely new to MS ACCESS, so this problem might be fairly simple but yet it is eating my head.So if I get some directions it will be greatly helpful and appreciated.
Description:
The bakery buys ingredients from number of suppliers, makes a several kinds of cakes and sells to few shops. There are about seven ingredients in the first week with the possibility to add new in ingredients later on. There’s a catch though; on any day, only a single type of cake is made, in a single batch and all of them must be delivered before the next day’s cake are finished. It is necessary to be able to rack the ingredients used in a batch back to the suppliers.
I have made tables for suppliers, ingredients, orders and order line (with fields ordereId, ingredientId, quantity, unit price).
Forms that allowing users to allow new ingredients types, suppliers, and new order for ingredient are required which I have made. These forms are to be used to add the data for which ingredients are needed in the second week of baking.
So these are my questions on which I am stuck in
1. Do I need any more Forms or do I need to edit my form?
2. Is it okey to put table as the data source or would it be better to place a query?
3. How do I keep records of my earnings and expenditure, expenditure and keep them updated with my databases?
I hope you will understand that I am completely new to these topics and its really hard for me to grasp them all at once. Merely some guiding points will be of great help. Thank you.
6pandn21
View Replies
ADVERTISEMENT
Mar 2, 2007
I have an inventory database i am working on but am too new to access to know all the inner workings. Quick summary of what I need to accomplish:
I have a databse with several tables each representing different types of parts that we sell. Each table is similar in that the primary key for each is the part#, beyond that though the different types of parts have different specs that go with them. (example: for enclosures, the dimensions are needed but the amperage is not, for circuit breakers the opposite would be true.)
Next I have an inventory table, each record has a unique Control number that we assign as the primary key, then just a part #, notes about its condition (some parts are used) , a shelf location, and a price and date.
I have a very small sample database that i can email to anyone interested in looking at it.
Basically what i think i need to come up with is form that has several aspects to it. A user enters a "CATALOG NUMBER" into a combo box at the top of the form. tabbing out of that field then shows the specs of that catalog number below. the problem is the part numbers would come from a number of tables like the BUS PLUGS, MCCB'S, ENCLOSURES tables, all of which have different types of data. Finally, at the bottom of the form would be a result of a search through the INVENTORY table returning the multiple lines of line by line data where the CATALOG NUMBER matches the one specified above to show the records with the same catalog number but different notes, prices and different shelf locations. anyone care to take a look?
View 4 Replies
View Related
Jul 12, 2005
undefined
Hi, can anyone help? Like an idiot I veered away from my intention to put a password on an Access database which holds only a table and a few queries and explored other security options. I now cannot get in to the database at all and get a message telling me that I do not have the necessary acces srights and to consult my administrator. When I look at the properties, I am shown as the administrator, which makes sense but despite trying various, desperate things, I cannot get back in to take the security off. Needless to say I did not create a copy first, so have learned a lesson the hard way. Any advice gratefully received.
View 3 Replies
View Related
Mar 22, 2006
Hello Everyone,
I just finished building my database and i have to install it on a PC. my problem is how to remove access tool bars and menues so that the only thing that appears when you open the databse is the login screen?
I don't want the user of the database to be able to go to design view and change the design of the forms or the reports. I want the user to see and be able to interact with the login form and the switchboard.
I wanna also know how can i install the databse on the PC. do i just copy it and paste it or is there any way else?
I hope you understand what i want.
Thanks in advance for your much appreciated help.
View 6 Replies
View Related
Apr 11, 2008
Hello,
I need to import the data from one database into another everytime a form opens, I have some code which works:
Function GrabAccessTable()
DoCmd.TransferDatabase transfertype:=acImport, _
databasetype:="Microsoft Access", _
databasename:="\triggerwwwroot$AW@RDAW@RDOnline.mdb", _
objecttype:=acTable, Source:="Main", _
destination:="Table", structureonly:=False
End Function
The problem is that it won't surrently write it into an existing table, I want it to write into a table called Table (stupid choice of name I know....) but instead it creates a new table calle Table1.
Thanks
Richard
View 1 Replies
View Related
Jun 21, 2007
hi...
i have created a database link between oracle and msaccess....i am trying to access the msaccess table from sql(oracle)..
when i do a
select * from msacees_table@database_link
i am getting the desired result
but when i try to acces a specific column with in the msaccess table like
select msaccess_col1 from msacees_table@database_link
its says invalid column name....
any idea why its behaving like this ???
thanks...
View 1 Replies
View Related
Jan 17, 2006
I thought I new enought about access databases. I've used the Time & Billing database as a template for my sister to use, but her partner uses a MAC. How can I get the MAC to access the database? I believe the MAC can use Excel but how can I link these two, if at all.
View 4 Replies
View Related
Jun 22, 2007
well i have a scenario like this,
there is a standalone msaccess database(*.mdb)...
i want to create an audit table cantainig the respective detaill(created or update by,time stamp.n the data that is update or created)...
the challenge is there is no form ....the database is manully updated...i mean anybody can opne the databse n alter the existing table inside the databse....
its just a standalone database..
is it possible to achieve???
if yes...
how can this be achieved???????
if not is there any other approach that i can follw????
will really appreciate for ur feedbacks...
thanks in advance.......
View 8 Replies
View Related
Oct 6, 2004
hei hei
can you think for me 1 more second , PLEASE
because I made all my menus invisible in startup.
BUT when I open a form , for every form a taskbar icon is getting created.
whwen I click any of the taskbar button of my my project forms, the Database window is apearing.
I don't understand why is it coming , I don't want it to apear when users are working on the project.
but before it is not apeared but it is happening since yesterday only.
please help me in lettimg me the reason for the appearence of Database window.
thank you for your time.
Kiran.
View 1 Replies
View Related
Feb 18, 2008
Hi folks, I'm new here - pretty hot at Excel but a total newbie at Access, So..
OK, I've got my database up and running. It keeps records of about 500 staff - everything is working fine but I now want to do stuff beyond my capabilities.. I've got a master record (Master Staff List) and a main input form (Data entry/amandment form). One of the input controls (via a sub-form) is for recording the time staff have spent working at a higher grade. Now if this was a one-off occurrence I could handle it but any one person could have multiple occasions that need recorded and I don't want to 'overwrite' the previous record. I have tried researching the problem and I think I need a 'one-to-many' relational table and a way to add a new period from the subform when required. The subform would also need to show blank when I opened it.
Any help would be appreciated.
View 5 Replies
View Related
Jan 19, 2008
Hi guys, I'm fairly new to databases and am just creating sample ones at the moment and following tutorials from books. I tried to create my own cinema/movie database yesterday and there's something wrong.
I really wanted to work this one out for myself but after trying many different combinations of various relationships I just can't seem to get it working.
I have four tables..
A member table: MemberID
A booking table: MovieID, Screening Number, MemberID
A movie table: MovieID
A screening table: Screening Number
So the booking table is a composite key that links the member tbale (via the memberid), the movie table (via the movieID) and the screening table (via the screening number)
The problem occurs when I try to make a booking form for members to book a movie.
I use the form wizard and take the following fields:
The MemberID from the member table, the Screening Number and MovieID from the booking table. Then I add other relevent information from the Movie and screening table (for example, screening time, date, price etc from the screenig table and movie name, information from the the Movie table)
The MemberID is displayed on the main form with the other information in a sub form.
The problem I am having is when I enter a booking on the form, I get an error telling me the Microsoft Jet Engine cannot find the screening number in the screening table or something like that.
It lets me enter the other information but not with the screening number.
Thanks for any help.
Benn.
View 2 Replies
View Related
Apr 13, 2005
I created a Contact database where I have the date and time as two separate fields in one of the tables. I have them defined as such, but would prefer that the fields wait until I enter data into them and not auto fill with the current information. Is there a property of these fields I can set so that these fields do not get auto filled? Again this database is too large to send, so I hope that you can help without it.
View 1 Replies
View Related
Feb 16, 2008
Hi
Not sure if anyone can help. Its been about 10years since I've built a relational access database, but offered to build one for someone, but am now a little stuck.
Its a photo sales tracking db.
I have 3 main tables. Photos, Sales & Customers.
The main form is a photo form with embedded subform showing the list of the sales related to the photo. This works well!
The second form is a customer form with embedded subform showing the list of purchases (sales form) related to that customer. This again works, but I cant get it to show the photo title of that sale in that subform. I think I am missing some sort of link.
I have relationships between photos and sales, and customer and sales. But it always shows a blank photo title field in the customer purchases list
This is a very brief outline and I can explain in more detail if some thinks they can help. So far the database looks good and mostly works but it is all a bit relevant if I cant get the customer purchases to automatically show the name of the photo sold.
Any help most appreciated!
View 4 Replies
View Related
Mar 9, 2005
Im trying to figure out how to model 'Printer' and 'Cartridge' information. The relationship between them is Many to Many, but to add to my confusion, more than one cartridge can be used in a printer (Black + different colour cartridges). Adding a new entity to resolve the Many to Many does not solve this issue of how a printer can have, for example, a black cartridge and 3 different coloured cartridges.
Any ideas how to model this please?? I have been toiling with it for some time and I keep going round in circles :confused:
I'm sure it shouldn't be as difficult as it seems, so I'm hopeful someone might be able to set me straight :o
View 1 Replies
View Related
Feb 5, 2008
Hi there!
I'm currently working on a malformed Access database, not normalized at all.
It only consists of one table and is designed like this:
(table compartment)
#ID#Name#Member1#Member2#Member3#Member4#...#Membe r20#
So all the members are realized by single columns.
I created a new database, consisting of the main table, the "people" table and a relation table, connecting the people with the compartment (n:m relation).
It works great but I have to get the old data into the new tables. The old main table consists of more then 100 columns, and most of them are now hived off by using relations.
Now I'm looking for a smart solution to convert the table.
Do you have any idea? I tried to use a Query, but I couldn't merge two or more columns and put them in a single one on a new table.
Thank you in advance!
View 13 Replies
View Related
Mar 15, 2005
Hi all, i have a relational database. In a form, Transactions, i have a text box, CustomerID, which relates to the primary key of the customers table. At the moment, if i enter a number that has no related record in the customers table, it will alert me only when i try to close the form or move to another record. I am wondering if it is possible to check for a related record immediately after the text box loses the focus.
thanks
joe
View 2 Replies
View Related
Jan 22, 2008
The first time a person tries to use Microsoft Access, they are likely to miss the whole point of what a database is and how to use it. When they start having trouble making it work for them, they think in terms of what they may be doing wrong in their use of the obvious tools of Access. But often their problem is that they are unfamiliar with the principles that make databases so useful and they have started with a flawed database schema.
This tutorial is intended to explain the underlying principles of relational databases. It is, admittedly, rather theoretical and perhaps even boring. But without an understanding of these principles, a beginner cannot hope to use Access for any really useful purpose. If you are a beginner, I urge you to read through the entire tutorial.
There are several issues that arise time and time again, such as:
. Storing multiple values in a field.
. Storing calculated values in a table.
. Determining primary keys.
. Using Autonumbers.
. How many tables does this database need?
. Why don't my queries work?
Sometimes answers in the forum make it sound like these things are a matter of preference, or "which is the best?" But in most cases, they are not--there are well established rules that determine the one correct answer. It is only with an understanding of the principles of relational database theory that you can determine the answers.
So I hope you will find it worth your time to study this 7-page tutorial. If you think you've found something in it that's incorrect, incomplete or impossible to understand, please feel free to contact me. I have been known to make mistakes!
View 5 Replies
View Related
Feb 14, 2008
I have been using access 2003 at my work place.
When I bring back my access 2003 project from work back home, i open the project at home, I am using access 2002 or 2000 format.
Then I notice if I save a copy of my work using access 2002, while initially using an access 2003 database, the file size decresaseas dramatically, from like 3mb to 800 kb?
any reason why?
No data seems 2 be lost.
Cheers!
View 4 Replies
View Related
Nov 10, 2014
I am in the process of trying to create a DragNDrop feature in the Access database that I've built. I have 3 employee with several different txt boxes all side by side and I need to be able to drag and drop the job from one to another.
I need to create a class module to do this but my problem is I don't know how.
View 7 Replies
View Related
May 14, 2007
Hi, I have a query with approximately 100 criteria. That is a bit much, thus I have made a table of the criteria and in the query a relation laid to this table. The criteria are countries with an increased risk on tbc. I point these query on a column with parent 1 and afterwards on the column with parent 2. Then I want the results from both query's in a table, but without double rows. I have tried this with a merge query but these only give the results where the hits for parent 1 and parent 2 are the same. This is by far not always the case, so, I miss all hits where there would be a hit for one parent only. Cumbersome tale, but perhaps there is someone who understands what I mean?
View 4 Replies
View Related
Aug 11, 2005
Im creating a data access page overtop of a relational database. the database is simple... it has customers and workorders. there may be more than one workorder for a customer.
When I create a new customer record through my data access page, I want it to automaticly create a new workorder (filled with 0's)
Does anyone know how to do that?
Thanks
--Ben
PS: I understand SQL... I just need to know where to enter it (if necessary)
View 1 Replies
View Related
Jul 17, 2007
Hope someone can help with this.
Basically we have an Access database with a main table, and several linked tables, joined on a unique ID.
For example :
tblFeatures :
FeatureID
FeatureName
etc
tblCategories
FeatureID
Category
So each feature can have many categories in a one-to-many relationship.
What I have a requirement to do is to pull that out as a flat file in Excel, ie
FeatureID, FeatureName, Category1, Category2
etc
What's the best way to go about this?
Cheers,
Iain
View 2 Replies
View Related
Mar 22, 2005
Hi! I have a table named Employee Records, with a primary key "ERName". I use this to link to a table named Certification Records, with ERName as the link in both tables. My problem is this:
When I add a new record to Employee Records table, the name is not thrown in to a new record in Certification Records. This is a problem, because in my form that uses a query that includes both tables, when I add a certification record, it doesn't populate the name field in Employee Records table. I don't know where I went wrong, but I feel stupid because this is so basic and I should have caught it earlier... any ideas, please? Thanks!
KellyJo
View 3 Replies
View Related
Aug 23, 2013
I have a simple relational database with the proper linking tables, etc. I am normalized through BCNF. I have a table that will track tasking details, however, more than one person is assigned the tasking. How do i add to my form the ability to add additional people to the tasking?
If I drag and drop the name field then it is a one name for one box deal. I want to create a combo box that uses a query to prepoulate multiple elgible names (already built) and then you can select the person you want. Afterwards I want the task assigner to dynamically add additional people to the task with no true limit as the group can be as small as one and as large as thirty.
I attached a small screenshot of the basic table structure. I need to be able to add multiple SME's to the one tasking...
View 2 Replies
View Related
Jan 17, 2013
I'm attempting to set up a relational database utilizing a composite key of several data fields. I had no problem doing this with FileMaker Pro yet with Access 2003 I'm having problems. For example, in Design View I select the field names needed for the composite key.
Yet when I click save I get a message "Microsoft Access can't save property changes for linked tables." As a result I'm not able to construct a composite key of the several data fields selected.
View 1 Replies
View Related
Apr 18, 2005
Hi I have an access database called contact.mdb and I have linked to it just fine and used the records held in it to populate a datagrid and can update and delete records.
I don't understand databases that much but I would like to read the values into a drop down menu which are held in the field 'ID' from a table called 'courses'. I have a feeling it is something to do with data sets but am not too hot on these either. Any pointers or code would be gratefully received.
Thanx loads Lervy :d
View 3 Replies
View Related
