How to automate pulling data by date

[Thedda]

I've inherited this database that is a nightmare the way it was structured. I have this query where I have to type in yesterdays date and it then populates the appropriate fields. Here is the SQL statement.

SELECT [SOLTEX_Daily_MTD/FGI].[PART NUMBER], [SOLTEX_Daily_MTD/FGI].SOL_PART_NUMBER, [SOLTEX_Daily_MTD/FGI].[5/06] AS QTY, [SOLTEX_Daily_MTD/FGI].COST, [Qty]*[COST] AS TOTAL, [UPDATE SOLTEX].Family INTO BILLINGS
FROM [SOLTEX_Daily_MTD/FGI] INNER JOIN [UPDATE SOLTEX] ON [SOLTEX_Daily_MTD/FGI].[PART NUMBER] = [UPDATE SOLTEX].[Assembly Name]
WHERE ((([SOLTEX_Daily_MTD/FGI].[5/06])<>0))
ORDER BY [UPDATE SOLTEX].Family;

I would like to automate this if possible. Right now I have to open the query in design and change the date for the Qty to next date which is always
date()-1.

The table has the dates as the column names. Appriciate any help....

 

[jdraw]

I've inherited this database that is a nightmare the way it was structured. I have this query where I have to type in yesterdays date and it then populates the appropriate fields. Here is the SQL statement.

SELECT [SOLTEX_Daily_MTD/FGI].[PART NUMBER], [SOLTEX_Daily_MTD/FGI].SOL_PART_NUMBER, [SOLTEX_Daily_MTD/FGI].[5/06] AS QTY, [SOLTEX_Daily_MTD/FGI].COST, [Qty]*[COST] AS TOTAL, [UPDATE SOLTEX].Family INTO BILLINGS
FROM [SOLTEX_Daily_MTD/FGI] INNER JOIN [UPDATE SOLTEX] ON [SOLTEX_Daily_MTD/FGI].[PART NUMBER] = [UPDATE SOLTEX].[Assembly Name]
WHERE ((([SOLTEX_Daily_MTD/FGI].[5/06])<>0))
ORDER BY [UPDATE SOLTEX].Family;

I would like to automate this if possible. Right now I have to open the query in design and change the date for the Qty to next date which is always
date()-1.

The table has the dates as the column names. Appriciate any help....

Some weird naming for sure. I don't see where you have to change Qty???
You could make it a parameter query and use criteria like [Enter Qty Date] or set it in the where clause a s Date()-1 as you have identified.