Build Query Headrs based on Table Cells (1 Viewer)

Ossama22

Registered User.
Local time
Today, 17:15
Joined
Aug 31, 2018
Messages
52
Hello Guys ,
I have a question and i hope to find a solution here ,
iam working to create a database to help my in my work ,
all i need is
i have a lot of companies codes in a table
each company has a specific items for example (Xerox : Basic Salary & Meal Allowance &Incentive)
if i create a table consists of all companies codes as headers and below every code the items which belong to this company ,
is there anyway to generate a separate query with specific items(as a query headers) for every company which i assigned it before based on a choose from text box .. i attach photos to be more clear.

Hope to help me guys as i tried to search for the last 3 hours and it seems that its a hopeless case .
 

Attachments

  • Untitled.png
    Untitled.png
    8.9 KB · Views: 352

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:15
Joined
Aug 30, 2003
Messages
36,118
I'm not clear on the goal, but the table is not normalized. I'd expect fields for company and items:

Xerox Basic
Xerox Incentive

Then it's easy to query the items associated with a company. You don't want a field for every company, it would be a nightmare to maintain.
 

Ossama22

Registered User.
Local time
Today, 17:15
Joined
Aug 31, 2018
Messages
52
I'm not clear on the goal, but the table is not normalized. I'd expect fields for company and items:

Xerox Basic
Xerox Incentive

Then it's easy to query the items associated with a company. You don't want a field for every company, it would be a nightmare to maintain.

Iam sorry for not being clear, all i need is a tool to generate a query with items for every company,
For example : when i choose XE, it generates query with salary items i assigned to XE only
Is that possible
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:15
Joined
Jan 20, 2009
Messages
12,849
Iam sorry for not being clear, all i need is a tool to generate a query with items for every company,
For example : when i choose XE, it generates query with salary items i assigned to XE only
Is that possible

That is definitely not "all you need". You have built a spreadsheet in Access.

You need to either restructure the data as a database or put your spreadsheet in Excel. There is no point using Access with data like that.
 

Minty

AWF VIP
Local time
Today, 15:15
Joined
Jul 26, 2013
Messages
10,355
What Paul is saying is that you haven't stored your data correctly, and therefore your task / query is being made very difficult.

You have stored your data as if access was a spreadsheet, not in correctly designed tables.

Whenever you see a field name that has effectively been created as an actual data point, you will have a problem. So as Paul described you should be storing your data "vertically" not "horizontally" , have a read here http://www.niftyaccess.com/normalization-tool/ and the link in my signature.

Edit: Damn those Galaxiom faster fingers....
 

Ossama22

Registered User.
Local time
Today, 17:15
Joined
Aug 31, 2018
Messages
52
What Paul is saying is that you haven't stored your data correctly, and therefore your task / query is being made very difficult.

You have stored your data as if access was a spreadsheet, not in correctly designed tables.

Whenever you see a field name that has effectively been created as an actual data point, you will have a problem. So as Paul described you should be storing your data "vertically" not "horizontally" , have a read here http://www.niftyaccess.com/normalization-tool/ and the link in my signature.

Edit: Damn those Galaxiom faster fingers....

hello , thanks for your answer ,

i bought that tool and iwatched the video , but i think that it wont help me to do what i need . :( , i think that its a hopeless case ,
iam so beginner in access , if you can guide me how to create what i need using transpose tool plz tell me :)
 

Minty

AWF VIP
Local time
Today, 15:15
Joined
Jul 26, 2013
Messages
10,355
Post up some sample data as you have it in an excel spreadsheet. I would zip it.
Uncle Gizmo may be able to better steer you through the process as he wrote the guide.
 

Ossama22

Registered User.
Local time
Today, 17:15
Joined
Aug 31, 2018
Messages
52
Post up some sample data as you have it in an excel spreadsheet. I would zip it.
Uncle Gizmo may be able to better steer you through the process as he wrote the guide.

I Create it in both ways , Vertical and horizontal , i know that its kind of annoying but iam seeking your help guys to be able to finalize this issue
 

Attachments

  • Excel Sample.xlsx
    9.2 KB · Views: 371

