SQL Query Help Please

[Winston409]

Hi all,

I am new to MS Access, I did an online curse, but still struggle to use Access. I am attempting to create a database for all the equipments I work with, my company use a web based system, but I find that it misses an awful lot of data, hence, for my own piece of mind, I want to create my own system to keep up to date with servicing.

I created the following in SQL:
SELECT
tblEquipmentInformation.[EquipmentLocation],
tblEquipmentInformation.[EquipmentModel],
tblEquipmentInformation.[EquipmentSerialNumber],
tblEquipmentInformation.[DateLastServiced],
tblEquipmentInformation.[ServiceInterval],
tblEquipmentInformation.[NextServiceDue],
tblEquipmentInformation.Modality
FROM
tblEquipmentInformation
WHERE
((tblEquipmentInformation.Modality) = "X-Ray")
SELECT
tblEquipmentInformation.[Modality] = "X-Ray",
[182],
[DateLastServiced]
DateAdd("d",[ServiceInterval],[DateLastServiced])
AS
tblEquipmentInformation.[NextServiceDue];

However, this pops up, any ideas, please?? I appreciate it may be something simple, but I cannot seem to find the issue.

Many many thanks in advance.

Winston

 

[Gasman]

Two selects, no union?
No comma after DateLastServiced in second select.
Why the second select?

 

[Winston409]

I am trying to create a next service due column, which will look at x-ray equipment and add 182 days to the date last serviced, and then put the result in the next service due.cheers.

 

[Gasman]

Please post code within code tags. the </> icon.

SELECT
tblEquipmentInformation.[EquipmentLocation],
tblEquipmentInformation.[EquipmentModel],
tblEquipmentInformation.[EquipmentSerialNumber],
tblEquipmentInformation.[DateLastServiced],
tblEquipmentInformation.[ServiceInterval],
tblEquipmentInformation.[NextServiceDue],
tblEquipmentInformation.Modality,
DateAdd("d",[ServiceInterval],[DateLastServiced])
AS [NextServiceDueCalc]
FROM
tblEquipmentInformation
WHERE
((tblEquipmentInformation.Modality) = "X-Ray")

That will just calculate the NextServiceDate, not populate any field called that in your table. If you can calculate it, generally, you do not store it.

You would update when an item was serviced and calc again for the next due date.

 

[Winston409]

Two selects, no union?
No comma after DateLastServiced in second select.
Why the second select?

Thank you.

 

[Winston409]

Please post code within code tags. the </> icon.

SELECT
tblEquipmentInformation.[EquipmentLocation],
tblEquipmentInformation.[EquipmentModel],
tblEquipmentInformation.[EquipmentSerialNumber],
tblEquipmentInformation.[DateLastServiced],
tblEquipmentInformation.[ServiceInterval],
tblEquipmentInformation.[NextServiceDue],
tblEquipmentInformation.Modality,
DateAdd("d",[ServiceInterval],[DateLastServiced])
AS [NextServiceDueCalc]
FROM
tblEquipmentInformation
WHERE
((tblEquipmentInformation.Modality) = "X-Ray")

That will just calculate the NextServiceDate, not populate any field called that in your table. If you can calculate it, generally, you do not store it.

You would update when an item was serviced and calc again for the next due date.

I cannot tell you how long its taken me to try and figure it out. you are a star Gasman, thank you so much.

 

[Gasman]

I use the QBE/design window as much as I can.
Youtube has some help as well, if you search for it.

https://www.youtube.com/results?search_query=sql+tutorial+access

 

[The_Doc_Man]

For future reference and to save you some typing.

Your initial SELECT could be written as

SELECT
EquipmentLocation,
EquipmentModel,
EquipmentSerialNumber,
DateLastServiced,
ServiceInterval,
NextServiceDue,
Modality,
DateAdd( "d",ServiceInterval, DateLastServiced ) AS NextServiceDueCalc
FROM
tblEquipmentInformation
WHERE
((Modality) = "X-Ray")

You can do without prefixing "tblEquipmentInformation" for every field because your FROM clause has only data source and every field comes from that. (This statement would no longer be true if you had a multi-table JOIN query.) You can do without the brackets because none of your fields contains a space or special character.

This is NOT meant to interfere with any of the replies from my colleagues.

 

[Isaac]

Definitely use the dialogue window to create queries at the beginning (and maybe indefinitely), don't try to just type it out in SQL

 

[isladogs]

Love the typo in post #1. An 'online curse'.