General :: Priority Based Multiple Order Shortage List

Jan 21, 2013

I have created an access application for production system .

I have a final stock query and 2 to 3 orders based on priority and i want to create a shortage list based on priority ... here is detail :

ItemName....|Stock qty....|ORDER1....|ORDER2....|ORDER3....|
A.................|200............|40............. |90............|100...........|
B.................|500............|0.............. .|400..........|200...........|
C.................|30..............|40............ .|0..............|20............|
D.................|0...............|0............. ..|10............|0...............|
E.................|900............|400...........| 300..........|280...........|
F.................|130............|200...........| 50............|0...............|

Now I want shortage as:

ITEM NAME |O1 SHORTAGE | O2 SHORTAGE | O3 SHORTAGE|
A................|00..................|00......... ..........|30..................|
B................|00..................|00......... ..........|100.................|
C................|10..................|00......... ..........|20...................|
D................|00..................|10......... ..........|00...................|
E................|00..................|00......... ..........|80...................|
F................|70..................|50......... ...........|00...................|

What method Do I use to write a query as I have hundreds of Products and 40-50 orders????????

View Replies


ADVERTISEMENT

Queries :: Order Shortage Based On Multiple Stages?

Aug 17, 2015

I have a production database in which orders are checked for shortages at different levels of production in which :

1 raw product can be fetched in 2 or more final products, Shortages are checked as per priority.

In this database i have tables named "finalproductlist" and "rawproductslist" in which products are connected in one to many (see table "productmapping"). For E.g. 1 product in raw table can be fetched into 2 or more final products.

I have stocks query on both levels with which shortage is checked.

I want to create the order shortage query which gives the shortage at both levels prioritywise.

View 14 Replies View Related

General :: First In First Out Order Shortage

Mar 25, 2013

I need to design a query in which order shortage is calculated with respect to fifo method for orders .

Attached is the logic

How to create it. and if i need to incorporate more than 10 orders , what is the best method for this ?

View 3 Replies View Related

General :: Adding Priority List To Database

Feb 20, 2013

I have been asked if I can add a priority list to my database and I am trying to figure out how to do it.

The database already exists and this would be added code/forms. Basically this database has a number of users, all identified by a login name. Each of these users have several projects assigned to them. The projects are identified by a unique project number.

What I need to do is to be able to list all the projects that a user has, which is easy to do by simply creating a query, but then the user needs to be able to type in a priority number (1 to infinity) for each of these projects with no number repetitions. If a project priority is updated, say from 6 to 2, then the projects that had priorities 2 to 5, get a +1 added to their priority number so they go 3 to 6. Likewise if a project gets "demoted" (priority is lowered) then the ones below go up one.

View 1 Replies View Related

General :: Access Query To Filter Out List Based On Multiple Row Criteria From Another Table

May 10, 2014

I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.

I want to create a query to filter websites list which does not have values or characters from table b.

I have these values in table B that I want to be filtered out or not shown in my URL Select Query

.org
.gov
.du
.pk
.dk

I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.

SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));

View 3 Replies View Related

General :: How To Order Values In Multi-Select List Box

Jun 4, 2013

Is there a way to order the values in a multi-select list box:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 appears in the listbox as:

1,10,11,12,13,14,15,16,17,18,19,20,2,3,4,5,6,7,8,9 . Is there a way to have it appear as it does originally?

View 1 Replies View Related

General :: Pull Data From SQL Server Based Off Of Order Number?

May 27, 2015

I am looking into re-creating something I have created in Microsoft Excel into Microsoft Access.What I do in the Excel spreadsheet, is when I type in an order number, various fields in the spreadsheet are populated using vlookups and ODBC's.

I wish to re-create this in Access but not sure how. I have a basic form, with various fields such as, order number, part number, description, quantity.What I have in my mind is that I would like to be able to type in an order number, then the rest of the fields would be populated by a query to a SQL Server database.i can see a way to do this using VBA but would rather look for a more direct way if possible.

View 2 Replies View Related

Multiple Combo / List Boxes - Sort Based On Entries

Sep 23, 2013

I am trying to put together a form where employees will be entering maintenance information into my database. One of the requirements for this data is that when they perform repair work on an asset, they have to list a problem, cause and remedy. These are codes that are specific to each asset.

The closest thing I can think of to what I am trying to acheive is the selectors on car search websites. You select a MAKE, and then the MODEL list is narrowed down based on that, then you pick the TRIM, which is narrowed down based on the model selection. I have an Excel spreadsheet of the Assets(Failure Class), Problems, Causes, and Remedies...I'm just not sure how to put this into Access to get the desired output. I have attached a sample of the Excel spreadsheet for illustration.