Minty

AWF VIP
Local time
Today, 15:15
Joined
Jul 26, 2013
Messages
10,355
Please see the attached.

3 Tables - Companies, ExpenseItems, CompanyExpenses.

You can easily add more companies and more expenses without redesigning anything.

Your query becomes a simple cross tab.
 

Attachments

  • Excel Sample.xlsx
    16.1 KB · Views: 372

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:15
Joined
Jul 9, 2003
Messages
16,245
I think Minty has hit the Nail on the head! ... If that's the way you want to go with it, then I can do a demo video showing you how to get there. Otherwise (and I think you should probably do this) you need to give a good explanation of what you are doing.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:15
Joined
Jul 9, 2003
Messages
16,245
Re-reading the thread, I think it's worth emphasising what we've identified between us as the issue. In essence you have suffered the Fate of most people moving from Excel into Access, in that what appears like a totally acceptable way to proceed and that is to copy your Excel data straight into Access, (it seems the right thing to do). However it is a significant trap which can cause you a lot of grief if you don't catch it early enough. It looks like you're in the early stages, and if you follow the advice on how to bring your data into Microsoft Access then you should be able to move on without losing too much ground.

I have blogged about the issue here:- http://www.niftyaccess.com/excel-in-access/

The other thing I recommend is search on "Normalisation", which is the technical term for restructuring your data to make it suitable for the MS Access. As I explain in my Blog, you can continue with the structure you have, but the difficulties in manipulating and extracting data will just multiply at an exponential rate!
 
Last edited:

Ossama22

Registered User.
Local time
Today, 17:15
Joined
Aug 31, 2018
Messages
52
Please see the attached.

3 Tables - Companies, ExpenseItems, CompanyExpenses.

You can easily add more companies and more expenses without redesigning anything.

Your query becomes a simple cross tab.

i Wanna to thank you for ur great efforts with me ,
Thx :)
 

Ossama22

Registered User.
Local time
Today, 17:15
Joined
Aug 31, 2018
Messages
52
I think Minty has hit the Nail on the head! ... If that's the way you want to go with it, then I can do a demo video showing you how to get there. Otherwise (and I think you should probably do this) you need to give a good explanation of what you are doing.

Thanks Uncle for your support and collaboration , it will be a great favor if you create that video ,

Thanks again bro :)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:15
Joined
Jul 9, 2003
Messages
16,245
I'll get to it this evening...

Well it's taking me a bit longer than I thought it would to get around to doing this. Here's the first video:-

Build Query Heads - Nifty Access
https://youtu.be/0w_uqBpA9gw

Which explains the question and the reason for taking the original data and normalising it to make it suitable for use in MS Access. I think this was an excellent example because, for a start it wasn't clear (To Me) how to normalise the data, in fact I was struggling a bit with that until Minty came up with the solution, so thank you Minty!

Unfortunately the next video is probably the one you really want, and I wont be able to get to that one till this evening! There is a video which demonstrates using the normalisation tool on my website here:-

http://www.niftyaccess.com/normalization-tool/

Unfortunately, due to the nature of the original data it's not quite obvious how to go about the transposition of the data. So it might be better to wait for the next video.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:15
Joined
Jul 9, 2003
Messages
16,245
Hi

Can you upload a zipped copy of the Database?

See Attached:-

I think I've got the cross-tab Query about right, but it definitely needs an explanation!
 

Attachments

  • Build Query Headrs_1c.zip
    25.7 KB · Views: 349

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:15
Joined
Jul 9, 2003
Messages
16,245
In this video I demonstrate how to normalise some data imported from Excel. This consists of breaking it up into 3 separate tables. I then demonstrate how this normalised data can be drawn back together again into a useful and usable table. I then demonstrate how to use a cross-tab query to produce a particular output required.

Build Query Heads 2 - Nifty Access
https://youtu.be/y-EQ2JgLki8

The form "frmTranspose" (shown in the video) is available from my website here along with further information on its use. It is very handy for turning denormalized data into normalised data for use in MS Access.

Normalisation tool in Action
 
Last edited:

Users who are viewing this thread

Top Bottom