Planning Help: Creating Records In "two Levels"

Feb 28, 2007

Hi all! Need a bit of planning help with this one.

I have a access db for a small webhosting company. In this db there is a table containing the different sites, and another table containing siteproducts (Domains, Hosting, etc).

I want to make a function that creates a invoice (tblInvoice) for every site with billingdate in march. This function has to transfer the siteproducts (tblInvoiceLine) as well and gives the invoiceline the right invoice number.

I guess I have to do this with querries, but I have noe idea how to transfer "two" levels - or from and to two tables at once.

Any suggestions?

View Replies


ADVERTISEMENT

Auto Creating Record In One Table For Groups Of Related Records? + Stock Levels

Mar 23, 2008

Hey guys,

OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:

http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg

At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:

If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.


My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so

Any help?

Thanks a lot in advance!

Daniel

View 4 Replies View Related

Database Planning

Mar 13, 2007

Hi All,

I'm Looking for a bit of advise. I'm about to try and build a new database that will have about 40 tables. One of these will be the main table. This table will have a primary key TAGNUM and several other fields containing data associated with each tagnum. Most of the other tables will then contain more detailed info about each tagnum.
So the obvious aim is to have all these secondary tables connected somehow to the main table so that when a new tagnum is added/deleted, the corresponding row in any effected secondary tables is added/deleted also.

My attempts so far have involved setting up a relationship beween tagnum in the primary table and tagnum in each secondary table. This appears to work ok but I'm trying to be sure that my setup is good. I have a one to one relationship in each case, I have selected referential integrity, and the cascade changes etc. My join type is "All records from main table and only those... secondary table". Is this correct?

