I've got a table called invoice with a field supplier and another one invoice_category among others.
How can I retrieve all the suppliers that have more than two categories in the invoice table.
I tried
Code:
select count(*) from (select distinct invoice_category from invoice)
but it didn't work.
hi,
at present i have an order form. this form has a id number and supplier field . at the moment a macro runs a report which produces the order. the macro does ask me for the order number. the order number is the id number, what i would like to do is get a macro that looks at the id field and supplier field, fetch the e-mail address from the supplier table and send the order to the e-mail address
thanks
steve
Basically trying to create a chart through a Query.
Table is as follow:
Applebrand Date Volume per week Category
------------ ------ -------------------- -----------
I am trying to display the aggregated volume in a chart. The volumes can change depending on the demand from the buyers and its always the latest volume for the specific applebrand that is in play.
Example
Apple A 20150101 10 Retail
Apple B 20150202 100 Restaurants
Apple B 20150303 200 Retail
Apple A 20150404 50 Retail
The chart i would like to see is a aggregated volume for category "Retail" displaying a line from 10 to 210 to 250.
Instead my chart displays 10 to 200 to 50
Is there a way a solve this or should i use a different approach?
I am just starting out in access. I have a table that has different vehicle models. There is a "category" field that has vehicles under boats, cars, planes, heli, etc... How do I make a query that will only show the different categories that are used? So I would just want the category field showing one of each category that exists and not multiple records with the same category.
View 7 Replies View RelatedI have a database with hundreds of records in a main table called tblPARTS which has three fields - Part Number, Part Description and Category. I have another table, tblKEYWORDS, with two fields; a field called KEYWORDS which has words like "cable", "bracket", and "relay". The table has a second field called CATEGORY. The CATEGORY field has either the word ELECTRICAL or MECHANICAL associated with each of the KEYWORDS. So, for example, "bracket" would have "MECHANICAL" as its associated category. There are dozens of these entries in the KEYWORD table.
The problem: I need to auto fill the parts table (tblPARTS) with the correct category by looking at the DESCRIPTION field in the PARTS table which is made of several words, e.g. "STAINLESS STEEL BRACKET - 3" X 2" ". I need to find the word BRACKET and fill in the CATEGORY field in the parts table automatically via a query. There are several hundred parts in the main table.
I can do this easily using a standard update query, but it is limited to 10 IFF statements and there will be many more than this. Can I do this in VBA and if so, where do I start? I am an intermediate user of ACCESS.
I have a simple SELECT CASE query. I'm not sure how the syntax goes and I want to learn about it.
In the attached file, if you click on "cohort table", you will see the categorization for each unit under "field1".
how will I be able to use a SELECT CASE statement in a query to populate each unit with assigned category?
Currently my design consists of a table for customers and a table for suppliers. At first it seems logical. Or so I thought!
As my design is evloving I have noticed a problem. What if a supplier calls and says "I want to purchase a..." they then also become a customer!
Obvoius I know but do I leave the design (attached) as is or disolve the supplier table and then just add one field called [CustSupp] and have three options to choose from "Customer", "Supplier" or "Both"
I could then base any other forms like the [FrmCustomers] on a query that looks at the [CustSupp] field and has a Like "Customer" criteria
Whst is the best method. Any advice greatly received.
I have been plodding along slowly building up a small DB and have hit a slight snag (or 2).
I have a Supplier Form (frmSuppliers). I have a frmSupplierContacts.
I want to prevent users entering data in the standalone frmSupplierContacts?
Also on frmPurchaseOrders I have a combo ContactID. I want the combo to display Supplier Contact Names for the selected supplier only.
I have put as row source:
SELECT tblSupplierContacts.ContactID, [LastName] & ", " & [FirstName] AS Contact FROM tblSupplierContacts WHERE (((tblSupplierContacts.SupplierID)=Forms!frmPurcha seOrders!SupplierID)) ORDER BY [LastName] & ", " & [FirstName];
This displays a Contact Name, but does not change if you select a different supplier?
Hopefully attached DB will explain all.
Thanks for any help in advance.
Phil.
I have a report that creates purchase orders, this could be many suppliers these suppliers have e-mail addresses.
is it possible to get the report to look at the supplier name , find the e-mail for that supplier and send it to them.
Trying to make up a pricing database for jobs
for instance if we have
Bonds : 15mm//17mm//20mm
Downtape : copper // aluminium
I want a drop down box where you select the category Downtape or bonds, then another drop down box showing all the possible choices for bonds if you selected bonds, but wont show you any of the down tape options.
I have been trying to work out the relationships here and been getting in a muddle so any help would be much appreicated.
Juffled
Dear All,
I'd like to solve the following problem.
I have a table similar to this:
material1_date1_qty1
material1_date2_qty2
...
material1_date9_qty9
...
materialN_dateX_qtyW
I'd like to define categories for date (e.g. month) and summarize the quantities:
material1_period1_qty1+2 (if date1&2 are in period1)
material1_period2_qty3+4+...+9 (if date3-9 are in period2)
...
I cannot give conditions/categories in function 'sum' - how can I solve this?
Please help!
BR
xxyy
Hi, I am trying to set up a form so that i can view the employees at my workplace by the scheme they are in. I have tried a number of things with no luck. If anyone could help me i wold really appreciate it. An example would be a god send. Thanks alot.
View 1 Replies View RelatedI have a table of categories with the columns: CatID | CatName | CatParentID
This way I'd like the ability to have unlimited subcategories.
My question is, how do I create a form that will let me enter the CatName with an autogenerated CatID, then enter a subcategory and select the first category as it's parent?
Something like this:
CatId | CatName | CatParentID
1 Tools 0
2 Hammers 1
3 saws 1
Hi,
In a form, I am using two comboboxes per record (i.e. when you add a new record, two more comboboxes for that record appear).
My goal is to have the second combobox show entries specific to what the first combobox was, with certain caveats (see below).
My current approach is using three tables - tblTypes, tblOptions, and tblEntries. tblTypes would store the different types of entries. Each of the types in tblTypes would contain options depending on its ID.
Basically, the first combobox will list the data in tblEntries and the second combobox will list the data in tblOptions, depending on what "Entry" was selected. New "Entry's" for the first combobox would be created in tblEntries, where the ID in this table would indicate what "Options" are available for that entry. As shown in the sample table below, I want to allow more than one entry with the same "ID" (i.e. there could be more than one entry with the same options, but the entry name would be different.)
A sample of what the tables would look like is below:
tblTypes
---------
ID |Type
1 TypeA
2 TypeB
3 TypeC
tblOptions
----------
ID | Option
1 Data1
1 Data2
1 Data3
2 Data1
3 Data4
3 Data5
tblEntries
----------
ID | Entry
1 Entry1
2 Entry2
2 Entry3
3 Entry4
Is this approach sound? If so, how would I create the comboboxes to use with this set of data? I tried doing so but ran into a certain problem - that is, the first combobox doesn't allow you to "select" entries with the same ID - it lets you select the first one, but no other... (using a query that simply selects all of the entries and its associated ID - I used the ID to populate the second textbox appropriately.
If this approach sounds unreasonable, how would you approach it? And after creating the tables, how would you go about setting up the combo boxes?
Any help would be much appreciated.
Thanks in advance.
Hi everyone,
I am tring to generate a report or form, its generated by a combo box, which list all the categories of the products, when the category is clicked on it will generate the report or form showing all the products for that particular category. Can anyone point me in the right direction? Any help would be appreciated.
I am working on a small database for a school to track their live and silent auction. Currently I have a table set up as follows.
Silent Auction table :
Item Number
Short Item Description
Long Item Description
there are more fields that I am not interested in right now.
Mainly what I am after right now is to do some decent reporting for generating the catalog and posting the silent auction items on the web site for viewing before the night of the event.
I need to generate a report that contains the Item Number, short and long description. I need to group on Category which corresponds to the item numbers. Example:
Item Number 100 - 110 = Class Projects
110 -199 = Live Auction Items
200 - 299 = Sports and Recreation
300 - 349 = Entertainment
and so on......
I realize that had they numbered them in the 100 range I could group in my report by 100's and everything would be pretty good however, I did not have the opportuinity to be involved in the numbering scheme.
Even if the numbers had been in the order of 100s I still would need a way to say that the 100 -110 range is Class Projects.
I think I need another table and link it but I am not sure how to set up the range of numbers for the category.
Sorry to be so wordy.
Thanks in advance for the help.
Lee
Network admin trying to learn Access.
Hi all, I want to create a pivotchart from a query, embed it in a form and use a combobox in the main form to allow the user to select the x axis value. So in a query with fields city, state, country, etc the user would be able to chose one of those fields as the category(x axis across the bottom) to display data. Is this even possible? If it is, how do I do it? As it is now I can add multiple categories to my chart- but it just makes a big mess of the chart. I can embed a chart in a form and use a combobox- but it allows me to choose from within a field- but not from among fields. Also- how do I access the code to manipulate the chart? Thanx in advance
View 1 Replies View RelatedI am creating a report which is about 200 pages and would like to add a table of contents for each category i have with page numbers, however i have googled and tried many different things but to no avail and what it the correct way or even showing me on a sample database.
View 2 Replies View RelatedI have a form with the following:
A combo textbox with Category A and Category B.
An Amount textbox.
In the form footer, I have a textbox calculating the sum of the Amount.
This works fine but how do I create a textbox that calculates just the Category A or Category B??
Question... i Wanted to create a Graph that has a bar for each category or column that has dates.. i want it to filter to show only the dates greater than 12-31-2011 so basically all dates in 2012.. i figured out how to do a query but i was only able to do a query for ONE column at a time so i'll have like 30 querys...is there a way to do this with one query and have each bar on the graph be for each column and have the graph showing how many people have completed these after 12-31-2011. URL...
View 10 Replies View RelatedI have been using the below code for a number of years to send reminders to Outlook and colour them red (my category colour for certain items).
I've been asked to roll out this code to all members of my dept but the category for other people assigned to the red colour is different to mine.
Is there a way to colour the item using the colour name rather than the category name?
Set OutlookApt = Outlook.CreateItem(olAppointmentItem)
With OutlookApt
.Subject = MailSubject
.start = Dte
.End = DateAdd("h", 1, Dte)
[Code] ....
Is it possible to send an email from an access 2007 database with a category?
View 8 Replies View RelatedTable: Transactions: ID,Date,Title,Category,Value, Budget Name
Table: Budget: Budget Name
Budget Years: Budget Name, Date From, Date To
Form entitles Budget, Fields : Date From, Date To, Budget Name
So within the form budget I have a subform which I want to group the categories and get a total value. THE tricky part is, I only want it to include values from the budget specified in the form and the date range of the form.
Here are my tables
Question Table
ID
Question
AnswerID
CategoryID
Answer Table
ID
Answer
Category Table
ID
Category
So my form shows :
Question, Answer, Category
And I can display any existing question and make updates to the question and answer, that works fine. BUT, what I want to do is update which category a question is in. If I change the Category in the form, it updates the 'Category' in the Category table. What I want it to do is update the CategoryID in the question table.
I'm having trouble with my VBA module.I have a random test generator which pulls records based on a category from my table into a temp table using a make table query. I use the following code:
Private Sub Command2_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "1", acViewNormal, acEdit
DoCmd.OpenReport "WrittenExam", acViewPreview, "", "", acNormal
Reports!WrittenExam.lblTitle.Caption = "Exam Name"
DoCmd.OpenReport "WrittenExamAnswerSheet", acViewPreview, "", "", acNormal
Reports!WrittenExamAnswerSheets.lblTitle.Caption = "Exam Name - Answer Sheet"
End Sub
My querry makes the table, and then generates two reports (my exam, and the answer sheet). I'm getting an error 3211, saying the temp table is already in use by another process when trying to generate both reports. I used a Macro before, but I have a need for custom report headings, so I'm using VBA.
There are 3 drop down button that should filter category of items in a database:
Category 1
Category 2
Category 3
Whatever users choose in category 1, it will filter the items in Category 2. whatever users choose in category 2, it will filter the items in category 3. users can choose 1 to 2 categories or sometimes the 3 categories. the items will show in a subform. the subform are getting the items from a query. the dropdown button gets its value from a Table.
My challenge is that, i want to get the number of items in Category 1 once users chose it. if they choose something in Category 2 the number of items will change also. there will be 3 textbox for Category 1 COUNT, Category 2 COUNT, and Category 3 COUNT.
as of now, what im getting is only the filtering of Category 1 and Category 1 COUNT.