IIF and WHERE from Tables without values (1 Viewer)

D_Gambino

New member
Local time
Today, 08:38
Joined
Mar 14, 2018
Messages
5
Hi all,
I think i have a sort of unique situation and am facing a possible logic issue.

Back-Story:
I can qry several sets of tables needed and apply to my access db.
the 1st table, lists the serial number (Service Tag) of a computer as well as all of the units components by part_number and Description.

The 2nd table lists a set of part numbers, and the Demand of those part numbers by our clients.
(Exe. part_number = ABC12 Demand = 50)

the 3rd table, lists all the validated substitutions for part number A, if it is out of stock.
(Exe. From_Part_Number = ABC12 To_Part_Number = CBA12)

Currently i can get Access to tell me which parts are in the unit, that are also in the Demand File, but if the part number is not in the demand file (or is at zero pcs needed, I need it to look to see if there is a valid substitute part, and check its demand.

This is what compares the unit to current demand.
I can provide more info if this is not enough

SELECT
BOM_File.LICENSE_PLATE,
BOM_File.PART_NUMBER,
BOM_File.DESCRIPTION,
Current_Demand.Actual_Demand
FROM BOM_File, Current_Demand
Where BOM_File.Part_Number = Current_Demand.DTSO_Part_Num
And Current_Demand.Actual_Demand > 0
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Jan 23, 2006
Messages
15,362
You can post a copy of your database even if you have less than 10 posts if you use ZIP format.
 

A S MANN

New member
Local time
Today, 19:08
Joined
Oct 18, 2017
Messages
9
what exactly is your question? sorry i could not make out what you want.
 

D_Gambino

New member
Local time
Today, 08:38
Joined
Mar 14, 2018
Messages
5
Attempted to but its 7+mb, and the Forum limits me to 2MB


Ignore my ignorance, removed some of the table data to shrink down.
1 zip has the access db, the other has the excel files that are linked in it.

Attached...
 

Attachments

  • IS_Demand2.zip
    269.9 KB · Views: 60
  • IS_Demand_DB - Copy.zip
    339.6 KB · Views: 69
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Jan 23, 2006
Messages
15,362
Your database has links to excel files which are not included???
 

A S MANN

New member
Local time
Today, 19:08
Joined
Oct 18, 2017
Messages
9
Your query are still linked to old xls table so do not run. since you have a working db so what is missing in it. Your question is highly unclear?
 

isladogs

MVP / VIP
Local time
Today, 13:38
Joined
Jan 14, 2017
Messages
18,186
One of your Excel links was wrong. I deleted them all and re-created links to the Excel files supplied

Two of your queries are badly constructed with a cartesian join (i.e. no join) but you have specified two fields as equal in each case

Instead link the two tables using those fields:

a) Q3_Current_Subs
Code:
SELECT E2_Subs.From_Part, E2_Subs.To_Part, Current_Demand.Actual_Demand INTO Current_Subs
FROM E2_Subs INNER JOIN Current_Demand ON E2_Subs.From_Part = Current_Demand.DTSO_PART_NUM;

b) Query1
Code:
SELECT BOM_File.LICENSE_PLATE, BOM_File.PART_NUMBER, BOM_File.DESCRIPTION, Current_Demand.Actual_Demand
FROM Current_Demand INNER JOIN BOM_File ON Current_Demand.DTSO_PART_NUM = BOM_File.PART_NUMBER
WHERE (((Current_Demand.Actual_Demand)>0));

None of your tables had a primary key field - ALWAYS do so!
I've added this in the attached

For the first part of your question, I created an unmatched query to get all part numbers not in Current_Demand table
c) qryPartsNotInCurrentDemand
Code:
SELECT Parts_In_Units.Part_Number, Parts_In_Units.Count, Parts_In_Units.Description
FROM Parts_In_Units LEFT JOIN Current_Demand ON Parts_In_Units.Part_Number = Current_Demand.DTSO_PART_NUM
WHERE (((Current_Demand.DTSO_PART_NUM) Is Null))
ORDER BY Parts_In_Units.Part_Number;

For the second part of your question, I tried to link that query to Current_Subs but there are no matching records

d) qryReplacementPartsNotInCurrentDemand
Code:
SELECT Parts_In_Units.Part_Number, Current_Subs.To_Part AS ReplacementPart, Parts_In_Units.Count, Parts_In_Units.Description
FROM Current_Subs INNER JOIN (Parts_In_Units LEFT JOIN Current_Demand ON Parts_In_Units.Part_Number = Current_Demand.DTSO_PART_NUM) ON Current_Subs.From_Part = Parts_In_Units.Part_Number
WHERE (((Current_Demand.DTSO_PART_NUM) Is Null))
ORDER BY Parts_In_Units.Part_Number;

It may be that I have misunderstood what you wanted - as AS Mann has already said, your first post was not at all clear
 

Attachments

  • IS_Demand2-CR.zip
    507.9 KB · Views: 79

Users who are viewing this thread

Top Bottom