The further complication is that I need to have Lookup?? tables (not sure if it's the correct word) tied to each of the secondary tables and 3 tied to the main table. The user needs to be able to select items from the fields bound to these lookup tables via a dropdown box on the field in a query. When the dropdown box is selected a descriptor for each option available need be seen The non bound fields in the lookup tables only needs to be available to a report bar the showing of the Description field during selection. Any ideas?

Sorry about the size of this and annoying you with what is i'm sure not difficult stuff! Pretty new to this.:)

View 14 Replies View Related

Table Structure / Planning - Help Please

Sep 6, 2007

Hello

I wonder if anyone can help me?

I am currently creating my first Access database and in the planning stage but have come across one problem area. I need to create a database that holds and reports on information recorded on a clinical form. The section of the form I am stuck on contains a table as below.

123456
Catheter Type
Wire Type
Balloon Type
Type of Stent
Size of Stent
Target Vessel
Successful?Y / NY / NY / NY / NY / NY / N

The user works their way down this form starting in column one but free typing what make of Catheter they will use and then type of Wire and so on. The problem arises when a particular item doesn't work, e.g. a type of Balloon. They then try a different type of balloon and would enter this information in Column 2 and then if this works go back to column 1 to fill in the next field 'Type of Stent'. As below..

1 2 3456
Catheter TypeCathsRUS
Wire TypeWire 1000
Balloon TypeBAL001 BAL002
Type of StentST555
Size of Stent10
Target VesselLeft side
Successful?Y / N Y / N Y / NY / NY / NY / N

Not only does the user need to record all this information without duplicating anything else from column 1 into column 2 but there are also instances that within the same procedure number, that a second bleed occurs and they need to fill in the next available column, which in the example above wiuld be column 3. They would again work their way down, but this time they may not need to use a wire as they can reuse the last one.

1 2 3 4 56
Catheter TypeCathsRUS OpCa34
Wire TypeWire 1000
Balloon TypeBAL001 BAL002 BA3
Type of StentST555 ST333
Size of Stent10 5
Target VesselLeft side Right side
Successful?Y / NY / N Y / N Y / N Y / N Y / N

The access database needs to be able to hold all this information and show that 2 separate episodes in affect have occurred. The user wants to be able to report on various things such as, what type of Stent/Wire/Balloon/Stent size is the most/least used / successful/unsuccessful? Etc

If anyone can help shed some light on the best way to structure this information in Access I would be very grateful!

View 4 Replies View Related

Problem With Planning Database + Creation Of Forms

Aug 11, 2005

Hi guys
Quick question. im running a planning database which has got 3 tables Objectors, Representations and Agents. Ive put these together using a one-to-many join matching up the index fields from Objectors and Representations. Ive just constructed a form which has a main form being the Representations and the sub forms with Objectors and Agents. When I want to update or add records a message saying " you cannot add or change a record because a related records is required in table Objectors.

Can any one tell me how I can get around this or possible solutions to this is? Any comments or feedback greatly appreciated!

Cheers

View 5 Replies View Related

Planning – Adding Time Management/registration

Dec 18, 2007

Hi all,

I’m adding a new function to our Costumer Relations Management system – Time management. Or maybe “time registration” is a better definition for it. The idea is that our consultants can add their hours into our CRM, either from the “Project”, “Tasks” or “Costumer” part of the CRM. Note: This is a tool to keep track of hours spent on a project, not a planning tool.

I’ve made the input-form for the records (Date, ClockStart, ClockStop, TimeSpent, InvoiceHours, etc.). But now I’m basically without a clue on where to start – I want to make a function almost like the calendar in Outlook, where the consultants can input what they’ve been working on, say between 13:00 and 14:00.

It doesn’t have to be fancy graphically, but functional and give a good overview.

Any ideas?

View 6 Replies View Related

Unbound Combo Box Filter On Planning Database

Oct 14, 2005

HI THERE. IM HAVING A FEW PROBLEMS TRYING SORT THIS PROBLEM OUT. I HAVE A DATABASE IM TRYING TO APPLY A FILTER ON THROUGH AN UNBOUND COMBO BOX. WHEN SELECTING THE DROP DOWN FILTER TO SORT THE RECORDS NOTHING HAPPENS. THE CODE IM USING IS
Private Sub Combo??_AfterUpdate ()
'Find the record that matches the the control
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[WARD_AREA] = '" & Me! [Combo??] & " ' "
If Not rs.EOF Then.Bookmark = rs.Bookmark
End Sub

Does anyone know why this filter is not sorting through the records. Any comments much appreciated!!

Attached is the database

View 1 Replies View Related

Access Levels

Apr 10, 2007

Iv got a user table with an admin record and head teacher record with the highest access level

I then have 2 teacher records with a lower access level

The admin and headmaster have access to the whole system this includes assigning user name and passwords. However I don't want the head master to be able to change the admins password, so is there a way to lock this record or even hide it from a user form

View 5 Replies View Related

Stock Levels

Oct 16, 2007

Hi hope someone can help. Thankyou in advance.

I have a small database for my warehouse that sells washing machines, fridges etc.

I am trying to get a grip on the stock control.
What I am looking for when a order is filled out on our order form is the stock number will reduce by the amount sold. Not sure how to make this happen.
I have the number of units in stock under the products table

ProductsID PK
ModelNumber
UnitPriice
Description
QuantityInStock

Sounds simple but for some reason I cannot figure it out.

Thanks
Kim

View 1 Replies View Related

Stock Levels

Feb 7, 2007

hello, i am new to this forum. i have been struggling on a project i am doing where i am tring to calculate the stock levles and keep track of them along with the re order level
i have calculated the current stock levlel, in a query called "stock level 3"
Here the total amount prodcuts sold are taken from the field called stock level.

Here is a break down of my original tables

CUSTOMER

Cu ID
And all their personal details such as contact details


CUSTOMER ORDER
This is where the order takes place and contains the following information


Order_ID
Cu_ID
Staff_ID
Order_Date
Required_Date
Recievied_Date
Order_Total
Delivery
Delivery costs
Overall discount
Type-Of_Purchase
Paid
Money_Owed
Final Costs


CUSTOMER ORDER DETAILS
Here is the specific details of one order.

Order_ID
Prod_ID
Qty_on_Order
Discount
Line_Total

PRODUCTS

Prod_ID
Supp_ID
Product_Name
Product_Type
Prod_ Unit_Details
Qty_in_Stock
Re_Order_level
Price
Unit_Cost
Manufacturer_ID
Shelf_Life

SUPPLIER TABLE – with detaisl e.g Supp Id and account number

SUPPLIER ORDER
Supp_OrderID
Supp_ID

Date_Ordered
Date_Required
S_Delivery_Date
S_Order_Total
Money_Owed Delivery
Driver
Supp_Credit_ Limit
Overall discount
VAT
Type-Of_Purchase

S_Paid
Supp_Final_Costs

SUPPLIER ORDER DETAILS

Supp_OrderID
Prod_ID
Qty
Unit_Detials
Qty_Recieved
Discount
Line_Total


QUERIES

•In the query “All customer order details”, I have worked out the line total for each prodcut with in one order. I have timed the Qty_on_Order (customer order details table) by price (in the produt table) and by the discount(customer order details) this then gives the line total

Now to update the stock level I made a new query called “stock level Query”, where I added up the total number of each products sold, by adding the “Qty_on_Order” from the query “All customer order details”

I then made a new query called “stock level 2” where I calculated the amount left on the shelves by taking “SumOfQty_on_Order” of each product from “Qty_in_Stock” (form prodcuts table) to produce the field called “Qty_in_Stock”

I then made a new query called “stock level 3” where I inserted the same fields but an extra field called “Re_Order_level” for some reason could not add this to the query "stock level 2"


So far the system can add up the total number of products sold, for all the orders, and then automatically calculate the Qty_ in_ stock by taking away the “SumOfQty_on_Order” once a new order is made.


I then made forms where I go in my form to “customer”" and then to “making new order”.
Below is a sub form called “all customer order details sub form” . Here orders are made with defining "Qty_on_Order

I want to make a system which calculates for me i have ordered more stock then there is.
Howcan i now when i make sure when i place a new order in the query"All customer order details" , that if i order above the number of "Qty_in_Stock" (from query srock level 3), from the query “stock level 3”, that when I order more products then ““Qty_in_Stock”, in the query “stock level 3”, does not allow me to enter this and if it is possible please can you insert error messages on it.


I do not know if this helps .- I can not make the “Qty_in_Stock”, to appear in the actual table “Products”.
At the moment I can only update this in the query “stock level 3”. I am not sure if I need this so I can then set re order level which then comes up with an error message telling me when the re order level has been reached. -

As later on I will need to add on any products brought in from the suppliers to the Qty_in_Stock”, in the products table to be automatically updated.


"stock level 3".
here are the fields

Code:
Prod_ID (Products)
Prod_Name (Products)
SumOfQty_on_Order (from "stock level Query")
Qty_in_Stock: Products.Qty_in_Stock-[SumOFQTY_on_Order]
Re_Order_level (Products)


here are both sql from both queries

All customer order details

Code:
SELECT [Customer Order Details].Prod_ID,
Products.Price,
[Customer Order Details].Qty_on_Order,
[Customer Order Details].Discount,
(Products.Price*[Qty_on_Order]*
(1-[Discount])/100)*100 AS Line_Total,
[Customer Order Details].Order_ID,
Products.Prod_Name,
Products.Prod_Type,
[Customer Order].Cu_ID
FROM [Customer Order] INNER JOIN
(Products INNER JOIN [Customer Order Details]
ON Products.Prod_ID=[Customer Order Details].Prod_ID)
ON [Customer Order].Order_ID=[Customer Order Details].Order_ID
ORDER BY [Customer Order Details].Order_ID;



stock level 3

Code:
SELECT [All customer order details].Prod_ID,
Products.Prod_Name, [stock level Query].
SumOfQty_on_Order,
Products.Qty_in_Stock-[SumOFQTY_on_Order] AS Qty_in_Stock,
Products.Re_Order_level,Products.Prod_Name,
[stock level Query].
FROM (Products INNER JOIN [All customer order details]
ON Products.Prod_ID = [All customer order details].Prod_ID)
INNER JOIN [stock level Query]
ON Products.Prod_ID = [stock level Query].Prod_ID
GROUP BY [All customer order details].Prod_ID,
SumOfQty_on_Order,
Products.Qty_in_Stock-[SumOFQTY_on_Order],
Products.Re_Order_level;

please may help me and gice m,e soem tips it will be much appreciated or website/downloads.
Thankyou for your help, and if you find easier ways to do this can you help me.
Thank you so muh.

View 1 Replies View Related

Avoiding 20 Levels Of Iif-Then-Else

Mar 14, 2008

I'm sure there's an easy solution to this somewhere...

I have a query that is supposed to assign a wage to a production job if the range output falls within a certain percentage range. For example, if they produce 125% to 130% of their goal, their wage is 15 cents higher. If it's between 130% and 135% of their goal, their wage is 30 cents higher. There are about 20 ranges that my bosses want set up. Short of making the longest Iif-Then-Else statement in history, does anybody have any suggestions? Should I use a separate table and somehow try to integrate it into the query?

View 14 Replies View Related

Creating New Records

May 24, 2005

Hi

I wonder if anyone can help. I attach a table that is a material list (27000 records). When I add a new material I want to be able to see the last part code I created for a particular supplier.

For example C001, C002 and C003 are for a supplier company called Chapters so I would scroll through the combo box search list in the form for the last one and create a new record called C004. The problem is that E00001, E00002 for a company called Edwards supplies 20,000 items so it's a long way to scroll through to create a new part code called E20001. How can I show in the form what the last part code is for each Supplier?

I attach the form and table as I'm not very good at explaining this, although I have had to delete approx 20000 records as the database was too big to be posted. Any advice/help would be greatly appreciated, many thanks

View 1 Replies View Related

Creating A Set Of Records

Nov 23, 2005

I have a database in which i keep track of my books collection. Amongst my tables i three of them are:

tblBooks - main books table
tblKeywords - where i store keywords (such as "database", "programming", "web development" etc)
tblBooksKeywords - to create a many-to-many relationship between the above two tables.

Now in tblbooks i have about 270 records (1 per book). Now for each book in this table i would like to create a record in the tblBooksKeywords table to add a the keyword "Computers" to each book.

i.e.

Book1 - Computers
Book2 - Computers
and so on till Book 270

Now rather than i do this manually for 270 records is there a way in which i can create these 270 records automatically ?

Thanks

View 5 Replies View Related

Macro Security Levels

Nov 3, 2005

Has any one found out a way to change Macro Security Levels!!
Microsoft seems to have kept this as a secret. The code below given in the help files doesnt function.
Application.AutomationSecurity = 1
or
Application.AutomationSecurity = msoAutomationSecurityLow

View 8 Replies View Related

Subtracting Stock Levels

Nov 9, 2005

Hi, I have a data base that is used to keep track of stock level. When i issue a stock item and move to the next record I would like the stock level to be reduced by 1, any ideas.

View 2 Replies View Related

Adding Stock Levels Together

Nov 12, 2005

I have a database which seems to open many copies when it is accessed over the internet (via ASP) but If I open it on my compter over my LAN then it seems to open 'exclusive' and those on the Internet get the error message that 'file is already in use'.

So I have devised a method where I will make a copy of my database on my computer, clear all the stock levels and then add in the new stock.
My question is - how do I write a query that adds the 2 stock levels together?

James

View 1 Replies View Related

Macro Security Levels

Jan 7, 2006

I've written a db which I will be putting on someone elses machine.

I want it so there is a short cut that directly opens the db from their desk top. I'm happy about setting up the 'on open settings' so it goes to the default page etc, however what I do want to avoid is the macro warning at the start when access opens up.

I know one way to avoid this is to drop the security level down from medium to low. It mentions that this is ok providing the machine in question has good anti virus software installed - which it does.

So, here's my question. Is it really ok to drop the security level down or am I potentially going to screw up my mates machine . Alternatively, is there a better way of doing it

Many thanks

View 1 Replies View Related

Multiple Levels Of Data

Jan 14, 2008

I'm fairly new to access, having done a lot of what would have been easier with access in excel. I've decided to make the switch but need one particular thing that I can't seem to work out.

I have a series of "Courses" which contain a series of "modules" and each module has various amounts of data that needs to be accessed.

Is it possible to have module tables embedded in the courses table. Or, do I have to just have one massive table that contains ALL the modules with a field that says what course they belong to. Then make queries that display only the modules of that course? Or is there another way to do it?

I also haven't gotten to learning about queries yet, so if this is the way to do, can anyone explain how or better yet, point to a tutorial that can explain how this is done?

View 6 Replies View Related

Creating New Records In A Table

Sep 28, 2006

I think this is a pretty simple thing, but for some reason I'm lost.

I need to add a number of records to a table.

The user would input a starting record number and ending record number (ie 60000, 60003) and access would create those records with the record number field populated with all the values, in other words, it would create 4 records 60000,60001,60002, and 60003

any ideas how this can be done? I'm thinking a query, but it might require coding of a for...next loop???

Thanks in advance for the help

View 4 Replies View Related

Subform Not Creating Records

Nov 11, 2004

Hi, im having a problem with shring my db over a networkt,

as far as i understand access 97 locks a page (2k) of records when a record is being edited,
does anyone know a way in 97 how i can get access to lock ONLY the EDITED record and not the page?

in Tools/Options/Advanced i have "edited record" locking clicked and each form locks only the record edited but its still locking the page, i know there is "record level locking" feature in Access 2002 but not in 97, is this possible?

thank you

Scott

View 1 Replies View Related

Creating Top Values With Two Records?

Oct 15, 2007

Hey all,

I have two records for a list of employees: start date and end date. I want to create a query for the top 5 employees who have been employed the longest (and are still currently employed, so end date should not exist for the top 5). How should I go about this using a top values property? thanks.

View 1 Replies View Related

Creating New Records For Students?

Jan 23, 2013

I have a simple database with 2 tables, students and progress. I need to set up my database so that when I create a new record for a student (using a form I've created) it automatically creates 4 new entries in the progress table using the ID I have generated in the form and a task number (1-4) for each of these entries.

Additionally, once all tasks are set to complete = true, I need to set the field "all tasks complete" to true. I'd like to do this all without vba if possible.

View 9 Replies View Related

Adding Security Levels For Different Users

May 15, 2005

I run a school library database (Access 2003) and want to set up a password logon system so that different users can get at different parts of the program - e.g. Administrator (everything), Librarian (can add and delete records but not change the database design), Student (can only access certain areas, read only).

I've tried using the security wizard to set this up, which seems OK until you then open the database again. I would expect to then get some sort of log-on screen next time I open the database, but instead it opens normally, but then won't let me do anything because I "don't have permission". I had to restore a back-up copy.

I must be missing something obvious here. Is there a place to look for instructions? Couldn't find anything useful in the help screens.

Richard

View 2 Replies View Related

Access 2000 Security Levels

Nov 11, 2005

Hi

I have produced a database in Access 2003 and saved it as an Access 2000 format. However when it is used on PC's with Access 2000, some (not all) users are getting messages displayed when they open the database that Access cannot open the database due to the Security Levels.

Can anyone tel me where I can find the security level settings on Access 2000, I've found them in 2003, but not 2000!

Can anyone help.

Thanks

Steve

View 1 Replies View Related

Counting And Checking N Levels In A Recordset

Dec 10, 2007

Hi,
I need to work out how many components we have in stock that make up a Part - identified by being the PrimaryPart. Each part has a component, which in turn has another component, but different part have a different number fo sub-components, a simple display of the table I am using is shown below:

ID PartName ComopnentID No. in Stock PrimaryPart
1 Part X 2 12 Yes
2 Part Y 3 14 No
3 Part Z 15 No
4 Part T 5 1 Yes
5 Part U 6 11 No
6 Part V 7 25 No
7 Part W 20 No

The problem I have is that I need my vba code to check if there is a sub-compnent for the current part and if so make sure that is counted. So in the instance above Part X has 2 sub-compnents (Part Y, Part Z) and there are 41 bits in stock, whereas Part T has 3 sub-compnents (PartU, PartV, PartW) and there are 57 bits in stock.

How do I get a script to recurse X number of times to check.

Thanks

View 3 Replies View Related

How To Handle Multiple User Levels

Aug 23, 2011

I've got this database that needs some users to be read only and some users to have full control. I applied the appropriate ntfs permissions but there is a problem. If a read only users open the database, and then a full control users opens the database its stuck in read-only.

The database is a 2007 accdb.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved