How to modify a saved query by way of a macro?

[gkick]

Hi,

Win10, LO6462, HSQL 2.51

I have a table containing a number of fields like id, date, mercedes, audi, vw and a query based on this table.

Now I want to add another field for bmw which I can do easily with running ALTER TABLE “xyz” ADD COLUMN “bmw” VARCHAR(12) from a macro using a variable.

How would I add the new field programmatically to the existing query with or without criteria and or sort order ? Is this possible ? If yes, would the coding be a combination of sql, basic and xml ?

Thanks for your thoughts

[frofa]

Curious as to why you need to add a new column (field) for each new car-maker. Surely you just need ONE column for recording car_maker and then populate it with the various car-maker names. It seems you are confusing the column name with the contents? I think you need to think carefully about your database DESIGN/SCHEMA. Your feedback most welcome.

[gkick]

@frofa G´day mate,

well just exploring design concepts, it could be some rental database where you start of with renting cars, later you also rent out your properties, later again your Cessna… in all cases you will need to change the structure of the database by adding new fields for scheduling purposes.
From a user perspective you just want to add a new Item to the items table where the item has to be a column and the row would be a rental date.
Hope this makes sense

[frofa]

Well, of course, for your above example, you could simply have 2 columns - one for rental_item and one for type (such as cars, properties, etc). If that were the core DESIGN of your database, you would NOT need to add new columns every time you add a new item. My previous comment does stand, I reckon.

[gkick]

Noops, think of a calendaring system where Items run horizontally(Columns) and dates booked run vertically(rows) so when there is a blank under item, the item is available for renting out.

[frofa]

I get your calendaring concept. To me, it seems like a Calc sheet application rather than a Base (SQL database) application. Here’s an EXAMPLE of a RENTAL ITEMS DB SCHEMA using Base. I guess the status field tells if a particular rental item is rented out (i.e. available) or not. It should be possible to get a calendar-style output (as you describe) from a properly setup SQL database.

[gkick]

opening a new thread as I can not add an image to a comment

[frofa]

See above 3 comments.