I believe I'm going to have to put each of the columns in a seperate table and use relationships..but I'm not sure about that.

View 2 Replies View Related

General :: Determining The Values In One List Based On Another

Jun 27, 2012

I have a table with a few Listbox options. I want one of the columns' values to be determined by the value chosen in another field. How do I do this?

Example: In column A I choose value ABC from the dropdown. I want column B to now offer values in that list of DEF, GHI, JKL. If I chose XYZ in column A instead, I'd want the column B list to show MNO, PQR, STU. Etc.

View 6 Replies View Related

Access Query To Filter Out List Based On Multiple Row Criteria From Another Table

May 10, 2014

I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.

I want to create a query to filter websites list which does not have values or characters from table b.

I have these values in table B that I want to be filtered out or not shown in my URL Select Query

.org
.gov
.du
.pk
.dk

I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.

Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query

SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));

View 9 Replies View Related

General :: How To Search A Table Based On A List Of Keyterms

Apr 7, 2014

I have a master data table containing 4 columns and ~ 500K rows. I have a list of keyterms from column1, column2 and column3. How do I use Access to search the masta data table and return the corresponding column4 value for each row on the list.

View 6 Replies View Related

General :: Multiple Selections In List Box

Dec 4, 2012

Basically I have a list box and I want to be able to pick several items from it (No relation except for the name in the Inital Assigned Field) then hit a button and then it opens another form and shows all the records selected. I want to this to see several records at once but also change the value of all the fields to the same value.

View 1 Replies View Related

General :: WHERE Statement - List Box To Be Populated Based On Three Different Criteria In Table

Sep 14, 2013

I have an AfterUpdate event where I want a list box to be populated based on three different criteria based on a table in my database

1. Complete = False AND
2. Supplier on form = supplier in table AND
3. Status in table = "SUPPLIER_RFQ FOLLOW-UP" OR "SUPPLIER_RE-RFQ TO OTHER SUPPLIER"

I am having trouble with the last OR statement criteria, i cannot get this to return values correctly. Here is my code:

Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool].[RFQ Contact] " & _
"FROM Consolidated_Master_Req_Pool " & _
"WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool].[RFQ Supplier] = '" & Nz(Me.cboStatusRFQ) & "' And [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RFQ FOLLOW-UP" & "' OR [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RE-RFQ OTHER SUPPLIER" & "'" & _
"ORDER BY [Consolidated_Master_Req_Pool].[RFQ Contact];"

View 2 Replies View Related

General :: Send Outlook Email Based On List Of Emails In Access Query?

Sep 15, 2014

I've setup a selection form that returns a specific list of email addresses in a query.

What I can't work out is how to press a button and have the email addresses populate the To: field in a blank Outlook email - I don't need any bells or whistles.

View 8 Replies View Related

General :: Report To Multiple Text Files Based On Filtered Value

Jun 14, 2014

I created report "rpt" with a few subrpts. I would like to export this report to word by value in column M_AGENDA_KOD.

M_AGENDA_KOD column has about 370 rows, so i do not want to export each manually.

So I would like to have each M_AGENDA_KOD value as separated word file named by that M_AGENDA_KOD value.
Is that even possible?

View 14 Replies View Related

General :: Search Records Based On Multiple Check Boxes?

Jan 3, 2013

I have a table of data regarding companies (contact info, etc). The company I work for provides these companies with up to 10 different products. On my input form, I have created 10 check boxes (and thus, 10 columns of Yes/No data in the corresponding table).

Each company has at least 1 product checked off, and up to all 10.

I would like to create a combo box that lists all 10 products, and upon selecting one, a list box then populates with the names of the companies (primary key) that use that specific product.

So, for example,

Company A buys CATS and DOGS from us
Company B buys DOGS
Company C buys CATS and ELEPHANTS
Combo box options: CATS, DOGS, ELEPHANTS

If I choose DOGS, then a list box gets populated with:

Company A
Company B

and when I click either of these, the record should be brought up for the respective company.

All of this should take place in the header of the form, while the form itself can be updated based on search selections.

View 3 Replies View Related

General :: Make A Field Based On Data From Multiple Other Fields

Feb 19, 2013

Let's say I have a table called "Courses" which among other things has "Completed"(Yes/No) and "Category"(Text).

Now, there also another table "Progress" which has similar columns.

