Add one new record to query (1 Viewer)

[jaryszek]

Hi,

i have query where i am sorting data.
And i want to add new "artificial" record within second query and into field "DataText" add "Tab local definition".

How can i do this in Access?

Thank you,
Best,
Jacek

 

[Gasman]

Union query?

 

[jaryszek]

Ok how to add to second artificial query only one record with custom data? (and saved the same fields)?

Jacek

 

[Uncle Gizmo]

I'd say Union Query, same as Gasman...

Maybe you need to provide more information...

Sent from my SM-G925F using Tapatalk

 

[jaryszek]

If i want to add custom record on the fly i think i have to create new table with this record and use union query.

First table is sorted, with added custom record not this is why i want to use union query here...

I thought that maybe there is some other solution like write query to create the same fields and fullfill with custom record and after that us union query...

Best,
Jacek

 

[Gasman]

I believe the first Select could be sorted as you wish and then just the UNION to add the custom record.?

 

[jaryszek]

how can i use just the join?

In first table tblPeople i have assume 1 field : PersonName
I have Paul and Jacek there sorted by PersonName.

And i want to add into query PersonName "Undefined" and union this to tblPeople (not add Undefined to tblPeople, just add artificial record within query).

It is possible?

Best,
Jacek

 

[Gasman]

SELECT PersonName FROM tblPeople ORDER BY PersonName
UNION
SELECT "Undefined" AS PersonName FROM tblPeople

If you need the Undefined last, add a sort key, eg

SELECT 0 AS SortKey, Lookups.Data
FROM Lookups
ORDER BY Lookups.Data;
UNION
SELECT 1 AS SortKey, "Aardvark" AS Data FROM Lookups

HTH

 

[Pat Hartman]

Not sure what purpose adding this dummy record serves but keep in mind that Union queries are not updateable.

 

[jaryszek]

Gasman thank you very much!!

Have a nice day!
Jacek

 

[jaryszek]

One more question:

if i will do:

SELECT 1 AS SortKey, "Aardvark" AS Data FROM Lookups
UNION
SELECT 0 AS SortKey, Lookups.Data
FROM Lookups
ORDER BY Lookups.Data;

i will have 1 and 0 in this order?

Jacek

 

[Minty]

I think you'll need to order by sort key, not Data, if you want 1 then 0 ...

But you might need to make the union a sub query to achieve that. Aircode!

Select * from
  (SELECT 0 AS SortKey, Lookups.Data
  FROM Lookups
  ORDER BY Lookups.Data;
  UNION
  SELECT 1 AS SortKey, "Aardvark" AS Data FROM Lookups)
Order By SortKey

 

[jaryszek]

thank you Minty,

i should add descending or ascending to your code.

the same is when i want to 0, then 1 , yes?

Jacek

 

[Minty]

Default ordering property is ASC , so you only really need apply DESC when you need it.

 

[jaryszek]

Select * from
(SELECT 0 AS SortKey, Lookups.Data
FROM Lookups
ORDER BY Lookups.Data;
UNION
SELECT 1 AS SortKey, "Aardvark" AS Data FROM Lookups)
Order By SortKey

ok so here if you will skip the Order by clause you will get 0 first and 1 as second because this is a order of query?

And only if you want to get first 1 and second 0 you can : change select order in above query,
add order by clause.

It is correct?
Jacek

 

[Minty]

You have two sorts: one on your original data, I am guessing to fill a combo in alphabetical order, the second sort takes the first data + your "Aardvark" made up line and sorts by the new sort order field.

So yes you would add the sort order to final sort to affect the outcome as you want.

 

[jaryszek]

ok but union query also is ordering queires one by one?

Jacek

 

[Minty]

I believe that within a union query each section can be ordered, but can't test that theory at the moment.

What results are you getting ? Try it ?

 

[Gasman]

I believe that within a union query each section can be ordered, but can't test that theory at the moment.

What results are you getting ? Try it ?

I have tried, but it appears the last takes precedence?

 

[The_Doc_Man]

Are you perhaps trying to create some sort of header? Because otherwise, I would just do an INSERT INTO query for the record, do it one time, and be done with it. Then if there are times when you don't want to see that record, include a flag in it that says "I built this" and leave the flag FALSE in all natural records. To assure the order, never access it as a table but ALWAYS as a query so you can impose an ORDER BY on it. Then make its record ID the first record using that order.

You are using the UNION to merge two things of the same structure. I would use a query with a WHERE clause to separate two things that are normally kept in the same place. You only need two queries- but that's OK because queries are cheap. One query that has the ORDER BY, the other that has the ORDER BY and a WHERE CREATEDFLAG = TRUE.