table with variables to be accessed in queries (1 Viewer)

Ports · Jan 3, 2020

Hi,

I have a table with a couple of variables. The table is structured as follows:

Code:

Period  |  YearCode |
-------------------------

R04     |  1920

The only thing that'll change here is me occassinally changing 'R04' and '1920' to some other values. I want to be able to change it in one place and it'll be reflected in the relevant queries.

I'd like to acces those two values in my queries. I've added the table to my design query window but cannot think of a way of adding the values. The values ('R04' and '1920') will be the same for all records across the board.

Actually, in the expression builder, I'm thinking of doing some conditional statement:

if (Period is blank), Period, do_nothing)

Essentially, I want to know which records were added in queries for let's say Period: R04. Then if there are new records in a query, they will be identified as R05 next month, etc.

Hope it makes sense.

The_Doc_Man · Jan 3, 2020

My question relates to this statement:

The values ('R04' and '1920') will be the same for all records across the board.

What is your end goal? This little snippet of some step you are trying to take doesn't help me to understand where you are going with this. My concern is that if something will be the same for all records, it doesn't belong in ANY of those records.

In a properly normalized database, every field in a record depends on that record's value of the primary key. Your statement says that these two things, whatever they are, will be pervasive but will not depend on the key. Therefore, it is clear to me that you are trying to use the fields in what we would consider as a non-standard situation. And the non-standard nature of the situation is what gives me pause.

I think we need to know more.

Ports · Jan 3, 2020

The_Doc_Man said:
My question relates to this statement:

What is your end goal? This little snippet of some step you are trying to take doesn't help me to understand where you are going with this. My concern is that if something will be the same for all records, it doesn't belong in ANY of those records.

In a properly normalized database, every field in a record depends on that record's value of the primary key. Your statement says that these two things, whatever they are, will be pervasive but will not depend on the key. Therefore, it is clear to me that you are trying to use the fields in what we would consider as a non-standard situation. And the non-standard nature of the situation is what gives me pause.

I think we need to know more.

Thank you for your reply. I realise it is non-standard as I couldn't find a reference on the net.

The two variables (Period and YearCode) are not part of the source tables but need to be present in the output queries. I just want to automate things. Previously what I'd do is to create one column in a query design window:

Period: "R04"

or

YearCod: "1920"

so it would be part of the queries that are to be exported to employees.

FYI, the input/source tables are excel files.

I'm not sure whether it clarifies things

plog · Jan 3, 2020

Please demonstrate this with data. Show us what data you have in your tables and what data you want the query to produce based on those table values.

Again, 2 sets of data:

A--Starting data from your tables. Include table and field names and enough data to cover all cases.

B--Expected results of A.

theDBguy · Jan 3, 2020

Ports said:
Thank you for your reply. I realise it is non-standard as I couldn't find a reference on the net.

The two variables (Period and YearCode) are not part of the source tables but need to be present in the output queries. I just want to automate things. Previously what I'd do is to create one column in a query design window:

Period: "R04"

or

YearCod: "1920"

so it would be part of the queries that are to be exported to employees.

FYI, the input/source tables are excel files.

I'm not sure whether it clarifies things

Hi. Sounds like you might be able to use a Cartesian Query, provided your new table only has one record in it.

vba_php · Jan 3, 2020

Ports,

Someone recently posted a question that's similar to yours I think. He had multiple spreadsheets, named tables in each one, and a list of project records, each one with a desired end date, budgeted amount for the project and location of the work were the construction people were at. He was trying to do the same thing (I think) that you're trying to do. What I'm think of was either this post or this one. I'm pretty sure what I'm thinking of is the 2nd link. Maybe that helps? If not, sorry. And regarding the term "variable". Using the term when talking about table "data" is a bit confusing. Variables are used in code.

Ports · Jan 3, 2020

plog said:
Please demonstrate this with data. Show us what data you have in your tables and what data you want the query to produce based on those table values.

Again, 2 sets of data:

A--Starting data from your tables. Include table and field names and enough data to cover all cases.

B--Expected results of A.

Apologies for not being clear. I've got the impression I'm making it sound more complicated than it really is.

Screenshot of sample data:
https://pasteboard.co/IOh9rb7.png