I want Progress.Completed to reflect if all courses of the specified category have been completed.

How would I do that? I'm new to access ...

View 1 Replies View Related

General :: Update Table Based On Form - Create Multiple Rows

Apr 15, 2014

I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.

I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.

View 3 Replies View Related

Query To List Multiple Records In A List From Multiple Queries

Jul 11, 2013

I have a DB where you there's 5 tables all linked together by one project ID

tables below

Project , Staff, Asset, allowances, travel, mark up

What I can do is create a new project, then add records to each of the other tables on what different items I require,

i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)

Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.

At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.

View 7 Replies View Related

Queries :: Creating A List Based On Multiple Queries

Jun 23, 2015

I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error

ODBC-- call failed ODBC Driver SQLBase.....

Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.

Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.

View 2 Replies View Related

List Box Alphabetical Order

Sep 26, 2005

Hi can anybody tell me how i can make a list box alphabetical and it updates as new records are added

Thanks

View 3 Replies View Related

Sort Order Of Unbound List

Sep 15, 2006

I need to manage a list of, in this case "medications", with this list the most common "meds" are on the top of the list. there will be at times new "meds" added that should be at the top of the list. I want to create a unbound box that will let me drag the "meds" up or down in the list. The best emample i know of is the way you can set the "Tab" fields on a form to move the tabs stops.

View 4 Replies View Related

Modules & VBA :: Run Validation In Order In Unordered List

Jan 12, 2014

How to define Order that starts with a dependency on the existing value.

Basically, a function that chooses what Status Validation to run then knows when to stop that displays some very simple predictive Inference.

Each Record has a Status. The Status will generally be correct 85% of the time. Once in a while, various things in the database are updated that triggers the business need to change the status.

-A collection of Rule based validation functions matches each Status.
-The objective is not to run Rule 1 through 40 sequentially for each record.
-Each Rule Validation function is lengthy and can take 0.1 seconds per rule (per record).
-If the current record's rule is still valid 85% of the time, Then run that Status Rule Validation function first.
-If the Validation returns True, there is no need to run the rest of the rules. Move to the next record.

The challenge: Statistically, if the Current Status failes its Rule Validation, there is a high probability that one of the Status Validation Function near to the current Status will validate to true.

Like the board game Clue, once a Validation Rule comes out False, there is no need to check it for that single record again.

In an extremely simplistic world, think about Status of : Order in Progress, Order Completed, Completed Order Paid, Shipping in Progress, Item Shipped Ready, Shipped, In Transit, Delivered, Rejected, Payment Recended, ...

So, if the current status was In Transit, there is a higher odd that Delivered would be the next. There would be no need to start from the beginning again. The wonderful world of Metadata.

View 2 Replies View Related

How To Find A Replica's Priority

Apr 21, 2006

I know how to setup a Replica's Priority, but where do I go if I want to change it afterwards? I have searched everywhere, or am I just supposed to create another replica and set it then?

View 2 Replies View Related

Priority Ranking Adjustments

Feb 28, 2008

I found this from different fourm by searching google. This is exactly what I need to do and the answer was given; however, I can't figure it out. Can someone please post a step by step instruction on how to complete the following.

Thank you!


Question

I have a datbase full of projects that must be ranked in priority order. This was simple enough but when I add a project I will likely need to change the priority rank of all the "lesser" projects. With over 500 projects adding a new project with a rank of 50 means changing all the ranks 50 and above. I want to be able to add a project and when I determine its rank I insert the rank number and every rank equal to the project just added is the indexed by 1 digit. No two project can be ranks the same, one is always more important than another.


Answer

Create an update query that sets the priority field to:

[Priority] + 1

then set the criteria to

>= new number

Where new number is the rank of the new number.

This will increment by one each priority.

Hope this helps,
Scott<>

View 5 Replies View Related

Modules & VBA :: Combo Box Will Not Display List In Correct Order

Nov 3, 2014

I have code attached to a command button to fill a Combo Box with data from a music collection. A letter of the alphabet is entered into a Text Box then records beginning with that letter are copied from a table, either by Artist or Title. They are saved to a temporary table at which time they are in no particular order. Those records are copied to a further table and saved in alphabetical order. This table is then used to fill the Combo Box.

I used two temporary tables because the records were not displayed in the correct order. I hoped this might cure it, it did not. The records are in order in the table but not in the Combo Box.

Code:
Private Sub Command68_Click()
'SEARCH AND FILL COMBO BOX
On Error GoTo errTrap
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tempList;"

[Code] .....

View 3 Replies View Related







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