I am working on a database to list all the inkjet cartridges we sell. Within the printer table, I want to list which category they are on our website. For example, Epson C20 Printer would be in Ink & Toner > Epson > Inkjet > Stylus C & CX Series.
I what some way to enter these in a number of fields, say Cat1, Cat2 etc. I havent a cluw what would be the best way to do this? Has anyone got any ideas? I am thinking about some kind of drill down but wouldnt know where to start on this. Any help would be gratefully received?
Suppose I receive boxes and store them in a room. Each box can contain items that are either shaped as cubes or spheres and I have to allow for the addition of more shapes in the future, but only one shape can be in each box. Each box contains a random number of shapes and two boxes may, or may not, contain the same number of shapes as another.Cubes can be either Red or Green, Spheres can be either Blue or Yellow.
I wish to set, and have the ability to change, how many cubes and how many spheres, on a percentage basis, I wish to have (e.g., 60% Cubes and 40% spheres).I also wish to set, and have the ability to change, what percentage of cubes I want to be Red and Green and which percentage of Spheres I wish to be Blue and Yellow (e.g., Cubes -Red 70%, Yellow 30% )
e.g. -- I have several boxes totaling:
Cubes Red 30 Green 40
Spheres Blue 10 Yellow 20
My goal is to produce three reports.The first report would provide the Target and Actual number of Cubes and Spheres expressed as a Value and %. The #'s would simply be calculated from my desired % . So, if I wanted to have 60% cubes and I had 100 Shapes, my target # would be 60. If I had 200 Shapes it would be 120. (i.e., The actual number of shapes I have is simply a factor of how many I receive and I have no control over this.)
The First Report would look like this
Target % - Actual % - Target # - Actual #
Cubes 60% 70% 60 70 Spheres 40% 30% 40 30
The second and third reports would be the same format but would break down cubes into red and green and spheres into blue and yellow.I can accomplish the Report that breaks down the shapes but I am running into problems with the next level as I need the total number of shapes and the the total number of only Cubes or Spheres to insert into the expressions.
I'm very new to Access, having used it for the first time today :)
I've got a database to write for a media database as a learning example and I've run into my first real problem...
I've got a table for users and I've got to allow users to be able to own one or more media types, e.g. CD, DVD, video, record, etc. Of these types, there are sub-categories for most of them. For example, a DVD may be a horror film, action film or music video, and a CD may be hip-hop, rock or blues, etc.
I thought I could have a reference data table containing a category ID and category value, together with a reference data category table which simply listed the categories. This would work fine if a user could only have one type of media but of course they can have several. So, I thought about having a "composite category" instead which would contain a category, whether it contained a sub-category and a sub-category ID. I'm trying to get my head round this and wondered if this must surely be a fairly common issue to solve. Does anyone know of any pointers or any examples I could do with looking at?
Okay here is my silly little issue, my boss decided to add sub categories, just when I get the database set-up. Table 1 Category Category_ID Category_Name
Table 2 Sub_Category SUb_Category_Name
Table 3 Contacts Company Contact Adress Ect Ect
I would like to be able to have each contact grouped by category, then sub category..but I am not sure how to get the relationship to work.
I am implementing a new database (Access 2000) for my company.
It contains contact details of a subscription base.
Four tables are present MEMBER, ORGANISATION, EVENTS and CATEGORY.
My problem is; people attend events, and i want to record this in the database. Each event has an event ID field, and an event description field.
When recording the events which people attend, i could just use the event ID field, but problem is that people can obviously attend more than one event..
Also;
People belong to a certain category
Again i have a category ID field, but people can belong to more than one category.
I'm wondering how to design an Access database with the same type of structure as "Yahoo Shopping!". The Yahoo site allows search for women's clothing or men's clothing or all clothing...please explain how the relationships are structured.
Is it just one category table with queries that display results of a search of multiple categories? How do I structure it in Access queries and forms?
I've built a contacts database which consists of one table containing contacts and services provided and a table with a list of categories, each has a PK autonumber id field. My problem is that individual contacts could fall into multiple categories, could somebody tell me how to do this.
- Category (A, B, C) - Family (A1, A2, A3, B1, B2, etc) - Item - Sales
I'm looking for a way to get the top 10 items in each category + family based on sales (ie: top 10 in A-A1, then A-A2, then A-A3, etc). When I try using the top function, it doesn't treat each category + family combination as unique. Any ideas?
Got a problem here with Access 2003: got 36 categories (example: volume from 0-0.1=category 1 vol from 0.1-0.2=category 2 etc) and a whole bunch of records (in a table) stating volume per record (0.111, 0.23456 etc). For each record I need to know which category it is. Now in order to prevent me from writing 36 update queries for each category (if vol>0 and <0.1 then category = 1 etc) there must b a smarter/faster way to do this. What is it?
I have a form for recording site details and have been trying to autofill three of the four categories.
What I have is a Site Index Table that contains the site ID, Name, Description and Type. I want the results to be recorded in an Affect Table that will also contain text data associated with these fields that I will enter in manually.
I then have a form with the four fields; the site ID and Type are both bound to the Affect Table that records all the data associated with each site. The Name and Descritpion source their information from a seperate query of the Site Index Table.
So what I want to do is to select the site ID on the form from a combo box I've created, and once selected it will automatically fill in the relevant text data into the Name, description and type categories on the form. I've been trying scripts with the DLookup function but have continued to encounter errors. If anyone can help PLEASE help me as i'm a novice when it comes to Access
I need to create a database of the info of a few hundred companies. Each company (food producers) specializes in various product categories, not just one. This is my problem/question: each company entry should then have a possibility to have as many product categories as needed. How can I do this without using "free text" fields? The website user should obviously also be able to search the db - by clicking checkboxes with the categories maybe?
(+Also: When the admin user needs to add the info of a new company (with, say, 9 product types) into the database, what kind of an SQL clause I need to INSERT INTO the db? For example if the product categories where laid out as checkboxes, and the user would just click the ones he wants.)
I would like to separate my list box in two categories.Indeed it is composed by 2 types of criteria but arranged in the same List box (I can't create another List box, it's forbidden).how can I proceed?I've thought to insert a dash but it has to appear all the time and I don't know if it's possible to do that.
I've also thought to enter a title IN the list box above each category but it has to appear all the time too..when I click on one item in the List box it becomes highlight.May be code the 2 titles I could add for being always selected (highlight).I don't want to have to select the 2 titles every time!!!
I have a bunch of records that are assigned to categories which are problem tickets. I would like to print the report showing not just the categories that have records open in them now, but also ones that don't have any records in them. So say I have these three categories. Power, Circuit, Hardware failure..If I have a few open tickets in circuit and hardware failures and none in power. Power will show show up on the report but with no records listed underneath it.
I am facing the difficulty to solve the calculation of my Employees payment table in query, employees having three type of payments 1. Leave 2. Air ticket 3. EOSB so I have created three different queries named Airticket_Accruals, Leave_Accruals, EOSB_Accruals for these tables.
The problem is I need to include how many payments have been done to every employee in total to my every individual query (airticket,leave & EOSB) than I will less the accrual that will give me the balance I need to pay them.Field for Payment table is as follows and it has relation with Employees table with Emp_ID field.
I use a combo box to select products and place in my order details sub form .I now realise i need a catogories table . How can i get my combo to look up a categories table then go the products of that table then enter in my orderdetails.
I need each item that falls under any one of these categories to have a unique identifier starting with the category number.
Example: Safety Glass - - - part number 930001 1.5 inch Cable - - part number 580001
I want to be able to select a category from my drop down box on the form and for the part number to auto fill with the next available number in that sequence.
So I have a form that has a sub form on it (based off of a junction table), indicating which category a particular project belongs to. It can belong to multiple categories:
ProjectID 100 Category Admin Finance
But I want to run a query to find all of the projects that are both admin and finance. When I do that now, it comes up as two separate records in the query, which is fine... but I have to know the projects before hand to find if it's in both, because it returns heaps of records, anything that says admin or finance... and that defeats the whole purpose. I want the query to show me JUST the projects that have both of those in their subform..
I'm developing my own personal finance database and the part stumping me on the design is how to setup the categories per transaction. For example a primary category would be "home" and the secondary category would be "furnishings". How do I set this up? I'm getting stuck on how to tie it into the transaction table.
I'm familiar with writing SQL queries, but this is really my first stab at database design.
The relationship diagram is attached. The transaction table and the category tables are to the right of the diagram.
I use custom categories in the navigation pane to make finding the right objects easier (duh) but now I have quite a lot of custom categories and they are stuck in the order they were created in.
I would like to rearrange them into some logical order (maybe alphabetical, maybe custom) so i can find the categories quicker.
I have a pivot chart based on a crosstab query. I would like the items on the category axis (x-axis) to appear left to right in the order that they appear in the query results.
Some specifics: Tables: tblFreq FreqID (PK, Number, Range 1-7) Frequency (text)
tblResp RespID (PK,Number, Range 1-5) Response (text)
tblResults ResultID (PK,AN) FreqID_FK RespID_FK
Query: TRANSFORM CInt(Nz(Count(tblResults.ResultID),0)) AS CountOfResultID SELECT tblFreq.Frequency FROM tblResp INNER JOIN (tblFreq INNER JOIN tblResults ON tblFreq.FreqID = tblResults.FreqID_FK) ON tblResp.RespID = tblResults.RespID_FK GROUP BY tblFreq.Frequency, tblFreq.FreqID, tblFreq.FreqID ORDER BY tblFreq.FreqID PIVOT tblResp.Response;
[code]...
Which I suppose is alphabetically ordered.I am unable to use OrderBy in the forms property sheet because tblFreq.FreqID is not an available field, even though it's an expression in the query.
I am creating a database relates to hospital medical equipment using Access 2003. Using VBA code, I want to generate an AutoNumber for each Equipment falling under different Equipment and different Hospital.
Ex: DGG-ECG-001 DGG-ECG-002 DGG-XRY-001 WBH-ECG-001 WBH-XRY-001 WBH-XRY-002 and so on.....
Where DGG & WBH are Hospital Codes (PK in tblHospitalCodes) ; ECG & XRY are Equipment Codes (PK in tblEquipmentCodes) ; last 3 digit denotes the AutoNumber.Both the Hospital & the Equipment select using combo box then generates the AutoNumber Using command Button.
I've got a continuous form that displays my massive 3,000+ inventory, with the option to filter down to specific categories. Some categories have 5 items, others have hundreds. The design I have used makes it look really tacky once I filter down to one of the categories that has 5 items, because there is no scroll bar.
What I've tried using is:
Code: If Me.RecordsetClone.RecordCount < 10 Then Me.Scrollbars = 2 'vertical only Else Me.Scrollbars = 2 End If
But it still doesn't display correctly (nobar.png). This is probably my biggest headache right now, and every "solution" I've found, doesn't work.
Here's the situation. I have a Database in Excel (~3000 rows & 7 Columns)
The first Column has numbers like this (3-10-34 or 223-7-45) the other columns have names, addresses and other stuff.
What I'm trying to do is to eliminate the use of huge binders. I want to have a Blank screen with only one input box where I can type a number and retrieve a specific row from my Database. Time saving.
Now, when I'm searching, I usually only have the first numbers before the dash (such as the 3 or the 223) not the other numbers. So I cannot input 3-10-34 for instance. I can only put 3...:confused:
I've already imported it to Access and it works fine but have no idea how to do what I just described!
sorry if this question is answered somewhere else i did check through the forum a bit first
anyway to the question
is there a way in access 2003 so when i change a value in one table it creates a new record in another table
example
i have a table called tickets and one called fixes, when i change a value in a record in the tickets table i want a new record in the fixes table to be created for me
