Using date to establish which column data goes in

[chrisjames25]

Hi.

Literally no clue where to start with this query so please bare with me.

Lets say im in farming and i grow the following crops:

potatos
tomatos
peas
beans

When i sow the above crops, i record on database the date the seeds were sown, what was sown, how many were sown and how long it will be till ready.

What i am after is a query that will create a table to summarise the data for each crop, so it will tell me that form 3 batches of potato i created 3000 potatos if each batch i sowed was 100 of each.

Hear is the kicker. I want the followng headings:

Product Number Available Now Number Still Growing Total Number


So lets say my three batches were all potted as follows:

POtato batch 1 - potted 01/04/18 - 4 weeks till available
Potato batch 2 - potted 15/04/18 - 4 weeks till available
Potato batch 3 - potted 30/04/18 - 4 weeks till available

The data i want presented is

Product Name: Potato
Available: 1000
Growing: 2000
Total Stock: 3000

Hope that is explained enough, and again apologies for no clue on how to tackle this one.

CHeers

 

[June7]

Provide sample data. Can copy/paste Access table into post.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

 

[chrisjames25]

Hi

PLease find example database. With basic data in table1 and i have created a query (query1) showing the heading results i am after but i am struggling to achieve the result i want under the available and growing heading.

 

[JHB]

Run the "Status" query, is that what you're looking for?

 

[chrisjames25]

its kind of what im looking for just not presented how i want. Rather than it have a status as field header i want the field header to say Growing. Another field header to say Available and another field header to say TOtal.

Can that be achieved?

 

[JHB]

Okay, the put the below into a new query:

SELECT Table1.ProductName, Sum(IIf([Date Available]>Date(),[QtyPotted])) AS Growing, Sum(IIf([Date Available]<=Date(),[QtyPotted])) AS Available, Sum(Table1.QtyPotted) AS Total
FROM Table1
GROUP BY Table1.ProductName;

 

[chrisjames25]

THat almost worked.

Problem with that one is that it did everything right apart fomr the Available column.

IN the growing column it say 2000 growing which is correct. In total column it says 3000 total which is correct but in available column it says 2000 available where it should be 1000 available.

 

[chrisjames25]

Sorted it just made one change

SELECT Table1.ProductName, Sum(IIf([Date Available]>Date(),[QtyPotted])) AS Growing, Sum(IIf([Date Available]<Date(),[QtyPotted])) AS Available, Sum(Table1.QtyPotted) AS Total
FROM Table1
GROUP BY Table1.ProductName;

 

[chrisjames25]

Last, question, hopefully.

HOw can i adapt your if statement so that if it is null in an expression for gorwing or available it puts value 0 in rather than a blank.

 

[chrisjames25]

Sorted last question myself.

THanks for all your help

 

[chrisjames25]

SELECT Table1.ProductName, Sum(IIf([Date Available]>Date(),[QtyPotted],0)) AS Growing, Sum(IIf([Date Available]<Date(),[QtyPotted],0)) AS Available, Sum(Table1.QtyPotted) AS Total
FROM Table1
GROUP BY Table1.ProductName;

 

[chrisjames25]

Hi.

Follow up to this. If i had a second table that was my sales invoices and then wanted to amend my query so it showed total plants gorwing / available and sold how could that be achieved.

For example 2 batches of potato, 500 each. 1 is available 1 is still growing and i have sold 300 potatos already.

When i try to do this is the example database attached it seems to mess up my data.

 

[JHB]

Run query 3.

 

[chrisjames25]

Hi JHB\

THnaks for your continued help on this one, and we are getting so close to result required.

I have posted new database. Run query 3 and that is what i am looking for.

However there is one issue. At the moment say i have stock of potato of 1000 and 500 is currently available and 500 still growing and an order for 600 comes in.

At times we will go to the growing patch and sell those that are closest to being available. HOwever at present the result i would currently get is Sold 600, growing 500 and available -100.

Is there anyway to tell it that if sold is greater than available then make available 0 and then minus the difference off growing?

Cucumber highlights this problem in the database example. (actually perhaps it was potato that does)

 

[JHB]

..
I have posted new database. Run query 3 and that is what i am looking for.
..

Hmm - can't find that query in the posted database.

 

[chrisjames25]

Apologies. Uploaded wrong database.

Now see correct one with query 3

 

[JHB]

Show how you want to see the result, (printscreen).
And if you've more requirements then show them all, and not one by one, it is time consuming.

 

[chrisjames25]

Hi JHB

Please see the final desired look.

Shown it in three steps. Step one more any sales made

Step 2 showing the negative i am trying to remove and step three is what i am trying to achieve

 

[Pat Hartman]

I did what I could but I have no clue how you really want to use this query.

 

[JHB]

chrisjames25, have you got what you where looking for?