So each month I replace the source files. The person reading my output query wants to know which rows are new this month (eg. last month it was R04, so any new entries are R05 this month, and next month it'll be R06)

The Period and Year Code have no relationship with the source tables whatsoever, it's just I'm processing files from Period 5 so I'll newly added records as R05, etc.

Thank you

Ports · Jan 3, 2020

vba_php said:
Ports,

Someone recently posted a question that's similar to yours I think. He had multiple spreadsheets, named tables in each one, and a list of project records, each one with a desired end date, budgeted amount for the project and location of the work were the construction people were at. He was trying to do the same thing (I think) that you're trying to do. What I'm think of was either this post or this one. I'm pretty sure what I'm thinking of is the 2nd link. Maybe that helps? If not, sorry. And regarding the term "variable". Using the term when talking about table "data" is a bit confusing. Variables are used in code.

Thanks - I'll look at the links.

Wouldn't it be good to have a place where you can assign a few variables and have access to them from anywhere in the tables? haha - I know I'm missing the point of how a relational database system work..... and am probably approaching it from a wrong perspective.

vba_php · Jan 3, 2020

Ports said:
Wouldn't it be good to have a place where you can assign a few variables and have access to them from anywhere in the tables?

there was a question about *this* as well just yesterday or the day before. Generally I would say storing text in a table with the purpose of grabbing it and using it for actual VBA code is pretty out of the norm. However, the interesting thing about that concept is that, more than likely, when computers start writing application source code themselves without the need for human coders, a technique like that will probably be along the lines of what will be going on. Makes perfect sense. I've done it myself by producing PHP code and storing it line by line in an access table, only to grab it later and automatically generating a php script in the form of a .php file.

Ports said:
haha - I know I'm missing the point of how a relational database system work..... and am probably approaching it from a wrong perspective.

not really. many *many* people ask the same thing. i wouldn't worry about it.

theDBguy · Jan 3, 2020

Ports said:
Thanks - I'll look at the links.

Wouldn't it be good to have a place where you can assign a few variables and have access to them from anywhere in the tables? haha - I know I'm missing the point of how a relational database system work..... and am probably approaching it from a wrong perspective.

Hi. We do have something available for that, but it depends on what you're trying to achieve to see which ones work better for you.

When you say:

Ports said:
So each month I replace the source files. The person reading my output query wants to know which rows are new this month (eg. last month it was R04, so any new entries are R05 this month, and next month it'll be R06)

Are you saying you're saving previous month's data in a local Access table?

plog · Jan 3, 2020

Your sample data was lacking. You provided no table names and you only provided one table for starting data--what table do the Period and Year Code fields exist in?

2 sets of data please:

A--Starting data from your tables. Include table and field names and enough data to cover all cases.

B--Expected results of A.

Ports · Jan 3, 2020

theDBguy said:
Are you saying you're saving previous month's data in a local Access table?

No, but your question made me realise that I'd have to in order to keep track which records were there in previous periods. I'll have to create a make table query out of the query showing records for a particular period.

theDBguy · Jan 3, 2020

Ports said:
No, but your question made me realise that I'd have to in order to keep track which records were there in previous periods. I'll have to create a make table query out of the query showing records for a particular period.

Okay, if you go that route, using a Cartesian Query should work for you then. Let us know if you need a hand with that. Good luck!

Ports · Jan 3, 2020

plog said:
Your sample data was lacking. You provided no table names and you only provided one table for starting data--what table do the Period and Year Code fields exist in?

2 sets of data please:

A--Starting data from your tables. Include table and field names and enough data to cover all cases.

B--Expected results of A.

The Period and YearCode fields are in a table called 'tblVariables', which literally consists of the two fields and 1 record ("R04" and "1920" respectively)

The source table is tblSales (field names are in my screenshot)

Expected results are in my screenshot as Output Qry.

I'm not posting the real data in the screenshot but more data to cover all the cases follows the same pattern. Each month/period there will be more transactions recorded and the Period field should indicate in which period they first appeared in the source table so in the end it'll look like:

Code:

SalesID....... Period
001             R04
002            R04
003            R04
004           R05
005            R05
006           R05
007           R06
008          R06
009          R06

Period 4: 3 records/transactions
Period 5: additional 3 records/transactions
Period 6: additional 3 records/transactions

Agan the tblVariables is in no way related to tblSales by any field/relationship.

This month is Period 5 so while I'm processing the source files, I'm going to change the value of the field Period in the tblVariables to: R05. Now all new records/transactions that are new this month (were not present in the source/output file last month) should have the value R05 in the output query, etc.

Thank you

theDBguy · Jan 3, 2020

Ports said:
This month is Period 5 so while I'm processing the source files, I'm going to change the value of the field Period in the tblVariables to: R05. Now all new records/transactions that are new this month (were not present in the source/output file last month) should have the value R05 in the output query, etc.

Hi. Pardon, but I just had a thought. If you go the local table route to accumulate the data for all periods, you wouldn't even need to create and modify tblVariables (especially if you only need two values from it). You should be able to simply use a parameter query. For example:

Code:

INSERT INTO LocalTableName (SalesID, OtherFields, ..., Period, YearCode) 
SELECT SalesID, OtherFields, ..., [Enter Period], [Enter YearCode] FROM SourceTableName

Hope that helps/makes sense...

plog · Jan 3, 2020

Using the screenshot you provided and the description of tblVariable, this will produce the results you want:

Code:

SELECT SalesID, AMount, Period, YearCode
FROM tblSales, tblVariables

Cronk · Jan 3, 2020

The sample data provided with #7 and #14 shows the R value changes and presumably the same with the Year value. Consequently, this data should be stored in a separate table.

tblRYear
RYearID autonumber key
RValue text
YearValue numberic

tblSales
SalesID numberic key
Value currency
RYearID numeric

The query would have a join on RYearID. A cartesian query won't work.

Ports · Jan 7, 2020

Ok. Thank you everyone. I've tried out your suggestions. Some of them didn't work or I didn't understand them- What I ended up doing is creating a make table query with a parameter (so I get a dialog box to input "R04" or whatever period it is.

Thanks again.

table with variables to be accessed in queries (1 Viewer)

Ports

Registered User.

The_Doc_Man

Immoderate Moderator

Ports

Registered User.

plog

Banishment Pending

theDBguy

I’m here to help

vba_php

Forum Troll

Ports

Registered User.

Ports

Registered User.

vba_php

Forum Troll

theDBguy

I’m here to help

plog

Banishment Pending

Ports

Registered User.

theDBguy

I’m here to help

Ports

Registered User.

theDBguy

I’m here to help

plog

Banishment Pending

Cronk

Registered User.

Ports

Registered User.

Similar threads

Users who are viewing this thread