Set Of Data Available Depends On Category Selected
Aug 4, 2005
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.
View Replies
ADVERTISEMENT
Mar 13, 2007
Hi,
Best to have a look at attached relations picture.
Basicly this is what I'm wondering about:
I add a new customer who bought a product from a specific supplier.
That unique combination gives me the right on commission.
From company A I get, let's say 5% of the capital.
Company B gives me 4,25%
Products are insurance policies, so at certain products you are insured for a capital i.e €100.000,-
Percentages given are to be calculated of that capital.
So I have a lot of possibilities here which determines my provision.
What is the best way to handle this ????
I have no clue where to start.
The field [Polissen.Provisie] is now manually filed in on the Form where I add all details, which should remain possible (sometimes there are different agreements concerning the fee)
Hope it's a bit clear..
Please ask if more info is wanted.
View 14 Replies
View Related
Nov 24, 2005
I have a serial no combo box which displays serial numbers available for shipment. What i need is some way to make the combo box show the values 'serial numbers in stock' (simple sql) when the [tocompany] is not 'CVP' and to show 'serial numbers out' (another simple query) when the [tocompany] is 'CVP'
Basically i need to change the row source depending on the value in the [tocompany] field.
Thanks in advance
Ed
View 1 Replies
View Related
Nov 6, 2014
I have a table machine with fields:
ID(PK)model ( lookup column to machine model)serialnotype
Then a table meter reading:
IDmachineserialno (lookup column to tblmachine/serialno)readingdatetotalcounter
I want it to be like when i press lookup column in tblmeterreading/ machine serialno....it shall show the machine serial no|model in the popup combobox.
Instead it shows my machine serialno|id,
for example: when I select the machineserialno. a combobox shall show:
s/n:221233 | sony
instead it show:
s/n:221233 | 1
??
View 4 Replies
View Related
Oct 8, 2014
I have a cross-tab query that is filtered on user-defined date range using a where condition of:>[Forms]![Reports_Menu]![txtFilterDateFrom] AND <[Forms]![Reports_Menu]![txtFilterDateTo]
the Query functions perfectly. The report based on that query, however, only functions with a DateFrom that is before 1/28/14 and a DateTo that is after 2/27/14. If I enter a date in either field that is outside of those ranges, I get the following error:The Microsoft Access database engine does not recognize " as a valid field name or expression.I have defined both of my Query Parameters as Date/Time in the Xtab query design.
View 7 Replies
View Related
Jul 6, 2006
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
View 3 Replies
View Related
Jun 18, 2007
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
View 3 Replies
View Related
Feb 14, 2006
hello everyone
i created 4 tables
Departments (DepartmentName as prime key),
Employees (EmployeeCode as prime key, as prime key field can't have duplicates as it occuring in case of EmployeeName),
ErrorReference (ErrorCode as prime key) and ErrorCategory
I built onemore table "Performance", in which 'workOrderRef, Date, Cost and WorkOrderPic' are local fields and rest of fields (departmentName, EmployeeCode, EmployeeName, ErrorCode, ErrorRef) are linked to different database to prop up data while data entry.
Also did relations between these tables in order to facilitate cascading effect of modification in respective database bases.
Now the question is - in input database file i.e. "Performance",
- when i prop up "CAD" department, i should get employees in CAD department and in turn employee code.
similarly
- when i prop up any Error rerefernce, it should automatically prop up corresponding ErrorCode
can anyone tell me how to se filter command?
Thank you
Rao
View 2 Replies
View Related
Dec 8, 2006
Can someone please help me?
I have a combo box on a form that the user can select a player name.
Under the combo box I have a label called "Show me information about selected Player: that once clicked takes the user to another form which has all information about the player that was previously selected in the combo box.
Everything works fine UNLESS the user forgets to select a playername from the combo box and clicks on the label anyway. Obviously no player name was selected so a blank form opens which is a problem!
How can I ensure that a player name is selected and prevent the user from clicking on the label below without having previously selecting from the combo box?
If anyone can please help I would appreciate it so much.
Rob
View 3 Replies
View Related
Sep 14, 2004
My application extracts data from a SQL server according to some filters and put it in a local database (MS Access), I actually do it in two steps, data extraction and loop tru the data sending it to MS Access with insert values instruction, is there a better way to do it (the routine is too slow for large databases, the string convertion make it slow I beliebe)?, I was thinking on something like "Select into", but I am not copying a database and I need to add 2 more columns to the database (add fields that you have at the moment to make the select but aren't on the database and were needed to make the selection).
Tanks
View 1 Replies
View Related
Feb 6, 2013
I am trying to import my data from a csv file, but I don't need all the data and the format of the csv is not very straightforward.
For some background information, the csv file contains bucket weights from a mining loader which are recorded with the corresponding timestamp, which is in a binary format. The file also contains a marker to tell me which loading unit it came from as the name of the file is the same regardless of which machine it comes from. The format of the file is out of my control. The file is a standard weekly download and I would expect to see the same type of file from at least another 5 machines.
If you look at the attached example csv in Excel, I need to pull out the information in columns B and C but only from row 29 down. The rest of the information above this is machine information only. What I also need to do is associate these records so I know that they came from the unit number which is shown in cell B5.
Also, how can I then convert the binary time to standard date/time format? This is the formula I use through Excel to convert it "= ((LEFT (D29, 10)/86400) +25569+8/24)"
My end result is to be able to run a report which shows the bucket weights for a particular unit in a user selectable time frame.
View 1 Replies
View Related
Jan 23, 2014
I have been tasked with creating a databse that contains multiple test records. Within a single test record, there is a field named 'bits' in which there are 10 different bits (0 - 9) each with a different value and description. I would like to set up the database such that a user can view a single test record and select a particular bit field from a drop down box and when the bit is selected, the descriptive information for that particular bit is automatically displayed to the user.
For example:
- User selects 'test record A'.
- Within 'test record A' there is a 'bits' dropdown box.
- From the 'bits' dropdown box, the user selects bit '0'.
- When bit '0' is selected, another field named 'bit description' automatically fills in the bit description for bit '0'.
View 1 Replies
View Related
Sep 23, 2014
My Membership Database includes 3 fields in the main Table which I need to clear prior to the new subscription year, but on a selective basis.The fields are R/N for (Renewal or New), a subscription date and an amount field (Currency).
At the due date I need to clear all 3 fields except where N/R=N AND date >01/01/2014, when data in all 3 fields is to be retained.I have tried several SQL codings to achieve this but have been unsuccessful so far.
View 6 Replies
View Related
May 24, 2006
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 Related
Jul 26, 2007
I 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
View 9 Replies
View Related
May 20, 2005
First off I apoligise if this is a clear answer. I have looked on the internet for the last two days and can't seem to find this anywhere, either that or I am just entereing the search parameters in wrong :confused:
I have filtered selection in a form which I want to export to excel.
Simple enough ;) : Created a macro with the export to command. This dus everything I want to do.
Well not quite. :rolleyes:
How do I select the fields I want to export.Something like Select Id, name, adres from query soandso
Hopefully there is a simple solution to this. If there any existing posts. Could you post the link for me.
Thank your for your time,
Kind regards,
View 14 Replies
View Related
Feb 16, 2015
I have over 100 queries of weather data, for each month, and would like to display certain or filtered information for a selected month.
I have been playing around with a combo box to select a month from a given year, and was hoping that maybe an 'After Update' would do what I want. The problem I have is that I don't know how to do this, as the month picked and displayed in the combo box is variable , and so a sort of wildcard might do the job, if I knew how.
View 14 Replies
View Related
Sep 9, 2014
I am working on a project where I need to upload selected data from multiple sheets of an excel file. Here is an example of what I want.
1. I want to create a table in Access with around 10 columns
2. Column 1 should be populated with the date field found in A2 cell of sheet 1 of the excel file
3. Column 2-5 should be populated with the columns B2-E200 in sheet 2 of the excel file.
4. Columns 6-7 would be populated based on values from columns 1-2 of the table. Basically Column 6 should be Column 1 date plus 60 days.
5. Column 8-10 would be user generated after the excel is imported and the user should have the ability to attach around 5 files to each row.
View 5 Replies
View Related
Apr 24, 2013
I have a main form (frmMain) and a sub form (frmChild), set as datasheet. The first field in the datasheet is set as a hyperlink and opens another form correctly. What I would like is if the user selects a record on frmChild, then a textbox on frmMain is populated by the corresponding value in the second column of the datasheet. Is it possible to do this via vba, and if so, how? I've read about solutions by using continous forms, but I'd like to stay with datasheet format if possible.
View 2 Replies
View Related
Feb 14, 2014
I've been working on creating a database for a Physical Therapist that will store patients information and information on past appointments (including the amount of money paid per appointment). I created a form that he can then easily select the patient and their D.O.B. and insurance carrier will show up.
But I need to create a way that when he then selects the treatment received for the appointment that the correct payment amount will show up and be recorded as well. The reason this is an issue is because for each insurance there is a different cost per treatment.
View 3 Replies
View Related
Jan 16, 2007
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.
View 3 Replies
View Related
Mar 27, 2006
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.
View 3 Replies
View Related
Jun 24, 2006
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 Related
Mar 4, 2014
I 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 Related
Jun 15, 2015
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?
View 2 Replies
View Related
Jan 13, 2015
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.
View 2 Replies
View Related